Verify a PostgreSQL backup by restoring it

A backup file can exist and still fail to restore. Test recovery into a separate disposable database, then check the records and constraints that the application needs. File size and a successful dump exit code are useful observations, but they do not show that the restored application can work.

Use an isolated destination

For an operator-controlled test database, a custom-format archive supports restoration with pg_restore. These commands are examples, not commands to run against an existing production database. source_fixture and restore_fixture must be deliberately created throwaway databases.

pg_dump --format=custom --file=fixture.dump source_fixture
createdb restore_fixture
pg_restore --exit-on-error --dbname=restore_fixture fixture.dump

Check the selected server and client versions before starting. Store connection credentials through the operator's approved mechanism. An archive can contain sensitive application data, so a recovery exercise also needs controlled storage and a cleanup policy.

Restore the archive into a separate disposable database, then check known records and constraint behavior.

Compare known fixture facts

Create a small synthetic source table with a primary key and a relationship to another table. Insert a few named rows whose expected values are written in the exercise notes. After restoration, compare those exact values, count the rows, and attempt an insert that should violate the relationship. Confirm that the restore recovered enforcement as well as data.

A count alone can pass when the wrong records were restored. For a larger exercise, compare selected totals and application invariants that the team understands. Keep the checks reproducible so that a later restore can repeat them without relying on someone's memory of the source state.

Record recovery limits

Logical database dumps do not automatically recreate every surrounding dependency. The application may also require uploaded files, external configuration and suitable roles. Inventory those dependencies and test them with the restored application. A database-only exercise should say that its scope ends at the database boundary.

The example above was reviewed against PostgreSQL's SQL dump documentation. PostgreSQL tools were unavailable in the authoring environment, so no dump or restore was executed here. On the target test host, record command exit codes, elapsed recovery time and the result of each data check. Avoid extrapolating a tiny fixture's timing to a full production database.

Keep the original backup untouched during the exercise and write any diagnostic exports elsewhere. Reference: PostgreSQL SQL dumps and restoration. Related operational notes appear in the engineering blog.