How to Build a Backup You Have Actually Restored

Almost every infrastructure audit turns up the same line. Backups: configured. Last restore test: never.

Those two facts sit together more often than not, and they are not a contradiction. Taking a backup is a job you can automate and forget. Restoring one is a job nobody schedules, because it has no deadline until the day it has several.

An untested backup is a receipt. It proves a job ran. It does not prove the file is complete, that it can be read, that the key that decrypts it still exists, or that what comes out the other side is a database your application recognises.

The loop

The setup is one scheduled job with four steps, and the fourth is the one that matters.

1. Restore last night's copy into a scratch container.
2. Start the database.
3. Run a query that a human would recognise.
4. Compare the answer to what you expect, and alert if it is wrong.

Everything else is detail. A job that does the first three and reports success has told you the file opens. Only the fourth tells you the data is there.

What that looks like for Postgres

pgBackRest is the tooling most Postgres estates settle on, because it does incremental backups, parallel restore and point in time recovery from the same repository.

# restore into a throwaway data directory
pgbackrest --stanza=main --delta --target-timeline=latest   --pg1-path=/scratch/pgdata restore

# start it on a port nothing else uses
pg_ctl -D /scratch/pgdata -o "-p 5599" -l /scratch/restore.log start

Then the step that earns the job its place:

ROWS=$(psql -p 5599 -d app -tAc "select count(*) from orders where created_at > now() - interval '2 days'")
if [ "$ROWS" -lt 100 ]; then
  echo "restore verification FAILED: only $ROWS recent orders" >&2
  exit 1
fi

Pick a query whose answer you can reason about. Recent orders, active users, yesterday's invoices. A row count on a table that only ever grows tells you almost nothing; a count on a table that changes daily tells you the backup is current.

The failures that only appear on restore day

Roles and extensions are not in your dump. pg_dump writes one database. Roles, tablespaces and cluster settings are not in it. Restore onto a fresh server and every GRANT in the dump fails, because the roles do not exist. pg_dumpall --globals-only is the other half, and it is the half people forget to schedule.

Large objects. If your application stores files as Postgres large objects and your dump was taken with a table filter, they are not in the file. The restore succeeds and the attachments are gone.

The encryption key lives next to the backup. Encryption is worth having, and it is worth checking where the key is. A key in the same bucket, or in the same server's home directory, protects you from nothing that actually happens.

The backup came from a replica that had stopped replicating. A replica that fell behind three weeks ago still answers, still backs up, and still reports success. Only a restore plus a freshness query catches it.

Nobody knows how long it takes. The restore that works but takes eleven hours is a different product from the one that takes twenty minutes. Time the job and write the number down. That number is your real recovery time objective, whatever the document says.

For files, not databases

For file trees, restic covers the same ground.

restic -r s3:s3.example.com/backups check --read-data-subset=5%
restic -r s3:s3.example.com/backups restore latest --target /scratch/files

check --read-data-subset actually reads and verifies a slice of the data rather than only the metadata index, which is the difference between confirming the catalogue and confirming the contents. Running it over a percentage each night means the whole repository gets verified over a month without a nightly full read.

Lock the copies down

Two properties decide whether a backup survives the incident it exists for.

Separate credentials. The account your application uses must not be able to delete backups. If one set of leaked keys can both encrypt your production data and empty your backup bucket, you have one copy, not two.

Immutability. Object lock on the bucket, with a retention window longer than the time it typically takes to notice a problem. Ransomware and a bad DELETE both look the same to a storage API.

What to schedule

Weekly is enough for most estates, and it should run at a time a human is awake. A verification job that fails at 03:00 on a Sunday and pages nobody is another green tick waiting to be believed.

Once the job exists, the audit line changes from a question to a date. That is the whole point.

If you want this built and proven on your own estate rather than described, our disaster recovery and backup work starts with exactly this loop, and our infrastructure management engagements keep it running.