Your SQLite backup is fine until it isn't
A service I look after keeps its state in a single SQLite file. The backup was a cron job that copied that file once a night, which had been working for about two years, which is exactly the sort of sentence that ends badly.
It ended when we had to restore. The database opened without complaint, ran
PRAGMA integrity_check without complaint, and was missing most of the previous
day. Nobody had noticed because nobody had ever restored it.
What cp actually copies
In WAL mode the database file is not the whole database. Committed transactions are appended to a separate write-ahead log, and pages are only moved back into the main file during a checkpoint. Copy the main file while the service is running and you get the state of the last checkpoint, not the state of the last commit.
Worse, cp has no idea the file is being written. If a write lands between
the moment the copy starts and the moment it ends, the copy can contain a page that was
half-written, or a mix of old and new pages that never existed at the same time.
The three things that work
1. Let SQLite do it
sqlite3 app.db ".backup '/backups/app-$(date +%F).db'"The .backup command uses the online backup API: it takes a read lock, copies
page by page in a consistent snapshot, and restarts the copy if the source changes underneath
it. This is the right answer for a live database and it has been for fifteen years.
2. VACUUM INTO, if you also want a compact file
sqlite3 app.db "VACUUM INTO '/backups/app-$(date +%F).db'"This writes a fully consistent copy with no free pages and no WAL. The catch is that it needs roughly twice the database size in free space, and it holds a read transaction for the duration, so on a large or heavily written database it is not free.
3. Stop, checkpoint, copy
sqlite3 app.db "PRAGMA wal_checkpoint(TRUNCATE)"
cp app.db /backups/app-$(date +%F).dbFine if you can stop the writer for a moment. Do not skip the checkpoint: without it the copy still misses the WAL.
What I actually changed
The cron job now runs the online backup, and then opens the result and runs two checks:
PRAGMA integrity_check and a count of rows in the table that should always have
a recent timestamp. The second check is the one that matters, because integrity_check only
proves the file is internally consistent, not that it is current.
The backup is also restored into a scratch directory once a week and queried. That takes about forty lines of shell and it is the only test that ever told us the truth.
I wrote walsnap partly because I got tired of copying those forty lines between projects.