PostgreSQL Backup: Logical vs Physical
February 13, 2026
|
PostgreSQL
Backup
DR
Compare pg_dump and pgBackRest.
Logical vs Physical Backup in PostgreSQL
PostgreSQL offers two fundamentally different backup approaches. Choosing the right one — or combining both — depends on your recovery objectives, database size, and operational requirements.
Comparison
| Feature | Logical (pg_dump) | Physical (pgBackRest) |
|---|---|---|
| What's backed up | SQL statements to recreate data | Raw data files + WAL |
| Speed (backup) | Slow for large DBs | Fast (parallel, incremental) |
| Speed (restore) | Very slow (re-executes SQL) | Fast (file copy + WAL replay) |
| PITR support | No | Yes (any point in time) |
| Cross-version | Yes (restore to different PG version) | Same major version only |
| Selective restore | Yes (single table/schema) | No (full cluster only) |
| Database size limit | Practical limit ~100 GB | Handles multi-TB databases |
When to Use Each
- pg_dump: Schema migrations, cross-version upgrades, single-table restores, small databases (< 50 GB)
- pgBackRest: Production disaster recovery, PITR requirements, large databases, compliance needs
- Both: Many organizations use pgBackRest for DR and pg_dump for schema-level backups and cross-environment data copies
RPO and RTO Comparison
| Strategy | RPO (Data Loss) | RTO (Downtime) |
|---|---|---|
| pg_dump nightly | Up to 24 hours | Hours (depends on DB size) |
| pgBackRest + WAL archiving | Seconds | Minutes (file restore + WAL replay) |
| Streaming replication + pgBackRest | Zero (synchronous) | Seconds (automatic failover) |
Eazy SaaS Tip: For any database in production, use pgBackRest with continuous WAL archiving. Keep pg_dump for ad-hoc schema exports and dev environment seeding. Never rely solely on pg_dump for production DR.