A Backup of a Lie
Every SQLite database I run is in write-ahead-log mode. That is the right setting. It is also why a file copy of the database can succeed, open cleanly, verify, and contain nothing.
I have hit this three times now. The third one is the one that got my attention, so here are all three, in the order I should have learned from them.
Four Kilobytes
In August I measured a production data file on a deployed service. The main database file was 4,096 bytes and contained zero tables. Every table and every row sat in a 164-kilobyte write-ahead-log file next to it, waiting to be checkpointed back.
A download of the main file succeeds. It opens. It is a valid SQLite database. It is empty.
The same command on a sibling database that morning returned all 33 rows, because that one happened to have been checkpointed. That is the worse half. A backup that fails outright gets noticed. One that works most mornings gets trusted, and is empty on the morning it is opened.
A backup of a lie restores as data loss that looks like success.
The Auth Store
In a different monorepo, the same shape, with higher stakes. The auth database that holds every user account was four kilobytes of main file beside a 300-kilobyte log. A file copy restores an empty auth store and reports success. The accounts are in the log, not the file.
The fix is one command. Use the database engine's own backup command instead of a file copy. It checkpoints as part of the operation. Everyone knows this. Everyone forgets it the day someone simplifies the backup script, and the someone is usually me.
So the restore drill does not claim it. It shows it. On every run, the drill restores that store both ways and prints the pair side by side:
cp auth.db only -> users = no users table at all
sqlite3 .backup -> users = 3
The day someone replaces the backup command with a copy, the output says what was lost, in the same terminal, before anything is trusted.
The Bar Is Restored Equals Live
The first version of that drill checked that a restored table had rows. It reported two working backups as broken, because one store's override table and one whole Postgres instance were empty for real. Rather than carve out exceptions for them, the bar became restored equals live. Compare the restored counts to the live counts, whatever they are. Zero equals zero is a pass.
The drill restores Postgres into throwaway containers on production's own images, so the geospatial extension actually runs instead of being taken on faith. It writes to nothing live. It costs a failed run to learn that one readiness probe is not readiness. The database entrypoint runs its setup against a temporary server that answers the probe and then shuts down, and the restore dies with "the database system is shutting down." Three consecutive queries a second apart clear it.
The drill is the point, not the backup. A backup you have never restored is a guess. I had several.
Two Bytes
One more finding from the same week, smaller and stranger. A snapshot of the same store taken by a Python writer and by a Node writer differ in exactly two bytes out of 217,088. Offsets 98 and 99, where each SQLite library stamps its own version number. Everything else is identical.
That is reassuring and also a warning. You cannot tell two SQLite files apart by looking at them. A directory called "deployed" names a role, not a machine. Restoring production from a laptop's copy is exactly the mistake a backup directory should not make easy. Nothing in the file tells you which one you are holding.
What I Do Now
If your store is in write-ahead-log mode, and it should be, then a file copy of the main database is not a backup. It is a backup of whatever was checkpointed last. That might be everything and it might be nothing, and you will not know which until the morning you need it.
Use the engine's backup. Run the restore. Compare the counts. Print the pair. Do it on a schedule, so that the day the script gets simplified, the drill is the thing that notices.
-- Justin Higgins. Software Engineer, Midwest. Restored an empty auth store once, and now the drill prints both ways.
Companion pieces: Verify the Artifact, Not the Process - the guard that printed green. One Line Held the Lock - the same instinct, applied to a profiler.
Reactions, disagreements, war stories: jchigg2000.dev@gmail.com