PostgreSQL backup verification and restore drill.

PostgreSQL backup verification fails when teams collapse four different questions into one: does the backup file exist, is it internally intact, can PostgreSQL restore it, and does the restored system satisfy the business recovery requirement? Each question needs separate evidence.

This runbook separates those layers. It applies to logical archives created with pg_dump, physical base backups created with pg_basebackup, and managed-service snapshots where the provider controls the physical backup mechanism. Adapt the commands to the PostgreSQL major version, backup format, extensions, encryption model, and recovery objective in your environment.

The four layers of PostgreSQL backup verification

A checksum cannot replace a restore. A successful restore cannot replace data reconciliation. A database that accepts connections cannot prove that the application, roles, scheduled jobs, or recovery point are correct. Treat each layer as a gate.

Verify a logical backup before restoring it

For custom or directory-format logical archives, begin by asking pg_restore to read the archive table of contents. This is a fast structural check and gives you an inventory for the planned restore.

pg_restore --list appdb.dump > appdb.restore.list
test -s appdb.restore.list

Review the list for expected schemas, extensions, tables, sequences, functions, ownership entries, and large objects. The PostgreSQL documentation explains that the output of --list can be edited and supplied to --use-list when a controlled subset or order is required. Do not remove an item merely because it looks unfamiliar; first identify its dependency and business purpose.

Restore into a clean target

Use an isolated target with the required PostgreSQL major version, extensions, locale, encoding, and storage capacity. Avoid restoring over a shared test database that already contains objects, because pre-existing state can hide ownership, extension, and schema-order failures.

createdb --template=template0 restore_validation
pg_restore \
  --dbname=restore_validation \
  --exit-on-error \
  --verbose \
  appdb.dump \
  2>restore_validation.log

pg_restore continues after SQL errors by default and reports an error count at the end. For a verification drill, --exit-on-error makes the first failure visible and prevents a partly successful restore from being reported as good enough. A single transaction can provide all-or-nothing behavior, but PostgreSQL notes that it may be impractical for databases with many objects because the restore holds a lock for each restored object.

Do not normalize warnings away

Classify every warning. Missing roles, unavailable extensions, ownership failures, incompatible settings, and permission errors are recovery dependencies, not cosmetic noise. If the production restore procedure intentionally remaps ownership or omits privileges, record that decision and test the exact command that operations will run.

Verify a physical base backup

pg_basebackup generates a backup manifest unless manifest generation is disabled. The manifest records expected files and optional checksums. PostgreSQL's pg_verifybackup checks the manifest, file presence and size, file checksums, and the WAL ranges required by the backup.

pg_verifybackup /backups/base/2026-07-23

Run the utility version appropriate for the backup and preserve its exit status and output. For tar-format base backups, extract the archive into an isolated directory before verification. Keep the manifest protected with the backup evidence; if an attacker can modify both the backup and its manifest, a checksum alone does not establish trust.

PostgreSQL explicitly warns that pg_verifybackup cannot perform every check a running server will perform. The next gate is still a test recovery: start a disposable cluster from the verified backup, apply the required WAL, and prove that PostgreSQL reaches the intended recovery target.

Run the restore like an incident, not a demo

A realistic drill starts with an approved recovery scenario and clock. Record who initiates recovery, who supplies credentials and encryption keys, where the target runs, how the backup is selected, which recovery point is requested, and who declares the database usable.

1. State the target: identify the database, backup ID, required recovery point, recovery time objective, and recovery point objective.
2. Prepare isolation: prevent the restored system from sending email, running production jobs, publishing events, or connecting to real downstream consumers.
3. Restore from documented instructions: use the same access path, tooling, roles, and secrets process expected during an incident.
4. Capture timing: separate provisioning, transfer, restore, WAL replay, validation, and handoff time.
5. Validate and reconcile: run technical and business checks before declaring success.
6. Dispose safely: preserve evidence, revoke temporary access, and securely remove restored sensitive data.

Validate the recovered database

Build a small, deterministic validation pack before the drill. It should be safe to run repeatedly and should fail clearly. Useful checks include:

• the expected PostgreSQL version, encoding, collation, and timezone;
• required extensions and their versions;
• expected schemas, tables, partitions, views, functions, and sequences;
• row counts or controlled aggregates for critical business tables;
• the maximum timestamp or known transaction at the recovery boundary;
• foreign-key, exclusion, and application-level integrity checks;
• roles, ownership, grants, row-level security, and default privileges;
• application login, one representative read path, and one isolated write path;
• scheduled jobs, replication settings, monitoring, and backup configuration that must be recreated outside the database.

Do not rely only on total row counts. A table can have the expected number of rows while containing the wrong recovery point, tenant, partition, or date range. Select a few business facts that an owner can recognize and that reveal whether the restored data is coherent.

Record recovery evidence that can be audited

A restore drill should produce a compact evidence pack. At minimum, retain the backup identifier, source environment, target environment, PostgreSQL and tool versions, command transcript, timestamps, exit codes, validation query results, errors and warnings, measured recovery point, measured recovery time, reviewer, and unresolved actions.

Separate the drill result from the follow-up status. A drill can complete while still revealing that the recovery objective was missed. Report that honestly: for example, "restore and validation succeeded in 3 hours 18 minutes; the approved RTO is 2 hours." The gap then has an owner and an engineering decision instead of disappearing behind a green backup dashboard.

A practical recurring schedule

Frequency should follow change rate and business consequence. A common pattern is continuous monitoring of backup completion, automated archive or manifest verification after every backup, a representative restore each month, and a broader service recovery exercise at least annually. Systems with strict recovery commitments, rapid schema change, or frequent release activity may need more frequent drills.

Trigger an additional drill after changing PostgreSQL major versions, backup tooling, encryption, object storage, retention policy, extensions, topology, cloud provider, identity controls, or the recovery runbook itself. A restore procedure is only proven for the combination that was actually tested.

Further reading

• PostgreSQL documentation: pg_verifybackup
• PostgreSQL documentation: pg_basebackup
• PostgreSQL documentation: pg_restore
• Database operations handover checklist

About the author: Can Goktug Ozdem is Datrick's founder and senior technical lead. He works on database operations, migrations, recovery readiness, and production support.