How to verify PostgreSQL backups

Why a backup file is not proof

Backups fail quietly. A pg_dump job can exit early when a connection drops, a disk fills up, or a role loses permission on one schema. The file still lands in storage, the job still looks green, and nobody notices until the day a restore is needed.

Verification closes that gap. It answers two questions before an incident does: can this archive be read, and does it actually contain the data you expect?

Step by step

1. Take the backup in custom format

   The custom format is compressed, restores selectively, and can be inspected without restoring. Plain SQL dumps cannot be listed by pg_restore.

   pg_dump --format=custom --file=app.dump "$DATABASE_URL"

2. Record a checksum

   Store a SHA 256 checksum next to the archive so you can prove the file was not truncated or altered in storage. Check it again before any restore.

   sha256sum app.dump > app.dump.sha256
   sha256sum --check app.dump.sha256

3. Inspect the archive with pg_restore --list

   Listing reads the archive table of contents, so a damaged header fails here. Count the TABLE DATA entries and compare with the tables you expect. A file cut off partway through its data can still list cleanly, which is why the restore step below matters.

   pg_restore --list app.dump > app.toc
   grep -c " TABLE DATA " app.toc

4. Restore into a scratch database

   Restore into an isolated database that nothing else uses. Use --exit-on-error so a single failed statement fails the test instead of scrolling past.

   createdb scratch_verify
   pg_restore --exit-on-error --no-owner --dbname=scratch_verify app.dump

5. Compare tables and row counts

   Check that every expected table exists and that the tables your business depends on have data. Exact counts on critical tables are the most reliable signal.

   psql scratch_verify -c "SELECT count(*) FROM orders;"
   psql scratch_verify -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';"

6. Drop the scratch database

   Remove the restored copy when the test passes. Scratch copies contain production data, so treat them with the same access controls as production.

   dropdb scratch_verify

What to check after the restore

A restore that finishes without errors is a good start. These checks catch the quieter failures:

• Every table from the archive list exists in the scratch database
• Critical tables such as users, orders, or payments are not empty
• Row counts are close to production for tables that change slowly
• Extensions, sequences, and functions your application needs are present
• The backup is recent enough to meet your recovery point objective

Common reasons PostgreSQL backups fail verification

Most failures come from permissions and scope rather than corruption. The backup role cannot read a newly added schema, a table owned by another role is skipped, or a restore fails because the scratch server is missing an extension the archive expects.

Estimated counts from pg_stat_user_tables are zero or stale right after a restore until ANALYZE runs, so use exact counts on critical tables instead of relying on statistics.

How Senal Recover automates this

• Senal Recover runs pg_dump in custom format on your schedule and inspects every archive with pg_restore --list before it becomes a restore candidate.
• On the Business plan, tier two validation restores each backup into your own scratch database and compares the restored tables and row counts with the archive, flagging missing, extra, and empty tables.
• Drift detection flags a backup whose size or duration changes sharply from recent history, and a failed validation raises an alert through your existing channels.