Database Backup and Recovery with pg_dump and Point-in-Time Recovery

IntermediateDatabases~4 hours
pg_dumpWALPITRRPO/RTO

I implemented both logical backups with pg_dump and physical point-in-time recovery via WAL archiving, then tested restores against defined RPO and RTO targets. A backup is only real once its restore is verified, so every backup type was exercised end to end.

Objective & Context

Backups exist to be restored. This lab pairs logical dumps (portable, slow) with WAL-based PITR (granular, fast recovery to a timestamp), defining recovery point and recovery time objectives and proving them with actual restores.

Environment & Prerequisites

  • PostgreSQL 16 with WAL archiving configured.
  • Backup storage separate from the data volume.
  • Defined RPO/RTO targets to validate against.

Step-by-Step Execution

1. Logical backup with pg_dump

pg_dump -Fc appdb -f /backups/appdb-$(date +%F).dump

2. Base backup for PITR

pg_basebackup -D /backups/base -Fp -Xs -P

3. Restore and verify

pg_restore -d appdb_test /backups/appdb-2026-06-17.dump && psql appdb_test -c "SELECT count(*) FROM orders;"
 count
-------
 81234

Validation & Testing

Restore to a scratch database and to a specific point in time, then compare row counts and checksums against the source. Pass criteria: restores complete within the RTO, data loss stays within the RPO, and verification queries match expected values.

Advanced: Troubleshooting
  • Untested backups: schedule periodic restore drills; an unverified backup is a guess.
  • WAL gap: ensure continuous archiving; a missing segment breaks PITR.
  • Slow restore: parallelize with pg_restore -j on multi-core hosts.

Key Results

  • Verified restores for both logical dumps and WAL-based PITR.
  • Met the defined RTO on test restores from cold backup.
  • Kept potential data loss within the RPO via continuous WAL archiving.
  • Validated restored data with row-count and checksum checks.

Related Labs