11 — Backups and Recovery: Logical, Physical, and PITR

"Schedule a pg_dump and hope" was my backup plan, and it capped my recovery power at last night. The framing that organized the whole field: there are two kinds of backup — logical (a SQL or text reconstruction of one database) and physical (a copy of the data files for the whole cluster) — and the recovery power scales with whether you archived the WAL alongside it [1][2]. Once I separated the two kinds and saw that WAL archiving is what unlocks point-in-time recovery, the tooling landscape stopped being a long list and became a small matrix of choices.

Two kinds of backup

• Logical backup — exports a database as SQL statements (or a custom/tar format) that recreate the schema and data [1]. pg_dump does one database; pg_dumpall does every database plus cluster-wide roles and tablespaces [2]. Portable across versions and architectures, selective (specific tables or schemas), but slow to restore because every row is re-inserted.
• Physical backup — copies the actual data files [3]. pg_basebackup produces a consistent file-level copy of the whole cluster. Fast to restore (it's a file copy), cluster-level, and the foundation for replication and point-in-time recovery.

The recovery-power ladder

The question I had to nail down is what can I restore to. The answer is a ladder:

1. Restore to the time of the last backup — a pg_dump alone, or a pg_basebackup without WAL. If the backup ran at 2am and the failure is at 5pm, everything between 2am and 5pm is gone.
2. Restore to any point in time (PITR) — a pg_basebackup plus continuously archived WAL segments [4]. Recovery replays the base backup and then replays WAL forward to any chosen moment, stopping at a specific timestamp or transaction ID. This is the level production demands.

PITR is unlocked by setting archive_mode = on and archive_command to ship every WAL segment to durable storage (an S3 bucket, a backup server). Tools like WAL-G and pgBackRest automate this: they manage base backups and WAL archiving together, with compression, deduplication, and encryption [5][6]. They also handle restore, including PITR.

The tooling matrix

The roadmap lists many tools; they sort cleanly by purpose:

• pg_dump / pg_dumpall — logical backups for dev copies, migrations, and small datasets [1][2]. Not the right primary backup for a production database because of the restore-time cost and the lack of PITR.
• pg_basebackup — physical base backup, the starting point for streaming replication and PITR [3].
• pgBackRest, WAL-G, Barman, pg_probackup — full backup-and-recovery managers that handle base backups, WAL archiving, retention, and PITR together [4][5][6]. These are what I'd run in production. WAL-G and pgBackRest integrate with cloud object storage; Barman is a dedicated backup server.
• pg_verifybackup — validates that a pg_basebackup-produced backup is complete and uncorrupted, via checksums [7].

Validation: a backup is only a backup if it restores

The roadmap is emphatic that taking a backup is half the job; validating it is the other half [7]. A backup that has never been test-restored is a hope, not a backup. The procedures that matter:

• Restore tests — periodically restore the backup into a throwaway cluster and confirm the data is there. Automate this; it's the only proof the backup works.
• Checksum verification — pg_verifybackup confirms file integrity for physical backups [7].
• Log monitoring — watch the backup tool's logs for warnings, partial failures, or missed WAL segments.

A backup that silently failed months ago is the worst possible discovery moment, which is during an incident.

Major-version upgrades: pg_upgrade

Adjacent to backup is the major-version upgrade problem. pg_upgrade does an in-place upgrade of a cluster to a new major version without a full dump/restore, drastically reducing downtime [8]. The high-level flow: install both versions, run pg_upgrade pointing at old and new data directories, then run analyze_new_cluster.sh to refresh planner statistics. For minimal-downtime upgrades, logical replication (covered in the next notes) can keep the new version in sync while cutting over. Either way, the backup taken before the upgrade is the rollback plan.

How I use this

The matrix gives me a default. For anything production, I run a physical-base-plus-WAL-archiving tool — pgBackRest on self-hosted, or whatever the managed service provides — because PITR is non-negotiable once real data is involved. I keep pg_dump for dev copies, schema migrations between environments, and quick table extracts. And I schedule a periodic restore test into a throwaway cluster, because the only credible backup is one that's been proven to restore. A backup without a tested restore is theater.

References

[1] PostgreSQL Global Development Group, "pg_dump," 2024. [Online]. Available: https://www.postgresql.org/docs/current/app-pgdump.html

[2] PostgreSQL Global Development Group, "pg_dumpall," 2024. [Online]. Available: https://www.postgresql.org/docs/current/app-pg-dumpall.html

[3] PostgreSQL Global Development Group, "pg_basebackup," 2024. [Online]. Available: https://www.postgresql.org/docs/current/app-pgbasebackup.html

[4] Barman / EnterpriseDB, "pgBarMan," 2024. [Online]. Available: https://www.pgbarman.org/

[5] WAL-G, "Continuous PostgreSQL backups using WAL-G," Supabase Blog, 2023. [Online]. Available: https://supabase.com/blog/continuous-postgresql-backup-walg

[6] pgBackRest, "pgBackRest documentation," 2024. [Online]. Available: https://pgbackrest.org

[7] PostgreSQL Global Development Group, "pg_verifybackup," 2024. [Online]. Available: https://www.postgresql.org/docs/current/app-pgverifybackup.html

[8] PostgreSQL Global Development Group, "pg_upgrade," 2024. [Online]. Available: https://www.postgresql.org/docs/current/pgupgrade.html