Common symptoms

  • pg_restore throws "ERROR: could not find file for relation with OID" or unknown type errors
  • pg_dump finished without apparent error but the file is incomplete or abnormally small
  • PITR cannot reach the desired point because intermediate WAL files are missing
  • pg_basebackup finished but there are errors starting PostgreSQL on the copied data
  • The restore process takes hours and fails in the middle of importing a large table
  • The restored database starts, but tables are empty or missing recent data

Business risks

  • Discovering the backup doesn't work during a real incident is the worst possible moment
  • Without a valid backup, corruption or human error can be irreversible
  • The measured RTO (recovery time) may be much greater than the business-approved RTO
  • Untested backups don't meet audit requirements or regulatory frameworks such as SOC 2, ISO 27001 or financial regulations

Detailed technical checklist

  • 1. Verify the backup format (custom vs plain text) Plain text format (-Fp) is not recommended for production: it doesn't allow selective restore or efficient compression. Custom (-Fc) or directory (-Fd) format are the correct choices.
  • 2. Include global roles and configurations pg_dump does not export users, roles or global configurations. You need pg_dumpall --globals-only.
  • 3. Verify extensions available on the destination server If the source uses extensions like postgis, pg_trgm or uuid-ossp, they must be installed on the destination before restoring.
  • 4. Verify tablespaces and their paths If the database uses custom tablespaces, the paths must exist on the destination server. Use --tablespace-map in pg_restore if paths differ.
  • 5. Perform a test restoration on an isolated server This is the only real test. It should be done at least monthly.
  • 6. Validate record counts on critical tables Compare counts between production and the restored database to detect missing data.
  • 7. For PITR: verify WAL file continuity No WAL segments should be missing between the base backup and the target recovery point.
  • 8. Verify that data_checksums was enabled on the source Page checksums allow detecting silent corruption. If they weren't enabled, the backup may contain corrupted pages without knowing it.
  • 9. Measure the actual restoration time Document how long the full restore takes. If it exceeds your approved RTO, you need to adjust the strategy (PITR, standby replica, etc.).
  • 10. Automate backup integrity verification A backup without automatic integrity verification is not reliable.
TERMINAL 
# A real restore test, not just pg_dump
pg_restore --list backup.dump | head -20
pg_restore -d restore_test_db backup.dump

When to escalate to a specialist DBA

  • There is no valid and verified backup of the production database
  • The last valid backup is older than the RPO approved by the business
  • There is data or page corruption on the production server without a reliable backup
  • The WAL files for PITR are incomplete or the archive_command has been silently failing
  • The measured restoration time significantly exceeds the business RTO

Frequently asked questions

How often should I test backup restoration?

At least once a month for critical systems. The test should be done on an isolated server and verify: total restoration time, object integrity (tables, functions, indexes), data from the latest transactions, and that the application works correctly against the restored database. An untested backup is not a reliable backup; it's just a hope.

What is the difference between pg_dump and pg_basebackup?

pg_dump is a logical backup: it exports the schema and data as SQL or in binary format. It is portable between major PostgreSQL versions and allows selective restorations by table or schema. pg_basebackup is a physical backup: it copies the cluster files directly at the block level. It is faster for large databases, enables PITR and is the basis for configuring streaming replicas, but is not portable between major versions.

Why does the backup appear correct but fails on restore?

The most common causes are: the dump was interrupted and the file is incomplete but the script didn't verify pg_dump's exit code; extensions from the source server are not installed on the destination; tablespaces point to paths that don't exist; objects that depend on functions or types created in extensions not installed; or differences in the PostgreSQL version that make some storage option or data type incompatible.

What is PITR and when do I need it?

PITR (Point-in-Time Recovery) allows restoring PostgreSQL to the exact state it had at a specific moment in the past, combining a physical base backup with subsequent WAL files. You need it mainly when a human error (accidental DELETE or DROP TABLE) or a failed process modifies data incorrectly and you need to recover the database state just before the error, with second-level precision. Requires archive_mode = on and a working archive_command.

Related resources