The method

How to actually test a Postgres backup

Every restore disaster starts with months of dumps that "worked". Truncated files, missing extensions, version mismatches and empty tables all look identical in a bucket listing. This is the drill that exposes them — in five steps, with the exact commands.

Step 0 — capture a manifest at dump time

A restore can only be verified against what the database contained when you dumped it. Record it in the same run:

psql "$DB_URL" -At -c "
  SELECT c.relname, s.n_live_tup
  FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
  ORDER BY 1;" > manifest.txt
pg_dump -Fc -f backup.dump "$DB_URL"
sha256sum backup.dump > backup.dump.sha256

n_live_tup is an estimate; for tables where exactness matters (billing, users), add real count(*) lines. The checksum catches truncated uploads — the most common silent failure.

Step 1 — restore into a disposable container

Match the Postgres major version of the source, and use client tools at least as new as the dump (why):

docker run -d --name drill -e POSTGRES_PASSWORD=drill -p 5499:5432 postgres:17
sha256sum -c backup.dump.sha256
pg_restore --no-owner --exit-on-error \
  -h localhost -p 5499 -U postgres -d postgres backup.dump

--exit-on-error matters: by default pg_restore logs errors and keeps going, and a "successful" restore can be missing objects. If you expect known, acceptable errors (ownership, extensions your platform pre-installs), drop it and read the error list instead — deliberately, not by ignorance.

Step 2 — verify against the manifest

psql -h localhost -p 5499 -U postgres -At -c "
  SELECT c.relname, s.n_live_tup
  FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
  ORDER BY 1;" > restored.txt
diff manifest.txt restored.txt && echo "COUNTS OK"

Then the structural checks:

-- any constraint that failed to validate?
SELECT conname FROM pg_constraint WHERE NOT convalidated;
-- sequences behind their tables? (classic partial-restore symptom)
SELECT s.relname FROM pg_class s WHERE s.relkind = 'S';
-- expected extensions present?
SELECT extname FROM pg_extension;

Step 3 — probe with domain queries

Counts can match while the data is wrong. Two or three queries that encode what "healthy" means for your app catch what generic checks can't:

SELECT count(*) > 0 AS has_users        FROM users;
SELECT max(created_at) > now() - interval '7 days' AS recent_activity FROM orders;

Step 4 — record and destroy

echo "$(date -u +%FT%TZ) drill OK: restore + counts + probes" >> drill-log.txt
docker rm -f drill

The log is the point. "When did you last test a restore?" should have an answer with a date in it.

How often?

Minimum: monthly, and after any schema migration.
Honest: every backup. That's what dodges the truncated-upload and version-drift failures, which don't wait for month-end.

Nobody runs this by hand every day. That's exactly why we automated it.

This drill, on every backup, automatically

pgProof runs steps 0 through 4 on every scheduled backup — manifest, version-matched container, counts, constraints, your probe queries — and emails you the proof. Failures alert immediately. Works with Supabase, Neon, Railway, Render and self-hosted.

Join the early-access waitlist