Problem
A database backup is easy to create and easy to trust too early. The backup is only useful if it can be listed, restored, and checked before an incident.
Mechanism
pg_dump can create a portable custom-format dump. pg_restore -l lists the dump contents, which is a fast first check that the file is readable and has the objects you expect.
pg_dump -h <HOST> -p <PORT> -U <USER> -d <DATABASE> -F c -f backup_YYYYMMDD_HHMMSS.dump
pg_restore -l backup_YYYYMMDD_HHMMSS.dumpFix
Restore into a separate database and compare table counts and table sizes against the source. Do this as a drill, not for the first time during an outage.
pg_restore -h <HOST> -p <PORT> -U <USER> -d <RESTORE_DATABASE> --clean --if-exists backup_YYYYMMDD_HHMMSS.dumpWhat to check
| Check | Why it matters |
|---|---|
pg_restore -l | Confirms the dump is readable |
| Row counts | Catches missing or partial table restores |
| Table sizes | Catches unexpectedly empty or bloated restores |
| Offsite upload | Protects against local disk loss |
Production lesson
Treat restore drills as part of backup creation. If nobody has restored the dump and compared the result, the backup is still just a hope.