Applies toPostgreSQL 13 and later; self-managed (pgBackRest, Barman, WAL-G, pg_basebackup) and managed services (RDS/Aurora, Azure Flexible Server, Cloud SQL)
Logical vs physical
Logical (pg_dump/pg_dumpall) | Physical (base backup + WAL) | |
|---|---|---|
| Restores | Single tables/schemas, to other versions | Whole cluster to any point in time |
| Speed on large DBs | Slow to dump and restore (indexes rebuilt) | Fast (file-level), parallel tools |
| RPO | Time of the dump | Seconds–minutes with continuous WAL archiving |
| Use | Migrations, small DBs, extra safety copies | Primary backup method for production |
Most production setups use physical backups as the main method and logical dumps as a supplement.
Self-managed tools
| Tool | Strengths |
|---|---|
| pgBackRest | Parallel backup/restore, full/diff/incremental, compression, encryption, S3/Azure/GCS repos, backup from standby, delta restore |
| Barman | Centralised backup server for many PostgreSQL servers, streaming WAL, retention policies |
| WAL-G | Lightweight, cloud-storage-native, delta backups |
| pg_basebackup + archive_command | Built-in; fine for small setups, more manual work |
# pgBackRest essentials (pgbackrest.conf)
[global]
repo1-type=azure
repo1-azure-account=stpgbackup
repo1-azure-container=pgbackrest
# plus credentials: repo1-azure-key (shared key or SAS) or repo1-azure-key-type=auto for managed identity, per your pgBackRest version
repo1-retention-full=4
repo1-cipher-type=aes-256-cbc
process-max=4
compress-type=zst
[main]
pg1-path=/var/lib/postgresql/16/main
# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
Managed services
- PITR built in with a retention period you set.
- Add: cross-region backup copies, longer-term snapshots/exports for compliance, and protection against account-level deletion (backup vaults with locks where available).
- Logical dumps still useful for selective restores.
Design points
| Point | Recommendation |
|---|---|
| Schedule | Weekly full, daily differential/incremental, continuous WAL |
| Retention | From RPO/RTO and compliance needs |
| Location | Off-server, ideally another region; immutable copy |
| Encryption | Repository encryption; keys stored separately |
| Monitoring | Alert on archive failures (pg_stat_archiver) and missed backups |
| Testing | Scheduled restores with timing |
SELECT archived_count, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;
Warning
Failed WAL archiving fills
pg_waland can stop the server (see the pg_wal Sev1 post). Alert onfailed_countincreases immediately.
Checklist
- Physical backups with WAL archiving for production
- Logical dumps where single-object restore or portability needed
- Tool chosen for self-managed servers
- Off-site, encrypted, immutable copies
- Archiver monitoring and alerts
- Timed restore tests