FDE Capstone: The Warehouse Got the Same Order Three Times

A rollback nobody ever ran: SQLite WAL backups and restore rehearsals

In one line

A rollback plan that has never been run is not a plan but a hope. For a service that uses SQLite in WAL mode, a backup is taken not by copying the file but with .backup or VACUUM INTO, a migration is bound in a single transaction, and a restore is finished only after handling the leftover -wal and proving by a content hash that it matches the backup point in time.

Why this was needed

At the review meeting the day before the customer's deployment, a rollback plan was passed around. "If something goes wrong, roll back with cp /backup/orders.db /srv/app/orders.db." Everyone nodded, and the next day the migration failed halfway. When they copied as the plan said, the service came up, and an hour later the customer support team asked, "We can't see yesterday evening's orders." The backup had been taken with cp while the service was up, and after the restore, the urgent fix that should have been rolled back was still alive.

The command itself was not wrong. Nobody had run it to see what it does in this service's file layout. What an FDE should do at a customer site is not to write a plan but to run a rehearsal and leave evidence. Is the backup intact, does a failed migration leave a trace, and does a restore really go back to that point in time — each has to be shown with numbers.

How it works

1. The WAL file is part of the database. According to the WAL documentation, in WAL mode changes are appended not to the main file but to the -wal file, and commits are recorded there too. A checkpoint moves that content into the main file, and by default it runs automatically when the WAL reaches 1000 pages. The WAL file remains while a connection is open, and is usually deleted when the last connection closes. And the documentation clearly says that because the WAL is the persistent state of the database, it has to travel together when copied or moved, and if they are separated, committed transactions can be lost or the file can be corrupted.

Confirming it by measurement is frightening. We put 500 orders into the main file, committed 137 more with the connection open (auto checkpoint off), and then cp'd only the main file. The copy had 500 and PRAGMA integrity_check was ok. The file is fine and only the commits quietly go missing. The fact that the check passed is not evidence that the backup is intact.

2. There are two online backups. The backup API documentation explains that the backup copies a little at a time, locks only while reading, and the result is a copy identical to the source at the point when the copy started. If another connection writes in the middle, it usually starts copying again from the beginning. The CLI's .backup uses this API. VACUUM INTO of the VACUUM documentation is an alternative that writes a consistent snapshot of the source into a new file of minimum size. If the target file already exists (and is not empty), it fails, and if the power goes out midway, the result may be incomplete.

The two produce different result files (measured: sqlite3 3.45.1). On a WAL DB containing 637 rows, the .backup copy kept the header in WAL mode and the VACUUM INTO copy was in rollback journal (delete) mode. The file sha256 values also differ from each other. But the .sha3sum of the CLI documentation is a hash of the content and not the on-disk representation, so it does not change with a transformation such as VACUUM. If you give --schema, it includes the schema too. The .sha3sum --schema of the two copies was the same. You ask "is it the same data at the same point in time" with this value, not with the file hash.

3. A migration is one transaction, and the version is user_version. According to the PRAGMA documentation, user_version is an integer at offset 60 of the file header and is a value for applications that SQLite itself does not use. It is good to use as a schema version number. If you put the migration contents and the raising of user_version in one transaction, both happen together or neither happens.

There are two traps here. The CLI documentation says that by default the CLI keeps running the next command even after an error, and that you have to give -bail to make it stop. The ON CONFLICT documentation explains that the default resolution, ABORT, rolls back only that statement, keeps the earlier statements of the same transaction, and keeps the transaction alive. When the two facts overlap, this is what happens in the measurement.

Application method	When the third statement violates UNIQUE
`sqlite3 db < migration.sql`	The earlier ALTER and UPDATE remain
`BEGIN; …; PRAGMA user_version=3; COMMIT;` without `-bail`	COMMIT with the earlier statements remaining, and user_version is also 3
The same content with `sqlite3 -bail`	Exit code 1, and both the column and user_version unchanged

The BEGIN IMMEDIATE of the transaction documentation takes the write lock at the start, so if another write is in progress, it fails at the beginning rather than in the middle.

4. A restore starts from the leftover -wal. If the service is forcibly terminated, the connection does not close and the -wal remains. If you cp a backup over the main file in that state, the next time it is opened, the leftover WAL is applied on top of the restored file. In the measurement, user_version came back to the backup value, but the urgent fix row that should have been rolled back was still visible. Before the restore, remove -wal and -shm, or overwrite through SQLite with the CLI's .restore, and when it is done, compare .sha3sum --schema with the backup's value.

리허설 한 바퀴
  .backup → integrity_check · 행 수 · .sha3sum --schema · user_version 기록
  migrate(N)            → user_version N
  migrate(깨진 N+1)     → 실패해야 하고, 내용 해시가 그대로여야 한다
  restore(백업)         → 내용 해시 = 백업, user_version = 백업

What it looks like in the field

"The backup runs every day" usually means that cron runs cp. It was mostly fine because the service is quiet at dawn, and on the days it was not fine, nobody knew. The conversation changes the moment you show, in the rehearsal, the backup row count compared with the number the service committed.

Migrations are similar. It is common for a file that passed on the development DB to fail halfway on production data (on development data with only one order per customer, the UNIQUE index gets created). If you show in advance that no trace remains at that point, the customer opens the deployment window believing not "if it fails, we can roll back" but "even if it fails, nothing changes".

What really matters in practice

You do not back up a WAL DB that is up with a service by copying the file. Even if integrity_check is ok, commits can go missing.
For the evidence of a backup, you leave four together: the row count, integrity_check, the content hash (.sha3sum --schema), and user_version. The file sha256 is evidence that "that file did not change", not evidence that "it is the same data".
For a migration, put the contents and user_version in one transaction, and use -bail with the CLI. A version that is already applied does nothing, and if it is not the next version, reject it.
A restore is finished only when it handles the leftover -wal and is proven by the content hash.
If the failure injection does not fail, that rehearsal proves nothing.

What you will do in the next lab

You start an order service running in WAL mode, measure what a cp backup loses, and after backing up with .backup and VACUUM INTO, leave the reference values. You build migrate.sh, which respects the version number, and restore.sh, which rejects a broken backup, and actually restore a production DB that was forcibly terminated during a deployment. Finally, if you build rehearse.sh, which runs from the backup to the restore proof in one pass, the grader runs that rehearsal on a fresh DB with a leftover -wal.