How to verify a pg_dump backup

For a custom-format dump (pg_dump -Fc), listing the archive reads its table of contents. A truncated or corrupt archive fails here with an error such as "could not read from input file: end of file".

pg_restore --list backup.dump | head

For a plain SQL dump, a complete file ends with a closing comment. Look near the end of the file:

tail -n 10 backup.sql | grep 'database dump complete'

Also record a checksum when the backup is written and compare it after you download it, and compare the size to the previous backup. A dump that is suddenly a tenth of its usual size is a finding.

sha256sum backup.dump

A file that lists correctly can still fail to restore. Restore into a throwaway database on a scratch server, never into production, and stop at the first error.

createdb restore_check
pg_restore --dbname=restore_check --exit-on-error --no-owner backup.dump

For a plain SQL dump, run it in a single transaction and stop on error:

createdb restore_check
psql --dbname=restore_check --single-transaction -v ON_ERROR_STOP=1 -f backup.sql

• Any extension the dump uses must be installed on the scratch server, or the restore will fail. That failure is exactly what you want to find now.
• Use a pg_restore at least as new as the pg_dump that wrote the file.
• For a large custom or directory-format dump, add --jobs=4 to restore in parallel, and time it.

A restore that finishes without errors can still hold the wrong data. Ask the restored database questions whose answers you already know. Change the table and column names to yours.

psql -d restore_check -At -c "SELECT count(*) FROM orders;"
psql -d restore_check -At -c "SELECT max(created_at) FROM orders;"
psql -d restore_check -At -c "SELECT count(*) FROM users WHERE email IS NULL;"

• The row count should be within the range you expect. An empty table in a restored database is a failed backup.
• The newest timestamp should be recent. An old maximum means the backup is stale or came from the wrong place.
• Columns that must never be null should have no nulls.

On PostgreSQL 14 and later you can also scan the restored indexes and tables for corruption:

pg_amcheck --database=restore_check

Run the same steps every time, in a fresh database that is removed afterwards. Edit the table name for your schema.

#!/usr/bin/env bash
set -euo pipefail
DUMP="$1"
DB="restore_check_$(date +%s)"

pg_restore --list "$DUMP" > /dev/null
createdb "$DB"
trap 'dropdb --if-exists "$DB"' EXIT

time pg_restore --dbname="$DB" --exit-on-error --no-owner "$DUMP"

rows=$(psql -d "$DB" -At -c 'SELECT count(*) FROM orders')
[ "$rows" -gt 0 ] || { echo "restored orders table is empty" >&2; exit 1; }
echo "ok: $rows orders restored"

• Physical base backups taken with pg_basebackup are checked with pg_verifybackup, which verifies the backup against its manifest. That confirms the files are intact; it does not restore them.
• A script on a cron job that nobody watches fails silently too. Alert on failure, and also on the script not running at all.
• A log line is not evidence. If an auditor or insurer asks, you need a dated record of what was restored, how long it took and which checks passed.

Dokaz runs this loop for you on a schedule against your PostgreSQL backups, in an isolated sandbox that never touches production, and produces a signed report you can hand to an auditor.