Run a restore drill that proves your Postgres backup works

Before you start

• A Postgres database with a scheduled backup.
• A clean host or container that has never seen production, with the same major Postgres version.
• Two people: an owner and a deputy who did not write the runbook.

Why the exit code is not enough

A dump of the wrong database exits 0. So does a job that writes to a full disk and keeps an old file, or one whose upload silently fails. The only proof is a restore that an application can log in to, timed, on a host that is not production.

The drill

1. Step 1Name an owner and a deputy

   The owner is paged when backups go quiet and signs the drill report. The deputy runs this drill from the runbook alone, so every question they ask becomes a missing line in it. A team of one still needs a second person on the page.

2. Step 2Take a dump in custom format

   The custom format is compressed and lets pg_restore list and reorder its contents.

   pg_dump -Fc -d app -f app.dump

3. Step 3Save roles separately

   pg_dump covers one database and leaves out roles and their passwords, which live at the cluster level. Without them the restore succeeds and the application still cannot log in.

   pg_dumpall --globals-only -f globals.sql

4. Step 4Check the archive is the one you meant

   List the archive before restoring. A tiny file, a missing table or the wrong database name shows up here in seconds.

   ls -lh app.dump
   pg_restore --list app.dump | head -20

5. Step 5Restore onto a clean host, and time it

   Use a host that shares nothing with production: not its disk, its network or its credentials. Restore the roles first, then the database, and stop on the first error.

   time (
     psql -d postgres -f globals.sql &&
     createdb app &&
     pg_restore --exit-on-error -d app app.dump
   )

6. Step 6Verify the data, not the exit code

   Compare row counts on the tables that matter with production, check the newest row is as recent as your recovery point promises, and point a copy of the application at the restored database and sign in.

   SELECT count(*) AS users, max(created_at) AS newest FROM users;

7. Step 7Write the report

   Record the date, who ran it, the backup it used, the data size, the time it took and every fix you needed. Declare your recovery time from the slow end of repeated runs, not the best one.

Where the copies live

Count copies by what can destroy them, not by how many there are. Two copies in the same building, the same cloud account or behind the same credentials are one copy against a fire, a deleted account or a stolen key.

• Keep at least one copy in a different failure domain: another building, region or provider.
• Keep one copy that the production credentials cannot delete, such as an object lock or a separate account.
• Check where each copy actually sits instead of reading what the contract says.