Run a Quarterly Restore Drill

Restore the latest production backup into an isolated database, validate row counts and checksums, and record how long it took.

~45 minbackupsdisaster-recoverypostgresql

Task

Prove backups are restorable and measure recovery time before an incident forces the question.

When to use this

Backups run every night and report success. Nobody has restored one in eighteen months. During an incident, pg_restore fails on a missing extension and the team discovers the recovery procedure is undocumented and the RTO estimate is wrong.

Prerequisites

Nightly logical or physical backups with verified checksums
An isolated PostgreSQL instance for drills
Access to the backup storage and encryption keys

Steps

1. Create a scratch database on the drill instance, separate from production and staging.

createdb -h drill-db.internal -U postgres restore_drill

Expected output: An empty database named restore_drill exists on the isolated instance.

2. Restore the latest backup with ownership and ACL remapping disabled, and time the operation.

time pg_restore -h drill-db.internal -U postgres -d restore_drill --no-owner --no-acl --jobs=4 /backups/latest.dump

Expected output: pg_restore finishes without fatal errors and reports an elapsed time that can be compared with the RTO target.

3. Compare row counts and a checksum against the source snapshot taken at backup time.

psql -h drill-db.internal -U postgres -d restore_drill -c "
  SELECT 'orders' AS t, count(*), md5(sum(id)::text) FROM orders
  UNION ALL
  SELECT 'customers', count(*), md5(sum(id)::text) FROM customers;"

Expected output: Counts and checksums match the recorded snapshot from the backup manifest.

4. Record the drill result, duration, and any gaps in a dated report, then drop the scratch database.

psql -h drill-db.internal -U postgres -c 'DROP DATABASE restore_drill;'

Expected output: The report states restore duration and outcome, and the scratch database is removed to free space.

Troubleshooting

Problem

pg_restore fails because an extension is missing

Solution

Create required extensions on the drill instance before restoring, and record them in the recovery runbook as prerequisites.

Problem

Restore is much slower than the RTO target

Solution

Use the directory format and parallel jobs, restore onto comparable hardware, and measure the bottleneck before promising an RTO.

Limitations

A logical dump drill does not test point-in-time recovery or WAL archiving.
Drill hardware may be faster or slower than production, so measured time is an estimate, not a guarantee.

A successful backup job is not a backup

Backup tooling reports whether the dump was written, not whether it can be restored. Restore drills are the only way to test the full path: storage access, encryption keys, tool versions, extension availability, disk space, and time. They also produce the RTO number you can defend in a review.

Keep the drill isolated

Never restore into production or staging; a mistake can overwrite live data. Use a dedicated instance with enough disk for the largest backup plus indexes. Restore with --no-owner --no-acl to avoid failures from roles that do not exist on the drill host. Run it on a schedule, quarterly at minimum, and after any major version upgrade.

time pg_restore -h drill-db.internal -U postgres -d restore_drill \
  --no-owner --no-acl --jobs=4 /backups/latest.dump

Validate more than exit code

pg_restore can finish with warnings. Compare row counts and a checksum against the backup manifest, then run a few representative queries. Check that indexes and constraints exist; a restore that skips them returns data but performs badly. Record the exact backup timestamp and the elapsed restore time.

Write it down

The drill report should include what was restored, how long it took, what failed, and which runbook step was wrong. Update the recovery runbook in the same change. An untested runbook decays quickly: a role rename or a new extension can invalidate it without anyone noticing until the real incident.