Remote SQLite backup

I wrote earlier about how to backup a small SQLite database.

That solution works well for small databases, but can become impractical for larger databases. In particular if the machine with the database has not enough disk space for another copy of it, and you want to backup to another machine.

One option is to use sqlite3_rsync, but I could not get that to work, it kept running out of memory. So I found another way, dump the database to text and gzip it. Combined with ssh, you can make a backup to a remote machine without storing anything extra on disk on the database machine. Run this on the machine where you want to have the backup:

ssh user@database.machine.com "sqlite3 path/to/mydb.sqlite .dump | gzip" > backups/db.sql.gz

Most likely, you can also initiate the backup from the database machine, but I leave that as an exercise to the reader.

(You might think that you could use VACUUM INTO in a similar fashion, but that does not work since it requires random access to the target file, you cannot stream it.)