Verdict
Yes
Yes, test backups with a scheduled job that restores the newest backup into an empty database every week and checks that the data is recent and complete; a backup job that exits 0 proves only that it wrote a file.
Why
- Blast radiususers
- Change frequencyrarely
- Detectabilitynever
- Reversibilityimpossible
- Test costmoderate
Yes, test backups by restoring them on a schedule. The typical case is a nightly pg_dump of a product's PostgreSQL database, copied to object storage by a cron job. Blast radius is users, because customers lose the records they entered, and Change frequency is rarely, because the job changes a few times a year. Detectability is never and Reversibility is impossible, because a broken backup looks like any other file until the day of a restore, and data deleted with no working backup is gone. Test cost is moderate, because the restore job is a short script in a scheduled CI pipeline, so rule R11 gives Test.
| When | Decision | Why |
|---|---|---|
| The database holds paid orders and invoices | Test mandatory: restore every night and compare the restored order count for the previous day with production | Blast radius rises to money, because lost orders cannot be shipped or billed |
| The backup script names the tables it copies, and new tables arrive in most weeks | Test mandatory: the restore test fails on any production table missing from the restore | Change frequency rises to constantly, because each new table stays out of the backup until someone edits the list |
| RDS or another provider takes the backups, and each restore creates a billed instance | Test: restore to a new instance every month, check recent rows and the table list, then delete the instance | Test cost rises to heavy, because each run creates and deletes a full instance; the decision holds because Reversibility stays impossible, so rule R9 does not apply |
| The database is an internal analytics warehouse that jobs can reload from the source systems | Test minimally: restore the newest warehouse backup once a month and check that every table came back | Blast radius falls to internal and Reversibility to with-effort, because only staff lose reports while a reload runs |
| You take one backup by hand before a risky migration | Test it differently: restore the pre-migration backup into a scratch database and query it before the migration runs | Change frequency falls to once, because the backup is made and used one time |
| The backup covers a side project database that only you use | Do not write a restore test for a side project database; restore the backup once by hand after setup | Blast radius falls to none, because you alone lose the data |
What breaks if you don't test
You learn that the backup is broken on the day you need it. A script without pipefail that pipes pg_dump into gzip uploads an empty archive every night once the server runs a newer major version than pg_dump, because pg_dump refuses to dump a newer server and the pipeline returns the status of gzip. The job stays green. The rows that a bad migration deletes are then lost for good.
What you lose if you over-test
A nightly full restore of a 500 GB database keeps a second large instance busy for hours each day, and each copy is one more place holding customer data. An assertion on exact row counts fails on every run, because production has moved on since the backup.
How to test
Write a restore test that runs on a schedule, because backups break when versions, keys and storage change. The minimum set:
- Every week, restore the newest backup into an empty database on the major version production runs, with
pg_restore --exit-on-error, which stops at the first error instead of carrying on. - Assert that the busiest table has a row younger than 26 hours and that every production table came back.
- Fail when the restore takes longer than the recovery time you promised.
- Ping a heartbeat monitor after each success, so a job that stops running raises an alert too.
For managed backups, script a point-in-time restore to a new instance and run the same queries.
When the answer changes
- The database holds orders, invoices or payments.
- The backup script lists its tables, and new tables arrive most weeks.
- The backup exists for one migration only.
Real incident + Code example
The GitLab dump that held nothing
On 31 January 2017 a GitLab engineer deleted the data folder on the primary database server instead of the secondary (postmortem). The nightly pg_dump backups held nothing: the cron job ran pg_dump for PostgreSQL 9.2 against a 9.6 server, and the failure emails were rejected over DMARC, so nobody saw them (InfoQ). The team restored from an LVM snapshot taken six hours earlier for staging, which took about 18 hours, and lost the data of those six hours: about 5,000 projects, 5,000 comments and 700 new user accounts. A weekly restore test would have failed within a week of the first empty dump. This is the job I set up for the typical case:
#!/usr/bin/env bash
# restore-test.sh: a scheduled CI job runs it every Monday.
# Backups are pg_dump -Fc files. SCRATCH is an empty postgres:16 service container,
# the major version production runs.
set -euo pipefail
newest=$(aws s3 ls s3://acme-backups/db/ | sort | tail -n 1 | awk '{print $4}')
aws s3 cp "s3://acme-backups/db/$newest" newest.dump
start=$(date +%s)
pg_restore --exit-on-error --no-owner -d "$SCRATCH" newest.dump
[ $(( $(date +%s) - start )) -lt 3600 ] # we promise recovery within one hour
# Recent: the busiest table has a row from the last 26 hours.
recent=$(psql -At "$SCRATCH" -c "select count(*) from comments where created_at > now() - interval '26 hours'")
[ "$recent" -gt 0 ]
# Complete: every production table came back.
tables="select tablename from pg_tables where schemaname = 'public' order by 1"
diff <(psql -At "$PROD_REPLICA_URL" -c "$tables") <(psql -At "$SCRATCH" -c "$tables")
curl -fsS "$HEARTBEAT_URL" # the monitor alerts when this ping stops arriving
Related questions
FAQ
- Should you test backup restores?
Yes, test backup restores, because only a restore shows that a backup works. Restore the newest backup into an empty database every week and check that the data is recent and complete.
- How often should I test backup restores?
Run an automated restore test every week, so a broken backup is found within seven days of the first bad file. Rehearse the full recovery by hand after every move to a new database host, because the automated test does not practise the runbook.
- Should I test RDS automated backups?
Yes, test RDS automated backups by restoring one to a new instance on a schedule and querying it. AWS runs the snapshots, but the retention period, the key permissions and the restore steps are yours.