How to Test a PostgreSQL Backup Before You Need It
The first time you restore a production backup should not be during an outage. A backup test is a controlled recovery: restore into an isolated target, reject partial success, compare the result with the source, and keep evidence. Anything less proves only that a file was written.
Why a valid dump can still fail you
A dump may be readable and still be operationally useless. Missing roles can break ownership. Absent extensions can stop schema creation. A version mismatch can surface incompatible syntax. A restore may log errors, continue, and leave behind a database that looks populated but is incomplete.
PostgreSQL documents that pg_dump creates a consistent snapshot without blocking readers or writers. It also documents the boundary: pg_dump exports one database and does not include cluster-wide roles and tablespaces. A restore plan must account for everything outside that boundary.
1. Capture a source inventory
Before restoring, record what “complete” means. At minimum, capture the server version, extensions, schemas, tables, sequences, views, functions, row counts, and ownership expectations. For products built on Postgres, add domain checks such as user counts, tenant counts, or object inventory.
Take the inventory at the same logical point as the backup. Comparing a restored nightly dump with a live database the next afternoon produces noise, not evidence. Store the source inventory beside the backup artifact and identify both with the same run ID.
2. Restore into a clean, isolated target
A clean target exposes hidden dependencies. Restoring repeatedly into a long-lived test database can succeed only because an extension, role, or table survived a previous run. Provision a disposable database for each verification and remove it afterward.
- Match the production PostgreSQL major version unless you are deliberately testing an upgrade.
- Block application traffic and outbound integrations from the verification environment.
- Use temporary credentials and never send production emails, webhooks, or jobs.
3. Make restore errors fatal
For a custom-format archive, use pg_restore with an error policy that cannot silently leave a partial result. PostgreSQL’s --single-transaction option executes the restore as one transaction and implies exit-on-error: either all commands complete or none are applied.
If the database is too large for one transaction, use a deliberate alternative: fail on the first SQL error, retain complete logs, and discard the target on failure. The robust property is atomic interpretation of the result, not loyalty to one command-line flag.
4. Compare structure and data
Start with inventory because it catches broad omissions quickly. Compare schemas, relations, extensions, and row counts. Then add checks that represent business value. A table count cannot tell you whether the latest subscription exists or whether a restored user can access the correct tenant.
- Schema objects: names and types, with expected exclusions documented.
- Data: row counts plus targeted invariants and representative records.
- Security: roles, ownership, grants, and row-level-security policies.
- Sequences: next values must not collide with restored rows.
- Large objects and external files: count, size, and checksum where applicable.
5. Run application-level recovery checks
Database equivalence is necessary but not sufficient. Point a safely isolated application build at the restored target and exercise a short recovery script: authenticate, read a core record, perform a transaction, verify permissions, and retrieve a file if the product uses object storage.
Keep the smoke test small and deterministic. Its purpose is to catch broken contracts between the application and the restored state, not to replace the full test suite.
6. Measure and retain evidence
Record artifact identity and checksum, source and target versions, start and finish times, restore logs, inventory differences, smoke-test results, and the final disposition. This turns “we tested it once” into evidence that can be reviewed after a change or incident.
Track restore duration over time. A backup that restores correctly in six hours still fails a two-hour RTO. Growth in recovery time is an operational signal, just like growth in database size.
How often should you test?
Automate a structural restore test for every backup when the database size and cost make that practical. Run deeper application recovery drills on a slower schedule and after meaningful changes to versions, extensions, backup tooling, or infrastructure. The test frequency should follow the rate at which your recovery assumptions can change.
A result you can trust
Use three outcomes: verified, failed, or verified with explicitly accepted differences. Never turn unexplained differences into success just to keep the dashboard green. Expected exclusions should be versioned policy; everything else deserves investigation.
ReviveDB automates this restore-and-compare loop for managed PostgreSQL backups. Read the backup verification method or use the Supabase-specific backup guide if your Postgres database is part of a broader Supabase project.