Verify Database Backups by Restoring Them

Prove a PostgreSQL backup is usable by restoring it into an isolated database, comparing row counts and checksums, and recording the recovery time.

~45 mindatabasebackupoperations

Task

Replace "the backup job succeeded" with evidence that the backup restores and the application data is intact.

When to use this

A corruption or ransomware incident requires a restore. The backup files exist, but nobody has tested whether they are complete, readable, or usable within the recovery objective.

Prerequisites

A recent logical or physical backup
An isolated database instance for restore testing
Credentials that allow creating and dropping a scratch database
A list of critical tables and a checksum or row-count query

Steps

1. Create a fresh custom-format dump and record its checksum.

pg_dump -Fc -f backup.dump "$DATABASE_URL" && sha256sum backup.dump

Expected output: A readable dump file and a checksum stored with the backup metadata.

2. Restore into an isolated database, never over the production database.

createdb restore_test && pg_restore --no-owner --dbname=restore_test backup.dump

Expected output: pg_restore completes without errors and the scratch database contains the expected schemas.

3. Compare critical table counts and a deterministic checksum with the source.

psql "$DATABASE_URL" -c "SELECT count(*) FROM orders;" && psql restore_test -c "SELECT count(*) FROM orders;"

Expected output: Counts match the source at the backup point, and a checksum over ordered rows matches when the table is immutable.

4. Record restore duration and the point in time recovered, then drop the scratch database.

dropdb restore_test

Expected output: The drill produces a measured recovery time and a documented result.

Troubleshooting

Problem

pg_restore reports missing roles or extensions

Solution

Create the required roles and extensions in the restore target, or use --no-owner and install extensions before restoring data.

Problem

Counts differ from production

Solution

The backup was taken at a different time or a table is changing during the comparison. Compare against a consistent snapshot or a recorded backup timestamp.

Limitations

A logical dump verifies logical integrity, not physical page corruption; pair it with physical backup verification for large databases.
Restoring into a smaller instance may exceed the recovery time objective; measure on representative hardware.

A backup is a hypothesis until restored

Backup jobs fail silently in many ways: the dump is truncated, the encryption key is unavailable, the storage bucket is empty, or the restore target lacks an extension. The only reliable verification is a restore into an isolated environment followed by data checks. Schedule it, assign an owner, and record the result.

pg_dump -Fc -f backup.dump "$DATABASE_URL"
createdb restore_test
pg_restore --no-owner --dbname=restore_test backup.dump
psql restore_test -c "SELECT count(*) FROM orders;"

Compare meaningful data

Row counts catch a missing table but not a truncated column or a corrupted value. For immutable tables, compare a deterministic checksum over ordered rows. For mutable tables, compare against a consistent snapshot or accept a documented time skew. Check sequences, indexes, constraints, and extensions too; an application may restore rows but fail on a missing sequence.

Measure the recovery objective

Record how long the restore took, how much data was recovered, and the point in time represented by the backup. Compare that with the recovery time and recovery point objectives. A backup that restores in six hours does not meet a two-hour objective, even if it is technically valid. Test on hardware comparable to production, not only on a laptop.

Protect the drill

Use a dedicated restore environment, not production. Restrict who can read backups and encryption keys, and audit restore access. After verification, drop the scratch database and securely remove temporary dumps. A backup verification process that leaks a full copy of production data is a new incident, not a control.