How to prove a PostgreSQL backup restores

A PostgreSQL backup is proven only when it has been restored into a throwaway server and every table holds the rows it held at the moment of the dump. Below is the method in standard commands, so that you can run the same check on yours. Its comparison is the one our own weekly drill runs.

Count the rows inside the dump's own snapshot

A restore needs a target: the exact row count of every table at the moment of the dump. The n_live_tup figures in pg_stat_user_tables are estimates, not counts. And count(*) run before or after the dump cannot vouch for a table that gained and lost rows while the dump ran.

So take the dump and the counts from one snapshot: open a repeatable read transaction, export its snapshot, give it to pg_dump, and count every table inside the same transaction. This is snapshot.sql:

BEGIN ISOLATION LEVEL REPEATABLE READ, READ ONLY;
SELECT pg_export_snapshot() AS snap \gset
\setenv SNAP :snap
\! pg_dump -Fc --snapshot="$SNAP" -d appdb -f appdb.dump; echo $? > dump.rc
\i counts.sql
COMMIT;

counts.sql writes and runs one exact count per table:

SELECT format('SELECT %L, count(*) FROM %I.%I',
              n.nspname || '.' || c.relname, n.nspname, c.relname)
  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('r', 'p') AND c.relpersistence <> 't'
   AND n.nspname NOT IN ('pg_catalog', 'information_schema') \gexec

Run it with psql -X -tAq -v ON_ERROR_STOP=1 -d appdb -f snapshot.sql > counts.txt, and keep counts.txt with the dump: every later drill compares against it.

psql does not expand variables on a \! line, so \setenv carries the snapshot id. A failed pg_dump does not stop psql, so its exit status goes to a file you check. Materialized views are left out: the restore refreshes them, so a stale one would look like a mismatch.

Dump the roles too, with pg_dumpall --globals-only > globals.sql. The dump names owners and grants by role, and a restore with --exit-on-error stops at the first role the new server lacks. The file holds password hashes, so guard it like the dump.

Check the files before you restore them

Checksum every file when the backup is made, and check them all before the drill uses any:

sha256sum appdb.dump globals.sql counts.txt > SHA256SUMS
sha256sum -c SHA256SUMS

Count the OK lines against the number of files, because a checker given nothing to read can still exit 0. If the offsite copy is encrypted, check the encrypted files first, then decrypt with the key you would use after losing the server.

Check the dump itself too: larger than a floor you measured, with a TABLE DATA entry in pg_restore -l appdb.dump for a table that must be there.

A decrypted dump is your whole database in plain text: write it under umask 077 and delete it when the drill ends, failed or not.

Restore on the production major version

Read the version from production on every run, with SHOW server_version;, and restore into a throwaway server of that major version. A restore into another version proves that version, not yours. In recovery order:

psql -X -d postgres -f globals.sql
createdb -O app_owner appdb_drill
time pg_restore --exit-on-error -d appdb_drill appdb.dump

An error that the superuser role already exists is normal on the first line. Create the database with the same owner as production. Without --exit-on-error, pg_restore carries on past errors and counts them at the end, leaving a partial database to compare.

Settings made with ALTER DATABASE ... SET come back only with pg_restore --create. If you create the database yourself, reapply them as a written step and check them on the copy.

Compare every table, and time it

Then count the restored copy and compare:

psql -X -tAq -v ON_ERROR_STOP=1 -d appdb_drill -f counts.sql > restored.txt
grep '|' counts.txt | LC_ALL=C sort > before.txt
grep '|' restored.txt | LC_ALL=C sort > after.txt
diff before.txt after.txt && test -s after.txt && echo PASS

diff prints every table whose count differs or that exists on one side only. test -s fails the drill when nothing was restored, since two empty lists are equal.

Write down how long the restore and the comparison took: a real recovery takes at least that long, plus fetching and decrypting the backup.

Our own PostgreSQL drill runs this comparison: on 1 November, 113 tables and 850,716 rows, 0 mismatches. It has passed every Sunday since 4 October: our latest results.

Write the steps down

Keep the drill in a script, with a document beside it: the production version, the restore order, the owner, the settings, and where the decryption key lives. The first runs tend to fail on something nobody wrote down, such as a path, a readiness check or a schema name; each one becomes a step.

Each run appends one line to a log: the date, PASS or FAIL, tables, rows, mismatches and time, and on a failure the step that failed.

When it fails

• Keep the night's backup. Its files were checked on their own; a failed drill should not hold them back.
• Send a person one alert, with the failed step and the first lines of its error. Record a pass quietly.
• Clean up on every exit: the drill database is a full copy of production. A container needs docker rm -v, or its anonymous volume keeps the data.
• Treat the backup as unproven until a drill passes. Fix the cause, run the drill again the same day, and add the fix to the written steps.

MySQL: restore into the version production runs

Read the version from the running production server on every run, with SELECT VERSION();, and restore into exactly that version. Our production MySQL moved from 8.0 to 8.4, and for two Sundays our drill still restored into 8.0, a version we would never restore into. It now reads the version first: MySQL 8.4, 227 tables, in our latest results.

Wait for a real login, not a ping: a new MySQL container can answer a ping from the temporary server it runs during setup.

A dump made with --all-databases or --databases creates its own schemas; look for the tables there. Count them in information_schema.tables, and read the newest rows back to show the copy is recent. TABLE_ROWS is an estimate for InnoDB, so do not compare it.

Nexidy can run the same drill on your database: read-only on production, every step written down for your team, and the report signed off by Hamed Kabir.