FDE Capstone: The Warehouse Got the Same Order Three Times

The restore command in the rollback plan had never been run

Goal

On a customer service that uses SQLite in WAL mode, you actually run backup → evidence → migration → failure injection → restore → proof that it matches the backup point in time, and harden that procedure into migrate.sh, restore.sh, and rehearse.sh.

Why it matters

The recovery command in a rollback plan has usually never been run. A backup that was cp'd while the service was up has integrity_check ok but is missing commits, and a -wal left after a forced termination gets applied again on top of the restored file. If you do not confirm it in a rehearsal and leave it in numbers, you find out for the first time on the day you need the rollback.

Materials: /opt/lab/p1a-rehearsal/ — liveapp.py (an imitation of the order service: start, status, stop, crash), migrations/002_add_currency.sql, and migrations/003_customer_unique.sql. The working directory is /root/rehearsal (the service DB is /root/rehearsal/live.db). Put the evidence files in /root/rehearsal/evidence. The expected time is 60 minutes, and when the session ends, /root/rehearsal disappears.

Steps

Start the service with liveapp.py start, and with the service up, cp only the main file of live.db to /root/rehearsal/copies/cp-only.db, then write the row count and integrity_check of live and cp-only into /root/rehearsal/evidence/cp.tsv.
With the service up, create /root/rehearsal/backups/pre-v2.db with .backup and /root/rehearsal/backups/pre-v2-vacuum.db with VACUUM INTO, and write them into evidence/backup.tsv as file<TAB>rows<TAB>integrity<TAB>journal_mode.
Leave the file sha256 and .sha3sum --schema of the two backups, and the row count, user_version, integrity, and time, in evidence/baseline.json.
Create migrate.sh DB NNN_이름.sql (the placeholder is the name) as /root/rehearsal/migrate.sh and apply 002 to live.db. Apply it only when user_version is NNN-1; if it is already NNN, do nothing and return 0, and otherwise reject.
Apply 003 to live.db so that it fails, and leave the exit code, the user_version, the content hash before and after, and the error in evidence/failure.json. migrate.sh must leave no trace when it fails.
Create restore.sh BACKUP TARGET as /root/rehearsal/restore.sh. If the backup is not intact, reject it without touching the target, and even if there is a leftover -wal, restore with the same content as the backup and prove it.
After causing a forced termination during a deployment with liveapp.py crash, restore live.db from pre-v2.db and leave evidence/restore.json.
Create rehearse.sh DB WORKDIR UP.sql BROKEN.sql as /root/rehearsal/rehearse.sh. It runs from the backup to the restore proof in one pass and leaves WORKDIR/report.json, and if it fails to prove even one thing, it ends with a nonzero value.

Notes

While the service is up, your sqlite3 connection is not the last connection, so the -wal is kept. If you open and close live.db with the service stopped, a checkpoint runs and the step 1 condition disappears (even if you run liveapp.py start again, it is already moved). Do steps 1 and 2 with the service up.
Only read the backup files: sqlite3 'file:backups/pre-v2.db?immutable=1' 'PRAGMA integrity_check'
The 18th byte of the file header (counting from 0) is 1 for a rollback journal and 2 for WAL: od -An -tu1 -j18 -N1 파일 (the placeholder is the file)
The grader does not rely on the service you started. For steps 4, 6, and 8, it creates a new DB and runs your scripts directly, and for the rest, it recomputes the evidence files from the backup files and liveapp's record (app/events.log) and compares them. If you delete or modify a backup file, the earlier steps fail again.
Common mistakes: migrating with sqlite3 db < file.sql, BEGIN/COMMIT without -bail, and restoring with cp while leaving a -wal in place.

What do you lose if you cp the DB of a running service

Start the service, cp live.db to /root/rehearsal/copies/cp-only.db, and then write the row counts and integrity_check of live and cp-only into /root/rehearsal/evidence/cp.tsv.

First look with ls -l live.db* at what is next to the main file. Count the rows in live.db while the service is up, and read the copy only, through an immutable URI. The point of this step is to see whether the integrity_check result and the row count tell different stories.

Online backup with .backup and VACUUM INTO

With the service up, create /root/rehearsal/backups/pre-v2.db (.backup) and /root/rehearsal/backups/pre-v2-vacuum.db (VACUUM INTO) and write them into /root/rehearsal/evidence/backup.tsv.

VACUUM INTO fails if the target file already exists. You can tell the journal_mode from a byte of the file header without opening the file. Compare how the row counts of the two backups differ from the cp copy of step 1.

Leave reference values to compare after the restore

Leave the sha256 and .sha3sum --schema of the two backups, and the row count, user_version, integrity, and time of pre-v2.db, in /root/rehearsal/evidence/baseline.json.

Think about what the file hash and the content hash each prove. The two backups should differ in file hash and be the same in content hash. The time is the Pod's time in the format %Y-%m-%dT%H:%M:%S. After this, do not open the backup files and use them.

A migrate.sh that respects the version number

Create /root/rehearsal/migrate.sh and apply 002_add_currency.sql to live.db.

You get the version number from the number at the front of the file name. The migration file has no BEGIN, COMMIT, or user_version, so the script has to wrap it. The grader also runs it with DBs of different numbers and different user_versions.

A failed migration must leave no trace

Apply 003_customer_unique.sql to live.db so that it fails and leave /root/rehearsal/evidence/failure.json. migrate.sh must change nothing even if a middle statement fails.

Measure user_version and .sha3sum --schema before and after the failure. Check in the documentation what the sqlite3 CLI does after an error and whether a constraint violation rolls back the whole transaction. The grader tests migrate.sh with migrations that fail at different places.

A restore.sh that also handles the leftover -wal

Create /root/rehearsal/restore.sh BACKUP TARGET. Reject a broken backup before touching the target, and after the restore, confirm that the content hash is the same as the backup.

Look at the WAL documentation again for what happens the next time it is opened if a -wal remains next to the target. Check the backup before touching the target, and prove it after the restore. The grader gives you a DB where a -wal remained after a forced termination and a backup with broken pages.

Roll back a production DB that was forcibly terminated during a deployment

After liveapp.py crash, restore live.db from backups/pre-v2.db with restore.sh and leave /root/rehearsal/evidence/restore.json.

When the crash is over, look with ls -l live.db* at what remained. The outage's token is on the last crashed line of app/events.log. After the restore, also count whether any orders that start with hotfix- remain.

A rehearsal and report that run in one pass

Create /root/rehearsal/rehearse.sh DB WORKDIR UP.sql BROKEN.sql. Leave WORKDIR/backup.db and WORKDIR/report.json, and end with 0 only when every proof matches.

Call the migrate.sh and restore.sh you built earlier relative to the script's location. It must be nonzero in all of these cases: the failure injection did not fail, the content hash changed after the failure, and it differs from the backup after the restore. The grader runs it with a DB where a commit remains only in the -wal and with random version numbers.