How to Back Up SQLite to S3 on the Fly Without Losing Data

Love SQLite for its simplicity? Spin up a fresh VPS, launch a small service, drop a database file next to it, and everything works with instant response. No extra Docker containers with PostgreSQL, no user management or access rights configuration. Beautiful.

Problems start when the server unexpectedly crashes or the provider "accidentally" deletes your disk. If you were backing up once a day via cron, you lost a day's worth of users and orders. If you tried copying the database file with a simple cp while writes were happening, you ended up with a corrupted file that SQLite won't even open.

The creator of the well-known BoltDB key-value database, Ben Johnson, faced this exact dilemma and built Litestream. It's a small Go tool that solves the SQLite backup problem once and for all.

What Litestream Does and Why You Need It

Litestream runs as a background process on the same server as your application. Its job is to continuously stream changes from your local SQLite database to remote storage. This can be an S3-compatible cloud, Yandex Object Storage, MinIO, or just a folder on another mounted disk.

As a result, you get a ready-made disaster recovery system. If your VPS goes up in flames, you spin up a new instance, run a single restore command, and the database recovers right up to the last transaction before the crash.

The main feature of the tool is reliability. It interacts with the database strictly through the official SQLite C-API, not by blindly reading raw bytes from disk. This guarantees that write phases won't overlap with compression or journal checkpoints, and the database won't turn into a corrupted file.

How Replication Works Under the Hood

To understand how it works, let's recall SQLite's WAL (Write-Ahead Logging) mode.

By default, SQLite writes changes directly to the main .db file. In WAL mode, all new transactions first go to a separate journal with the -wal extension. Periodically, these changes are flushed back to the main file. This approach speeds up writes and doesn't block reads.

Litestream uses this mechanism:

1. It switches the database to WAL mode.
2. Monitors for new pages appearing in the WAL file.
3. Copies these changes and sends them in small segments to S3.
4. Controls the checkpoint process so SQLite doesn't clear the WAL prematurely before the data has been uploaded to the cloud.

The delay in sending data to S3 is just a few seconds. If the server breaks, the maximum you lose is data from the last couple of seconds.

Setup and Usage Example

You can start replication in a couple of minutes. Just download the binary or use a ready-made Docker image.

First, let's create the configuration file litestream.yml:

dbs:
  - path: /var/lib/my-app/production.db
    replicas:
      - url: s3://my-backup-bucket/production.db

Connection parameters to S3 like access keys and endpoint are usually passed via standard environment variables: AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY.

The process is started with a simple command:

litestream replicate -config /etc/litestream.yml

Often Litestream is run in the same Docker container as the main application, using a lightweight process orchestrator like entrypoint.sh or supervisord.

If the server crashed and you need to deploy the database to a new location, you run the restore command before starting the application:

litestream restore -o /var/lib/my-app/production.db s3://my-backup-bucket/production.db

The utility will fetch the latest base dump from the bucket, download all missing WAL segments on top, assemble a complete database file, and place it at the specified path.

Limitations and Caveats

The tool looks very appealing, but Litestream has a clear scope and its own limitations.

First, the utility requires WAL mode to be enabled. If your application or a specific database driver library is incompatible with it, nothing will work.

Second, this is a disaster recovery tool, not for horizontal read scaling. You can't use Litestream to spin up five copies of the service on different servers all reading from the same replicated database simultaneously. For distributed scenarios with multiple nodes, you're better off looking at LiteFS from the same team or projects like rqlite.

Third, if you have a huge database hundreds of gigabytes in size with constant heavy writes, S3 traffic and API request costs may come as an unpleasant surprise. The tool is designed for small to medium workloads.

Who Will Find This Project Useful

Litestream removes the need to set up heavyweight databases where there's no real need for them. It's an excellent choice for:

• Telegram bots and REST APIs in Go, Python, or Node.js with a single application instance.
• Projects on frameworks like PocketBase or Directus running on top of SQLite.
• Personal self-hosted services running on cheap VPS.
• Microservices with local cache or client data isolation.

If you've been wanting to move a pet project to SQLite but fear of losing your disk was holding you back, try adding Litestream. Setup takes half an hour, but you'll sleep peacefully.