Scenario · Storage & Backup
Backup restore drill
A sandboxed PostgreSQL incident — investigate with your own tools, submit a fix, and get deterministic Detect / Fix / Trap scoring.
L3 · 10–15 min · runs locally in Docker
Launch
Start this scenario
Boot it in a real PostgreSQL sandbox and investigate with psql, EXPLAIN and pg_stat_statements.
ride postgres start stage-05/10-backup-restore-drillPart of these paths
Show the postmortem & investigation hints spoilers
Backup restore drill
Type: incident simulation · Topic: Storage & Backup · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-05/10-backup-restore-drill
POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause (drill): teams often "verify" a backup by checking that the dump file
exists, or by reading the source database — neither proves the backup can be
restored. The only real proof is restoring it to a separate target and checking
the restored data there.
How it was done: take a logical backup of the source, restore it into a fresh,
separate target database (`restore_drill`), then verify on the target that the
expected snapshot marker ('stage-05-drill') is present.
The result: a restore that actually reproduces the expected data on an
independent target — a backup proven usable, not just present.
Lesson: a backup is only as good as your last successful restore. Drill it
regularly: restore to an isolated target and validate the data there. Reading the
source, or trusting that the file exists, is not a restore drill — and an index
has nothing to do with it.
INVESTIGATION HINTS (the staged path to diagnose and fix)
1. This is a restore drill: prove a backup can actually be restored AND that the restored copy contains the expected data. Look at the source marker first: SELECT * FROM restore_drill_markers; then take a backup and restore it to a SEPARATE target — don't validate the source and call it done.
2. Take and restore the backup with the scenario actions: `pgpg action <session> take-verified-backup` then `pgpg action <session> restore-drill` (loads the dump into the `restore_drill` database). Then verify ON THE TARGET: \connect restore_drill then SELECT * FROM restore_drill_markers WHERE id = 'expected_snapshot';
3. A drill is only complete when the restore TARGET holds the expected snapshot ('stage-05-drill'). Reading the source database proves nothing about restorability, and an index is irrelevant.