How to Test Backup and Restore Drills for a Small Database

Backups are a promise, and the only proof is a restore. A drill is a planned, timed restore, done calmly so the first real one is not a surprise at 3 a.m. For a small database it takes an hour and finds problems that matter: a missing key, an outdated document, a backup job that has been writing empty files for weeks. It is the manual companion of automated restore tests and verifying a dump is restorable. The examples assume PostgreSQL 17 and a daily dump.

Prepare the drill

Goal:        recover appdb from last night's backup onto a new server
Start:       one person, clean machine, only the runbook and the password manager
Stop clock:  from "disaster declared" to "application reads data correctly"
Success:     data matches expectations, time within the target

Decide what you are testing: loss of the database server, loss of the whole host, a corrupted table or a mistaken delete. Each needs a different procedure. Choose a person who did not write the runbook, because they find the gaps. Set a target before you start (for example: back in 2 hours, losing at most 24 hours of data) so you can measure against it.

Run it step by step

date -u +%FT%TZ > drill-start.txt
# 1. find the backup
rclone ls offsite:db-backups | sort | tail -3
# 2. fetch and verify
rclone copy offsite:db-backups/appdb-2026-11-03.dump.gpg .
sha256sum -c appdb-2026-11-03.dump.gpg.sha256
# 3. decrypt
gpg --output appdb.dump --decrypt appdb-2026-11-03.dump.gpg
# 4. new server and restore
docker run -d --name drill -e POSTGRES_PASSWORD=change-me postgres:17
until docker exec drill pg_isready -q; do sleep 1; done
docker exec -i drill pg_restore -U postgres -d postgres --create --no-owner --jobs=4 < appdb.dump
date -u +%FT%TZ > drill-end.txt

appdb-2026-11-03.dump.gpg: OK

Follow the runbook literally, even where you know better, and note every deviation as a documentation bug. Use a container or a fresh VM, never the production host. Fetch from the offsite location, as you would in a real loss, since a backup stored only on the failed server is not a backup; see offsite database backups. Decryption proves the key is available to someone other than the person who set it up. The commands are an example of the flow.

Check the data

docker exec drill psql -U postgres -d appdb -Atc "SELECT count(*) FROM users"
docker exec drill psql -U postgres -d appdb -Atc "SELECT max(created_at) FROM orders"
docker exec drill psql -U postgres -d appdb -Atc "SELECT id, email FROM users WHERE id = 42"
echo "elapsed: $(( $(date -ud "$(cat drill-end.txt)" +%s) - $(date -ud "$(cat drill-start.txt)" +%s) )) s"

12840
2026-11-03 02:41:17+00
42|[email protected]
elapsed: 1473 s

Compare counts with production, check that the newest record is from the expected time, look up a record you know. Then point a copy of the application at the restored database and run a smoke test, since a restored database that the application cannot use is not recovered. The elapsed time is your measured recovery time for the database step; add the time to provision a server, change DNS and reconfigure the application for the total. The values above are examples.

Record and fix

Drill 2026-11-03
- Restore took 24 min for 1.8 GB (target: 120 min total)         OK
- Runbook referred to a deleted bucket                            FIXED
- Roles missing after restore; globals dump was not backed up     FIXED
- Key passphrase known by one person only                         ACTION: second holder
- Application config needed DATABASE_URL change, not documented   FIXED
Next drill: 2027-02-03

The write-up is the product. Every item either becomes a fix or a deliberate acceptance. The commonest findings are missing roles and extensions, an expired credential, a retention policy that deleted what you needed, permissions on the backup bucket, and documentation that refers to a system that changed. Update the runbook immediately while the details are fresh.

Schedule

Quarterly is reasonable for a small system, and after every significant change to the infrastructure, the database version or the backup tool. Put it in the calendar with an owner. Alternate scenarios: one drill restores the latest backup, the next restores to a point in time or from the oldest retained backup. Automated restore checks, as in verifying backups automatically, run between drills and catch regressions weekly.

Why a drill beats a document

A runbook is a hypothesis about what you would do in a disaster, and a hypothesis is only as good as the experiment that tests it. People who wrote the instructions know the context that the text omits: which server, which credential, which flag they always add. During a drill, someone without that context follows the text and discovers what is missing. The failures are small and cheap then, a command with a wrong path, a password in a vault that nobody can open, and large and expensive in a real incident. The purpose of the exercise is to find them while the stakes are low.

Drills also train the team. A person who has restored the database once is calmer the second time, makes fewer mistakes under pressure and knows which logs to read. This benefit is as real as the technical findings, and it argues for rotating who performs the drill so that the knowledge is spread across several people.

Scenarios worth rehearsing

Vary the scenario from one drill to the next. Losing the primary server with a healthy backup is the easiest. Harder ones include a backup that is corrupted and the previous one must be used, a deletion discovered three days after it happened which needs a point-in-time restore, loss of the secrets store so that backup decryption needs a recovery key, and an outage of the provider region where the backup lives. Each one exercises different parts of the process and different assumptions. After a few drills you will have a library of scenarios and a clear sense of which ones the current setup cannot survive.

Include the application. A restored database that the application cannot use has not delivered a recovery. Check the schema version against the code, the connection settings, the extensions the application expects, and the background jobs that assume particular data. A smoke test that logs in and loads a few pages catches mismatches that count queries cannot.

Measuring what matters

Time every step, not just the total. The breakdown shows where the minutes go: finding the backup, downloading, decrypting, restoring, building indexes, starting the application. Often one step dominates and is easy to improve, such as fetching from a nearer location, restoring in parallel with pg_restore --jobs or keeping a recent copy on local storage. Compare the total with the recovery objective, and record the trend across drills. A restore that took 24 minutes at 2 GB will not take 24 minutes at 200 GB, so project forward, and run the drill on a realistic data size.

Closing the loop

Treat every finding as a ticket with an owner and a date. Re-run the failed step after the fix and note it in the report. Share the report with the people who depend on the system, in plain language: what was tested, how long it took, what broke and what was done about it. That openness builds trust, and it makes the budget conversation easier the next time more storage or a second copy is needed to meet a stricter objective.

Where this does not apply

A drill covers the database; recovery of a business also needs the application, secrets, DNS and people. For larger systems, a full disaster recovery exercise covers all of them. If the database is managed, drill the provider's restore feature and note the time it takes, which is often longer than the dump size suggests. See the PostgreSQL backup and restore chapter and the Google SRE book for the reliability mindset.