PostgreSQL backup has three main approaches per the Postgres docs: pg_dump (logical, single database), pg_basebackup (physical, full cluster), and WAL archiving (point-in-time recovery). The team that uses pg_dump for small databases, pg_basebackup + WAL archiving for production, has recovery from both logical corruption (pg_dump restore) and point-in-time failures (WAL replay).
Table of contents
- The three approaches
- pg_dump
- Restore from pg_dump
- pg_basebackup
- WAL archiving for PITR
- Backup schedule
- Managed database backups
- Third-party tools
- FAQ
The three approaches
| Approach | Type | Granularity | Best for |
|---|---|---|---|
| pg_dump | Logical | Single DB | Small DBs, migrations |
| pg_basebackup | Physical | Full cluster | Production, large DBs |
| WAL archiving | Continuous | PITR | Disaster recovery |
The team that picks based on database size and recovery needs has the right strategy.
pg_dump
# Single database
pg_dump -U postgres mydb > mydb-$(date +%Y%m%d).sql
# All databases
pg_dumpall -U postgres > all-$(date +%Y%m%d).sql
# Compressed
pg_dump -U postgres -Fc mydb > mydb-$(date +%Y%m%d).dumpThe team that uses pg_dump -Fc (custom format) has compressed output and parallel restore with pg_restore.
Restore from pg_dump
# Plain SQL format
psql -U postgres -d newdb < mydb-backup.sql
# Custom format
pg_restore -U postgres -d newdb mydb-backup.dumpThe team that uses custom format has parallel restore (faster on large DBs).
pg_basebackup
Physical backup of the entire cluster:
pg_basebackup -U postgres -D /backup/base -Ft -z -P-Ft: tar format.-z: compress.-P: progress.
Restoration requires the entire data directory; this is a binary copy. The team that uses pg_basebackup has faster backups/restores for large databases.
WAL archiving for PITR
Continuous archiving of Write-Ahead Logs enables point-in-time recovery:
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'Then backup the base + WAL files. To restore to a specific time:
# Restore base backup, then recover to a point in time
recovery_target_time = '2026-07-05 10:30:00'The team that uses WAL archiving has recovery to any point in time within the WAL retention window.
Backup schedule
Typical production schedule:
- Daily: pg_basebackup or pg_dump.
- Hourly WAL archiving: continuous, low-overhead.
- Offsite: copy to S3/GCS/Azure Blob.
The team that has offsite backups has disaster recovery. The team that only has local backups loses data if the disk fails.
Managed database backups
- AWS RDS: automated daily backups + PITR via WAL.
- GCP Cloud SQL: automated backups + PITR.
- Azure Database for PostgreSQL: automated backups.
- Crunchy Bridge, Neon, Supabase: managed Postgres with backups.
The team that uses managed Postgres has backups handled by the provider (with some cost).
Third-party tools
- pgBackRest: full-featured backup tool. Parallel, compressed, encrypted.
- Barman: Backup and Recovery Manager. WAL archiving, PITR.
- pg_probackup: PostgreSQL’s official backup tool.
The team that needs advanced backup features (incremental, encryption, parallel) uses these.
FAQ
pg_dump or pg_basebackup for production?
pg_basebackup for production (faster, full cluster). pg_dump for single DBs, migrations, dev environments.
How do I do point-in-time recovery?
WAL archiving + base backup. Restore base, then replay WALs to a specific time.
Can I run pg_dump while database is in use?
Yes - pg_dump takes a consistent snapshot without locking. The team that runs scheduled backups during peak hours is fine.
What’s the difference between pg_dump and pg_dumpall?
pg_dump: single database. pg_dumpall: all databases + global objects (roles, tablespaces).
Should I backup to S3 or local disk?
Both - daily to local disk (fast restore), then sync to S3 (offsite). The team that has both has fast restore AND disaster recovery.
If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.
Useful related references: