You have a cron job that runs pg_dump every night. That is not a backup strategy. A backup strategy includes proof that the dump can become a working database again. Until you have run the restore, you have a file, not a backup.

Why dumps silently rot

A pg_dump can complete with exit code 0 and still be useless. Common ways this happens:

• The dump ran against a replica mid-failover and caught a half-committed state.
• A extension (postgis, pgvector) is installed on production but missing on the restore target, so the restore aborts partway through.
• Disk filled up during the dump and it truncated silently if you were not checking exit codes and file size.
• Someone changed the pg_dump user’s permissions and it started skipping tables it could not read, with only a warning in stderr nobody reads.

None of these show up until you actually try to load the file into a database and query it.

The drill

Run this monthly, on a schedule, not “when you remember.”

1. Take the backup the same way production does

pg_dump -Fc -h prod-db.internal -U backup_user -d appdb -f /backups/appdb-$(date +%F).dump

-Fc (custom format) is what you want for anything beyond a toy database — it’s compressed and supports parallel restore.

2. Spin up a throwaway Postgres

docker run -d --name restore-drill -e POSTGRES_PASSWORD=drill -p 5433:5432 postgres:16

Use the same major version as production. Restoring a 16 dump into a 14 instance is a fresh way to lose an afternoon.

3. Restore it

pg_restore -h localhost -p 5433 -U postgres -d postgres \
  --create --jobs=4 /backups/appdb-2026-01-27.dump

--jobs parallelizes table restores — meaningfully faster on anything with more than a handful of tables.

4. Verify, don’t eyeball

Eyeballing “yep, tables are there” is not verification. Check row counts against production and check that a handful of specific, known rows exist:

psql -h localhost -p 5433 -U postgres -d appdb -c \
  "SELECT count(*) FROM users; SELECT count(*) FROM orders;"

Compare against the same query run on production at backup time. A count that’s off by more than your expected write volume during the dump window means something is wrong.

5. Tear it down and log the result

docker rm -f restore-drill

Write down: dump size, restore duration, row count match, pass/fail. Three months of this log tells you if your backup time is creeping toward your maintenance window.

Automate it

Doing this by hand monthly is fine for a while, then it’s the thing that slips during a busy sprint. Put steps 1–4 in a script triggered by cron or a CI schedule, and have it fail loudly (page you, not just log) if the row-count check fails or the restore errors out.

#!/bin/bash
set -euo pipefail
# ... dump, restore, count ...
if [ "$prod_count" != "$restored_count" ]; then
  curl -X POST "$ALERT_WEBHOOK" -d "restore drill failed: row count mismatch"
  exit 1
fi

What this doesn’t cover

Point-in-time recovery (PITR) via WAL archiving is a separate concern from logical dumps, and if you need to recover to “3:47pm yesterday” rather than “last night’s dump,” you need pgbackrest or wal-g with continuous WAL shipping, not just pg_dump. If your RPO is measured in hours, dumps are enough. If it’s measured in minutes, set up WAL archiving and drill restoring that too — the failure modes are different and just as silent.

The point stands either way: a backup you have not restored is a hypothesis.