Backup and Recovery for PostgreSQL DBAs
Backup and recovery is making copies of a PostgreSQL database and restoring them to a chosen moment. Logical backups with pg_dump export SQL or custom-format data, while physical backups copy the data files with pg_basebackup plus archived WAL, enabling point-in-time recovery. It is the data safety stage of the PostgreSQL DBA path.
Stage: Reliability · Part of the PostgreSQL DBA roadmap · Last reviewed 19 November 2026
What to learn in Backup and Recovery
- Take a custom-format pg_dump and restore selected tables with pg_restore
- Take a base backup with pg_basebackup and configure WAL archiving
- Restore to an exact timestamp using recovery_target_time
- Choose between logical and physical backups for a stated recovery objective
- Test a restore on a schedule and measure the recovery time
Backup and Recovery interview questions
Why does a pg_dump backup not support point-in-time recovery?
pg_dump is a logical snapshot of one moment, without the WAL stream between snapshots, so you can only restore to when the dump was taken. Point-in-time recovery needs a physical base backup plus continuously archived WAL replayed up to the chosen target.
What are RPO and RTO, and how do they drive backup design?
RPO is how much data loss is acceptable, and RTO is how long recovery may take. A small RPO needs continuous WAL archiving rather than nightly dumps, and a small RTO needs fast restore methods or standby servers. Verify both by actually restoring and timing it.
Where Backup and Recovery fits in the PostgreSQL DBA roadmap
Backup and Recovery belongs to the Reliability stage (intermediate level), which takes about 4 hours. Never lose data.
Other units in this stage
Back to the full PostgreSQL DBA roadmap