SQLite3 Rsync!

I've been researching LiteStream and LiteFS for backing up and replicating SQLite databases. They're both very cool. LiteStream will stream your SQLite database to an S3 bucket, an SFTP server, or a local directory, with configurable sync-interval which defaults to 1 second. LiteFS will replicate your SQLite DB to any number of replicas with live data.

It turns out there's a first party tool which can perform a similar task - sqlite3_rsync. It takes an origin and a replica path - at least one of which needs to be a local directory. It will work over ssh, so you can use it like :

sqlite3_rsync user@primary_host.com:/mnt/data/storage/production.sqlite3 /mnt/data/storage/replica.sqlite3

This command will sync only the parts of the database which have changed, making it a very efficient way to make a replica of an SQLite database.

It seems the best way to get a copy is to build it yourself and Simon Williamson has posted about how to build it here:

Compiling and running sqlite3-rsync

Today I heard about the sqlite3-rsync command, currently available in a branch in the SQLite code repository. It provides a mechanism for efficiently creating or updating a copy of a SQLite database that is running in WAL mode, either locally or via SSH to another server.

Simon Willison’s TILs

Although since that post, it looks like the make target has changed from make sqlite3-rsync to make sqlite3_rsync.