--- title: "11 — Backups and Recovery: Logical, Physical, and PITR" uid: backups-recovery tags: ["pg_dump", "pitr", "roadmap:postgresql-dba", "backups", "pg_basebackup", "postgresql", "recovery", "wal-g", "pgbackrest"] excerpt: "Two backup kinds — logical (SQL reconstruction of one database) and physical (the data files) — and recovery power scales from 'restore to last night' to 'restore to any second' depending on WAL archiving." date: 2026-08-13T03:27:51+0000 source: https://www.aveshina.my.id/en/blog/backups-recovery --- "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 ```figure ``` - **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](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](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](https://www.postgresql.org/docs/current/app-pgbasebackup.html) [4] Barman / EnterpriseDB, "pgBarMan," 2024. [Online]. Available: [https://www.pgbarman.org/](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](https://supabase.com/blog/continuous-postgresql-backup-walg) [6] pgBackRest, "pgBackRest documentation," 2024. [Online]. Available: [https://pgbackrest.org](https://pgbackrest.org) [7] PostgreSQL Global Development Group, "pg_verifybackup," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/app-pgverifybackup.html](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](https://www.postgresql.org/docs/current/pgupgrade.html) ```quiz Q: What is the key difference between pg_dump and pg_basebackup? - pg_dump is faster - pg_dump produces a logical SQL reconstruction of one database; pg_basebackup produces a physical copy of the whole cluster's data files correct: 1 explain: pg_dump exports data as statements that re-create it (portable, slow to restore, single-DB). pg_basebackup copies data files (fast, cluster-level, PITR-capable). Q: What does a backup need, beyond a base copy, to support point-in-time recovery? - A larger shared_buffers - Continuously archived WAL segments, replayed forward during recovery correct: 1 explain: PITR replays a base backup forward using archived WAL. Without the WAL, recovery can only reach the moment the base backup was taken. Q: Which tool family (pgBackRest, WAL-G, Barman) is the right choice for a production cluster? - They are obsolete; pg_dump is enough - A backup-and-recovery manager that handles base backups, WAL archiving, retention, and PITR together correct: 1 explain: These managers coordinate base backups and continuous WAL archiving with retention and restore tooling — the production-grade layer that pg_dump alone cannot provide. Q: Why must backups be validated by periodic restore tests? - Because backups are always corrupt by default - Because silent partial failures can otherwise go undetected until an incident, when discovery is catastrophic correct: 1 explain: A backup that has never been restored is an assumption. Regular restore tests are the only proof the backup is complete and restorable. Q: What does pg_upgrade do? - Upgrades PostgreSQL minor versions automatically in the background - Performs an in-place major-version upgrade of a cluster without a full dump/restore, reducing downtime correct: 1 explain: pg_upgrade converts the data directory between major versions in place, avoiding the slow dump/restore. For near-zero downtime, logical replication can keep a new-version clone in sync during cutover. ```