How to test that a pg_dump backup restores

A dump file that exists and has a sensible size can still fail to restore, for example when the copy stopped partway or the target server lacks a role the dump expects. You find out by restoring it. This guide does that by hand with the standard PostgreSQL client tools, into a database you create for the test and drop afterwards.

It assumes a custom-format dump from pg_dump -Fc and a Postgres server on the same machine where your login can create databases.

What you need

The client tools pg_dump, pg_restore, psql, createdb and dropdb on your PATH, and a local server to restore into. The restore writes a full copy of the database, so the server needs free disk for one more copy while the drill runs.

Use a pg_restore at least as new as the pg_dump that wrote the file. An older one refuses the archive with an error that starts with pg_restore: error: unsupported version. Restore onto the same Postgres major version you would use in a real recovery, so the drill tests the path you depend on.

1. Take the dump in custom format

$ pg_dump -Fc -d app -f app.dump

-Fc writes the custom archive format that pg_restore reads. A plain SQL dump (the default, -Fp) is replayed with psql instead and is outside this guide.

2. Read the archive's table of contents

$ head -c 5 app.dump; echo
PGDMP
$ pg_restore --list app.dump > app.toc
$ echo $?
0
$ grep -c ' TABLE DATA ' app.toc
$ grep -c ' INDEX ' app.toc

A custom-format archive starts with the bytes PGDMP. pg_restore --list prints the table of contents (TOC), one line per archived object with its type, schema, name and owner. Counting lines by type gives you an inventory you can compare from one night to the next. The counts are TOC entries, so they cover the tables and indexes the archive describes. Row counts only show up after a restore, in step 5.

3. Why a clean `--list` is not a restore test

The TOC sits near the front of the archive and the table data comes after it. A file cut off somewhere in the data still lists without complaint, and only a restore reaches the missing bytes.

$ head -c 150000 app.dump > app-cut.dump
$ pg_restore --list app-cut.dump > /dev/null; echo $?
0
$ head -c 2000 app.dump > app-tiny.dump
$ pg_restore --list app-tiny.dump > /dev/null
pg_restore: error: could not read from input file: end of file

Cuts of a 312,901-byte test dump on Postgres 17. Where a cut starts to break --list depends on how large your TOC is.

A half-finished copy to backup storage leaves exactly this kind of file. The 2,000-byte cut ends inside the TOC and fails at --list. The 150,000-byte cut passes --list and fails during the restore in the next step.

4. Restore into a throwaway database

$ createdb restore_drill
$ pg_restore --exit-on-error -d restore_drill app.dump
$ echo $?
0

Pick a database name that nothing else uses. pg_restore -d writes into whichever database you name, so a typo here can write into a real one.

Without --exit-on-error, pg_restore carries on past errors, prints a warning with the number of errors it ignored, and exits 1 at the end. With it, the restore stops at the first error and that error is the last thing printed, which makes the cause easy to read. Run the 150,000-byte cut through this step and it exits 1 with pg_restore: error: could not read from input file: end of file.

5. Check what came back

Exit code 0 means every TOC entry restored. Whether the data is what you expected is a separate question, so look at a table you know:

$ psql -d restore_drill -c 'SELECT count(*) FROM orders'

Compare a few row counts, or the newest timestamp in a busy table, with what production held when the dump ran.

When the restore fails, the first error usually tells you which side is at fault:

could not read from input file: end of file means the file is truncated. Copy it again or take a new dump.
unsupported version means your pg_restore is older than the pg_dump that wrote the file. Install newer client tools.
role "app_owner" does not exist means the dump names a role this server does not have. The archive is fine. Create the roles first (pg_dumpall --roles-only on the source server writes them out), or add --no-owner --no-privileges to the restore if ownership does not matter for the test.

6. Drop the database

$ dropdb --force restore_drill

--force needs Postgres 13 or later and ends any sessions still connected to the database. Drop it after a failed restore as well. Otherwise half-restored drill databases collect on the server and take up disk.

Running it after every backup

f=/backups/app-$(date +%F).dump
db=restore_drill_$(date +%s)
pg_restore --list "$f" > /dev/null || exit 1
createdb "$db" || exit 2
pg_restore --exit-on-error -d "$db" "$f"
rc=$?
dropdb --force "$db"
exit $rc

Example script. Run it after the backup job and alert on a non-zero exit.

A timestamped database name keeps two runs from colliding. The script drops the database whatever the restore returned, then exits with the restore's code.

Doing the same with pg-witness

pg-witness is a Linux amd64 CLI that runs this drill on your machine. It checks the PGDMP header and hashes the file, reads the TOC with pg_restore --list, restores into a randomly named database with --exit-on-error and drops it afterwards, even when the restore failed. It refuses any Postgres target that is not local, so the dump never leaves the machine.

Each run writes a JSON evidence file with the dump's SHA-256, TOC counts by type, the pg_restore exit code and a status. A missing role is reported as environment and a broken archive as dump_bad, with matching exit codes for scripts. It costs $99 once. Checkout is closed right now; the homepage shows the evidence files it wrote for these test dumps.