Why Backups Are Non-Negotiable
A backup strategy protects against hardware failure, accidental data deletion, application bugs that corrupt data, and ransomware, and PostgreSQL supports both logical backups (pg_dump/pg_dumpall, which export SQL statements or a custom-format archive) and physical backups (pg_basebackup or filesystem snapshots, which copy the actual data directory). The right choice depends on recovery time objective (RTO) and recovery point objective (RPO): logical backups are portable and human-readable but slower to restore on large databases, while physical backups restore faster but are tied to matching PostgreSQL major versions.
Cricket analogy: A team keeps both a detailed scorebook (logical, human-readable) and full match video footage (physical, exact reproduction) because each serves a different recovery purpose after a disputed decision.
Logical Backups with pg_dump
pg_dump exports a single database as a script or as a custom-format (-Fc) archive that pg_restore can replay selectively, table by table, or in parallel with -j for faster restores on multi-core machines; pg_dumpall additionally captures roles and tablespaces that pg_dump alone omits. Because pg_dump takes a consistent snapshot using PostgreSQL's MVCC without blocking writers, it is safe to run against a live production database, though very large databases can still take hours and the restore can take even longer.
Cricket analogy: A commentator summarizing an entire day's play into a highlights reel that can be replayed in any order mirrors pg_dump's custom format, which lets pg_restore selectively replay specific tables.
# Logical backup: custom-format, parallel-friendly archive of one database
pg_dump -Fc -d appdb -f appdb_2026-07-10.dump
# Restore that archive into a fresh database, using 4 parallel jobs
createdb appdb_restored
pg_restore -j 4 -d appdb_restored appdb_2026-07-10.dump
# Dump roles and tablespaces separately (pg_dump alone omits these)
pg_dumpall --globals-only -f globals.sqlpg_dump takes a transactionally consistent snapshot without locking out writers, thanks to MVCC — it is safe to run during business hours, unlike a naive file copy of a running data directory.
Physical Backups and Point-in-Time Recovery
pg_basebackup copies the entire data directory of a running instance and, combined with continuous WAL (write-ahead log) archiving, enables point-in-time recovery (PITR): restoring the base backup and then replaying WAL up to any specific timestamp before a mistake occurred, such as an accidental DROP TABLE. This requires setting wal_level to at least 'replica' and configuring archive_mode and archive_command so that WAL segments are continuously shipped to durable storage separate from the primary server's disk.
Cricket analogy: Restoring a match to the exact ball before a controversial dismissal by replaying the ball-by-ball commentary log up to that point mirrors point-in-time recovery replaying WAL up to a specific timestamp.
If you enable archive_mode but the archive_command fails silently (e.g., due to a full disk on the archive target), WAL segments accumulate on the primary and can eventually fill its disk, causing an outage — always monitor archiving status and alert on failures.
- Logical backups (pg_dump/pg_dumpall) are portable SQL/archive exports; physical backups (pg_basebackup) copy the raw data directory.
- pg_dump's custom format (-Fc) supports selective, parallel restores via pg_restore -j.
- pg_dumpall --globals-only captures roles and tablespaces that pg_dump alone does not include.
- pg_basebackup plus continuous WAL archiving enables point-in-time recovery (PITR) to any timestamp.
- PITR requires wal_level >= replica and a working archive_command shipping WAL segments off the primary.
- Physical backups restore faster but require matching PostgreSQL major versions and architecture.
- Always monitor WAL archiving — a silently failing archive_command can fill the primary's disk.
Practice what you learned
1. What is the main advantage of pg_dump's custom format (-Fc) over the plain SQL format?
2. What does pg_dumpall capture that pg_dump alone does not?
3. What two components are required to perform point-in-time recovery (PITR)?
4. Why is pg_dump considered safe to run against a live production database?
5. What minimum wal_level setting is required for archiving WAL to support PITR?
Was this page helpful?
You May Also Like
PostgreSQL Performance Tuning
Learn how to diagnose slow queries and tune PostgreSQL configuration, indexing, and query plans for production workloads.
PostgreSQL Quick Reference
A condensed reference of essential PostgreSQL commands, psql shortcuts, and system catalog queries for day-to-day work.
PostgreSQL Interview Questions
Prepare for PostgreSQL technical interviews with commonly asked questions on internals, transactions, indexing, and troubleshooting.