How to Verify a pg_dump Backup: Why --list Is Not Enough

pg_restore -f /dev/null rejected all 161,162 cuts I tried on custom-format archives up to 2.4 MB. pg_restore --list accepted a large dump cut to 25% of its size, and gzip -t passed nine cut plain dumps. The full read still accepts a complete dump of an empty database and some docker exec -t dumps, so add a size floor and a TABLE DATA count.

I ran this in a lab under WSL2. The byte cuts and the comparison of checks used Docker 29.6 and Postgres 18.6 containers directly on the host, and so did the directory-format run, the sed run on a healthy dump, and the multipart test. The other runs used Docker 29.9 and PostgreSQL 18.6 on Ubuntu 24.04 in a privileged container, with MinIO as the storage and a database that dumps to about 92 MB. Nothing ran on real R2, B2, or S3, so every rclone result below is a MinIO result.

These checks read the file and compare the upload, and a passing file can still be a complete dump of the wrong database. The full read, the size floor, and the upload comparison run inside pg-backup, the script in my off-site backups guide, and testing a Postgres restore covers whether an application can use the result.

How Do You Check a Dump Before Uploading It?

Read the whole archive with pg_restore -f /dev/null, compare its size with a floor, then compare the upload with the local file. This is that part of pg-backup, where $tmp is a private temp directory holding only the dump and $pg is the database container:

size=$(stat -c %s "$tmp/$name")
if [ "$size" -lt "$min_bytes" ]; then
  echo "dump is $size bytes, below the floor of $min_bytes" >&2
  exit 1
fi

# Reads and decompresses every data block. pg_restore --list would pass a truncated file.
docker exec -i "$pg" pg_restore -f /dev/null < "$tmp/$name"

rclone copyto "$tmp/$name" "$remote/$name"
rclone check --download "$tmp" "$remote" --one-way --include "$name"

Each check misses something, and two common ones passed damaged files:

The full-read result holds for custom format only. On Alpine, a cut directory-format dump still exited 0 (PostgreSQL bug 19754).

I added --download because plain rclone check compares a multipart object with the MD5 rclone stored in its metadata, not with the stored bytes. On MinIO, a multipart object with one flipped byte and the good MD5 in that metadata gave 1 matching files and exit 0. --download on that object gave contents differ and exit 1. With no stored hash, the plain check printed 1 hashes could not be checked and also exited 0.

A failed rclone check exits 1 and leaves the bad object in the bucket, as the half upload and the flipped byte both did, so a failure alert has to reach you.

rclone switches to multipart at 200 MiB by default. --download reads the whole object back, which adds time and any download charge your provider has.

Does gzip -t Prove a Plain SQL Dump Is Complete?

No. Plain SQL dumps need a different check. I dumped with pg_dump | gzip and ended the dump three ways, three runs each: pg_terminate_backend (term), kill -9 of pg_dump (kill9), and docker kill. All nine cut files passed gzip -t, because as I read it gzip saw the end of its input and closed a valid file. They also restored through psql with ON_ERROR_STOP and exit 0 (two of the nine shown):

good, 91568499 bytes: gzip -t 0; psql exit 0; orders 1500000|items 2000000|audit_log 200000|indexes 3
term run 1, 29279705 bytes: gzip -t 0; psql exit 0; orders 0|items 1025508|audit_log 200000|indexes 0
kill9 run 2, 35250337 bytes: gzip -t 0; psql exit 0; orders 0|items 1271039|audit_log 200000|indexes 0

orders came back empty, items came back partial, and all three indexes were gone. Only the missing completion comment at the end of the file shows it. This prints 1 on a complete plain dump and 0 on the cut one I tried:

gunzip -c old.sql.gz | tail -n 5 | grep -c 'database dump complete'

Why Does docker exec -t Break a Dump?

A terminal turns every LF byte into CR LF, which wrecks a binary archive. docker exec -t with pg_dump -Fc gave a corrupt file in all 47 runs I counted, 43 of them with the clock faked by libfaketime to vary the date bytes in the archive header. The November 17 dates below come from that clock. pg_dump exited 0 every time, and pg_restore gave mixed answers (trimmed):

app_3                  2026-11-17 22:41:50   dump-exit 0  full-read 0  list 0  TABLE-DATA 0  TOC Entries: 0
billing_3              2026-11-17 22:41:50   dump-exit 0  full-read 0  list 0  TABLE-DATA 0  TOC Entries: -327795
shop_3                 2026-11-17 22:41:50   dump-exit 0  full-read 139  list 139  TABLE-DATA 0
lab-shop               2026-11-05 14:55:37   dump-exit 0  full-read 1  list 1  TABLE-DATA 0

full-read is the exit code of pg_restore -f /dev/null, and 139 is a segfault. An exit code alone can't clear such a dump, and a TABLE DATA count of 0 caught all of them.

If an old script used -t, check your dumps. A 0 from the second line means no table data, and shop-postgres-1 is my lab's container, so use yours:

docker exec -i shop-postgres-1 pg_restore -f /dev/null < old.dump; echo "full read exit $?"
docker exec -i shop-postgres-1 pg_restore --list < old.dump | grep -c ' TABLE DATA '

Run this repair only on a dump whose count is 0, and keep the original:

LC_ALL=C sed 's/\r$//' old.dump > repaired.dump

On a mangled dump it matched the control's size with both checks passing. That was one run with no hash compared, so restore the result before you rely on it.

On a healthy 61 MB dump the same sed removed 719 bytes. The full read then exited 1, while --list still counted its table.

Summary

pg_restore -f /dev/null rejected every cut of a custom-format archive I tried, while pg_restore --list and gzip -t passed damaged files. Pair the full read with a size floor, because it accepts a complete dump of an empty database, and compare the upload with rclone check --download. A TABLE DATA count catches the docker exec -t dumps that the full read and the floor both pass. On plain SQL, look for the completion comment at the end of the file, and leave -t off docker exec.

The results come from a lab with MinIO and Postgres 18.6 containers, not a real provider, and the full-read result holds for custom format only. Run the checks on your real provider and server before you count on them.

The full read, the size floor, and the upload comparison run inside my off-site backup script, and my self-hosting course sets up database backups to storage outside the server.

Thanks for reading.

And stay awesome!