How to test a PostgreSQL backup restore (and automate it)

• The backup restores without errors.
• The restored data is correct: the right database, complete and recent.
• Recovery fits inside your time objective, so you record how long it took.
• The test is repeatable, so next month's result is comparable to this month's.

Never restore a test into production, and do not reuse a long-lived staging database that other people depend on. Use a throwaway server, and match the PostgreSQL major version you run in production. A container is the quickest way:

docker run --rm -d --name pgtest -e POSTGRES_PASSWORD=test -p 55432:5432 postgres:16
pg_restore -h localhost -p 55432 -U postgres -d postgres --exit-on-error --no-owner backup.dump
docker stop pgtest

With a managed provider, restoring a snapshot or a point in time normally creates a new instance. Run your checks against that new instance, then delete it.

Decide the checks once, in a file under version control, so the test does not depend on someone remembering them.

• Row counts for your most important tables, within an expected range.
• Key tables and columns exist, so schema drift is caught.
• Critical columns contain no nulls.
• The newest row is recent, so a stale backup is caught.
• On PostgreSQL 14 and later, pg_amcheck passes on the restored database.

A test you run by hand when you remember is not a control. Run it weekly at minimum, for example:

0 4 * * 0 /opt/verify-backup.sh /backups/latest.dump >> /var/log/backup-verify.log 2>&1

Alert in two cases: the test fails, and the test did not run. The second is the one people forget, and it is how a broken scheduler stays broken for months. A heartbeat or dead-man's-switch check handles it.

For each run, keep the date, which backup was restored, how long the restore took, which checks ran and their results. For SOC 2, ISO 27001 or a cyber-insurance renewal, this dated record is the evidence. Store it somewhere that is not the system being tested, and make it hard to edit after the fact.

Dokaz does this for PostgreSQL: each drill restores your backup into a single-use sandbox on an isolated network, runs assertions on the restored data, and produces a signed Proof-of-Recovery report that anyone can verify offline with no Dokaz software. It never connects to your production database.