There are two main backup approaches for Postgres: logical (pg_dump) and physical (pg_basebackup or filesystem snapshot). They are not interchangeable. They solve different problems and have very different trade-offs.
Most teams I work with have only one of them and assume it covers their needs. Usually it does not. Here is the framework I use.
What each one is
Logical backup (pg_dump) captures the database as a stream of SQL statements (CREATE TABLE, INSERT INTO, etc.) or a custom format. Restoring is replaying the SQL on a fresh database.
# Custom format (compressed, parallelized restore)
pg_dump --format=custom --jobs=4 --file=/backup/monpg.dump monpg
# Plain SQL
pg_dump --format=plain --file=/backup/monpg.sql monpg
Physical backup (pg_basebackup) captures the entire data directory as a filesystem-level copy. Restoring is unpacking the archive to a new data directory and starting Postgres.
pg_basebackup -D /backup/base -Ft -z -P -X stream -W
These produce fundamentally different artifacts and have fundamentally different cost profiles.
Speed
Logical: slow to take, very slow to restore. The dump has to read every row through SQL; the restore has to replay every INSERT and rebuild every index.
- A 100GB database might dump in 2 hours and restore in 8 hours.
Physical: fast to take, fast to restore. It is just a file copy.
- A 100GB database might base-backup in 30 minutes and restore in 30 minutes.
For anything over ~50GB, physical is significantly faster.
Granularity
Logical: per-database, per-schema, per-table. You can restore just one table:
pg_restore --table=orders /backup/monpg.dump > orders.sql
Physical: cluster-wide. You restore the entire instance or nothing.
If the failure mode is "restore one bad table to last week," only logical works directly. (Physical can do it through more elaborate procedures, but it is significantly more work.)
Version portability
Logical: works across Postgres versions. You can dump from 14, restore to 16. This is how you do major-version migrations.
Physical: locked to the exact major version. Postgres 14 base backup cannot be restored on Postgres 16.
If you ever need to restore on a different version, only logical works.
Point-in-time recovery (PITR)
Logical: not natively. A pg_dump is a snapshot at the time it ran. To get PITR, you need to combine it with WAL archiving and a more elaborate process.
Physical: yes, with WAL archiving. Restore to any moment between the base backup and the last archived WAL.
For most production workloads, PITR is essential. "Restore to the moment before the bad migration" is a routine operational need. This is the killer feature of physical backups.
Storage cost
Logical: much smaller. SQL is more compressible than raw page data. A 100GB database might compress to 10-20GB as a custom-format dump.
Physical: similar to the database size, with mild compression. A 100GB database might be 70GB compressed.
For archival of historical states (e.g., "the database every quarter for 5 years"), logical is much cheaper.
What I recommend most teams do
For a typical production workload, both:
- Physical backup nightly + WAL archiving continuously. This is the operational backup for routine recovery: PITR, replica seeding, fast restoration after accidents.
- Logical backup weekly, retained for years. This is the archival backup. For "reproduce the database from 2 years ago" cases, version migrations, exporting subsets.
The two are complementary. Physical is the workhorse; logical is the archive.
For smaller workloads (under 10GB), logical alone is often fine because the speed difference does not matter. For larger workloads, physical is essential and logical is the safety net.
On managed Postgres
Managed PG (RDS, Cloud SQL, Azure) does physical backups and WAL archiving for you. The configuration is:
- Automated backups: typically daily physical, configurable retention.
- PITR window: typically 7-35 days, depending on plan.
- Cross-region replicas: for disaster recovery.
What managed services usually do not give you: logical backups for archival. If you need long-term schema-and-data preservation, you have to run pg_dump yourself, periodically, against the managed instance.
This is the most-overlooked managed-PG gap I see. Teams assume "the cloud handles backups" without realizing the cloud's retention is bounded.
A drill checklist
For each backup type:
- Physical: quarterly, restore to a fresh instance. Verify Postgres starts. Run sample queries.
- Logical: quarterly, restore to a different Postgres version (if you have one). Verify schema and data.
- PITR: every six months. Pick a random recent timestamp. Restore. Verify data reflects that moment.
The drills are the only proof that the backups actually work. Untested backups are the most expensive false sense of security in operations.
What I have learned from incidents
The most painful backup-related incident I helped with was a team that had only logical backups. Their database was 800GB. The disk failed. They restored from pg_dump. Restoration took 14 hours, including index rebuilds. The application was down the entire time.
If they had had physical + WAL archiving, restoration would have been ~45 minutes. The lesson stuck.
For any production database over 100GB, the cost-benefit of physical backups is overwhelming. The cost-benefit of logical backups is also high — they are cheap and they save you in the cases physical cannot. There is no good argument for picking only one.