How to Configure Sqlite Wal Mode with Litestream for Continuous Replication and Point-in-time Recovery on Ubuntu Server

ByAkensai

How to Configure Sqlite Wal Mode with Litestream for Continuous Replication and Point-in-time Recovery on Ubuntu Server is something every developer running SQLite in production should understand. SQLite is lightweight and fast. But without proper replication, a single disk failure can wipe out your data. Litestream solves this by streaming WAL (Write-Ahead Log) changes to remote storage in real time. This tutorial walks you through enabling WAL mode on your SQLite database, installing Litestream on Ubuntu Server, and setting up continuous replication with point-in-time recovery. By the end, you’ll have a production-grade backup system running as a background service. This setup works well for applications like self-hosted tools, internal dashboards, and lightweight APIs that rely on SQLite as their primary database.

Prerequisites and Requirements for SQLite WAL Mode and Litestream

Before you start, make sure you have the following in place.

Required software and access:

  • Ubuntu Server 20.04 or 22.04 (fresh install or existing setup)
  • Root or sudo access
  • An existing SQLite database file (or we’ll create one)
  • An S3-compatible storage bucket (AWS S3, Backblaze B2, or MinIO)
  • Basic familiarity with the Linux terminal

Assumed knowledge: You should know how to SSH into a server, edit files with a text editor like nano, and run basic Linux commands. You don’t need to know SQLite internals deeply.

Estimated time: About 30 to 45 minutes from start to finish.

Make sure your S3 bucket credentials are ready before you begin. You’ll need your access key, secret key, bucket name, and region. Store these somewhere safe. You’ll paste them into the Litestream config file later.

How to Configure SQLite WAL Mode and Install Litestream on Ubuntu

Related tutorial: How to Set Up a Centralized Rsyslog Server to Collect and Organize Logs From Multiple Linux Clients on Ubuntu

This section covers enabling WAL mode on your database and getting Litestream installed and running.

Step 1: Update your system packages

Always start with a fresh package index. Run the following command:

sudo apt update && sudo apt upgrade -y

Step 2: Verify your SQLite installation

Check if SQLite is already installed:

sqlite3 --version

If it’s not installed, run:

sudo apt install sqlite3 -y

Step 3: Enable WAL mode on your SQLite database

WAL mode allows reads and writes to happen at the same time. It also makes Litestream replication possible. Open your database and enable WAL mode:

sqlite3 /var/lib/myapp/app.db "PRAGMA journal_mode=WAL;"

You should see the output wal confirming the change. WAL mode persists across connections. You only need to set it once.

If you don’t have a database yet, create one first:

mkdir -p /var/lib/myapp
sqlite3 /var/lib/myapp/app.db "CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);"
sqlite3 /var/lib/myapp/app.db "PRAGMA journal_mode=WAL;"

Step 4: Download and install Litestream

Visit the official Litestream installation page for the latest release. Then run the following to download and install the Debian package:

wget https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream_0.3.13_linux_amd64.deb
sudo dpkg -i litestream_0.3.13_linux_amd64.deb

Confirm the installation worked:

litestream version

Step 5: Create the Litestream configuration file

Litestream reads from a YAML config file. Create one at the default location:

sudo nano /etc/litestream.yml

Paste in the following configuration. Replace the placeholders with your actual S3 credentials and bucket details:

dbs:
  - path: /var/lib/myapp/app.db
    replicas:
      - type: s3
        bucket: your-bucket-name
        path: myapp/app.db
        region: us-east-1
        access-key-id: YOUR_ACCESS_KEY
        secret-access-key: YOUR_SECRET_KEY

Save the file with Ctrl+O and exit with Ctrl+X.

Step 6: Enable and start the Litestream systemd service

Litestream ships with a systemd service file. Enable it to start on boot:

sudo systemctl enable litestream
sudo systemctl start litestream

Check that it’s running without errors:

sudo systemctl status litestream

You should see active (running) in green. Litestream is now replicating your SQLite WAL changes to S3 in real time.

Step 7: Test point-in-time recovery

To restore your database from a specific point in time, use the litestream restore command. First, stop your application so the database isn’t in use. Then run:

litestream restore -o /var/lib/myapp/app_restored.db s3://your-bucket-name/myapp/app.db

To restore to a specific timestamp, add the -timestamp flag:

litestream restore -timestamp "2024-06-01T12:00:00Z" -o /var/lib/myapp/app_restored.db s3://your-bucket-name/myapp/app.db

This pulls the exact state of your database at that moment. It’s one of the most powerful features of this setup. You can check available snapshots and WAL segments with:

litestream snapshots s3://your-bucket-name/myapp/app.db
litestream wal-segments s3://your-bucket-name/myapp/app.db

For more details on recovery options, see the Litestream restore reference documentation.

Troubleshooting Common SQLite WAL Mode and Litestream Issues

Even with a clean setup, things can go wrong. Here are the most common problems and how to fix them.

Litestream service fails to start

Check the logs first:

sudo journalctl -u litestream -n 50 --no-pager

Look for credential errors or path issues. A wrong bucket name or missing IAM permissions is usually the cause.

WAL mode not persisting

Some application frameworks reset the journal mode on connection. Make sure your application sets PRAGMA journal_mode=WAL at startup, or set it directly in the connection string.

Database file not found error

Make sure the path in /etc/litestream.yml matches the actual database location. Use:

ls -lh /var/lib/myapp/app.db

If the file doesn’t exist, create it before starting Litestream.

S3 permission errors

Your IAM user needs at minimum these S3 permissions: s3:PutObject, s3:GetObject, s3:ListBucket, and s3:DeleteObject. Check your bucket policy and IAM role carefully.

High storage costs from WAL segments

Litestream keeps WAL segments for retention. You can set a retention period in your config to limit storage use:

dbs:
  - path: /var/lib/myapp/app.db
    replicas:
      - type: s3
        bucket: your-bucket-name
        path: myapp/app.db
        region: us-east-1
        access-key-id: YOUR_ACCESS_KEY
        secret-access-key: YOUR_SECRET_KEY
        retention: 24h
        snapshot-interval: 1h

This keeps 24 hours of history and takes a full snapshot every hour. Older WAL segments are removed automatically.

Conclusion

You now know how to configure SQLite WAL mode with Litestream for continuous replication and point-in-time recovery on Ubuntu Server. Your SQLite database is no longer a single point of failure. Every write gets streamed to S3 in near real time. You can restore to any point in time with a single command. This setup is simple to maintain and costs very little to run. As a next step, consider setting up monitoring alerts if the Litestream service stops unexpectedly. You can also explore using Ubuntu Server’s systemd watchdog feature to auto-restart the service on failure. Pair this with regular restore tests to make sure your backups actually work when you need them.

Similar Posts