How to test a Postgres backup

A backup is tested only by restoring it. Restore it into a throwaway PostgreSQL of the same major version, with pg_restore stopping at the first error, then compare every table's row count, and the schema, with the database it came from. pg_dump exiting 0 says nothing about whether the restore will work.

Version

pg_dump refuses a server newer than itself. Dump with the server's version or newer, and restore into the same major version to test what a recovery would do.

Extensions

Every extension the backup creates must exist where it is restored (pgvector and PostGIS are separate packages). A table that needs a missing one fails to restore.

Roles

Row-level security policies name roles. Restore with --no-owner --no-privileges, and create the roles the policies name first.

Row-level security

pg_dump stops when RLS would hide rows from the role it runs as. Dump as the tables' owner or a role with BYPASSRLS.

Pooled connections

Dump over a direct or session-mode connection, not a transaction pooler.

Empty is not an error

A dump of the wrong database, or of a schema that was left out, exits 0. Only comparing the counts finds it.

The commands below do the test by hand. restoreproof does the same every week in your own GitHub Actions, with every table, column, constraint, index, view, function, trigger and policy compared too, and fails the job when the backup would not restore.

# 1. back up
pg_dump --format=custom --no-owner --no-privileges --file=backup.dump "$DATABASE_URL"

# 2. restore into a scratch database on a local PostgreSQL of the same major version
createdb restore_test
pg_restore --no-owner --no-privileges --exit-on-error --dbname=restore_test backup.dump

# 3. count every table on both sides, and compare
COUNT="SELECT format('SELECT %L, count(*) FROM %I.%I;', schemaname || '.' || relname, schemaname, relname) FROM pg_stat_user_tables ORDER BY 1"
psql "$DATABASE_URL" -XAtc "$COUNT" | psql "$DATABASE_URL" -XAt > live.txt
psql -d restore_test -XAtc "$COUNT" | psql -d restore_test -XAt > restored.txt
diff live.txt restored.txt && echo "every table came back with the same number of rows"

restoreproof: the same test, every week

restoreproof backs the database up in your own GitHub Actions, restores the backup into a throwaway PostgreSQL of the same version on the runner, and fails the job when it would not restore. Each run checks:

• The backup restores: pg_restore --exit-on-error into a throwaway PostgreSQL of the same major version, made on the runner with initdb, on 127.0.0.1 with a random password.
• Every table, column, constraint, index, view, function, trigger, row-level security policy, sequence, enum and domain of the source is in the copy, with the same definition.
• Every table's rows match the live count, taken in one READ ONLY snapshot just after the backup, give or take the rows written meanwhile (at most 10 or 10%). A table with rows that restores empty always fails.
• The database has not lost more than half its rows since the last good run, while last week's backup still holds them.

A recorded run, and what else exists.

restoreproof is a free tool from agentcheck, which runs scheduled checks of your endpoints and alerts you when an answer changes. Facts come from each provider's own pages, read on the day shown.