PostgreSQL Backup & Recovery: pg_dump, PITR & WAL Archiving

Q: Should I use pg_dump or physical backups?

Use pg_dump (logical) for small-to-medium databases, migrations, and moving data across major versions — it's portable and selective. Use physical backups (pg_basebackup plus WAL archiving) for large or critical databases and whenever you need point-in-time recovery or fast full-cluster restore. Many setups use both: physical + WAL for disaster recovery, plus periodic logical dumps for portability and long-term retention.

PostgreSQL Backup & Recovery

Backups are the difference between a bad day and a company-ending one, and PostgreSQL gives you two fundamentally different approaches: logical backups (pg_dump, which exports data as SQL or an archive) and physical backups (a byte-level copy of the data directory, combined with WAL). The physical approach, paired with continuous WAL archiving, unlocks point-in-time recovery (PITR) — the ability to restore the database to any moment, such as the second before someone ran DELETE without a WHERE.

Choosing and, crucially, testing a backup strategy is core operational work — not glamorous, but the thing you'll be most grateful for exactly once. This page covers the two backup types, how PITR works, and the practices that keep a backup from being a false sense of security. The single most important idea: a backup you've never restored is not a backup.

TL;DR

Two approaches: logical (pg_dump/pg_dumpall — portable SQL/archive of data) and physical (pg_basebackup — a byte copy of the data directory).
Logical is great for single databases, migrations, and cross-version moves; slower to restore for large data and not point-in-time.
Physical + WAL archiving enables PITR — restore to any moment in time, essential for large or critical databases.
PITR = a base backup + the stream of archived WAL replayed up to a chosen target time/transaction.
Managed services (RDS, Cloud SQL, Neon, Supabase) automate physical backups + PITR — know your retention window and RPO.
Test restores regularly. An untested backup is a hope, not a plan. Track your RPO (data loss tolerance) and RTO (recovery time).

Quick Example

Logical backup and restore of one database (portable, simple):

Physical base backup, the foundation for PITR:

Core Concepts

Logical vs Physical Backups

Logical backups are portable and selective — ideal for moving a database, upgrading across versions, or backing up a modest database. Physical backups are the foundation of serious disaster recovery for large systems and the only path to point-in-time recovery.

WAL and Point-in-Time Recovery

PostgreSQL records every change in the write-ahead log (WAL) before applying it. If you take a base backup and then continuously archive the WAL produced afterward, you can restore by loading the base backup and replaying the archived WAL up to any chosen point — a timestamp, a transaction ID, or a named restore point.

This is PITR, and it's the capability that lets you recover from human error and corruption, not just hardware failure. Its power is granularity — you're not stuck with last night's snapshot; you can stop recovery one second before the mistake.

RPO and RTO

Two numbers define your requirements and should drive the strategy:

RPO (Recovery Point Objective) — how much data loss you can tolerate. Nightly dumps → up to ~24h RPO. Continuous WAL archiving → seconds of RPO.
RTO (Recovery Time Objective) — how long recovery may take. Restoring a huge logical dump can be hours; a physical restore plus WAL replay is usually faster.

Pick backup methods and frequency to meet your actual RPO/RTO, not by habit.

Best Practices

Test Restores on a Schedule

The only proof a backup works is restoring it. Regularly restore to a scratch environment, verify the data, and time it (that's your real RTO). Backups silently fail — wrong permissions, incomplete WAL, corrupt archives — and you must not discover it during an actual incident. Untested backups are the most common cause of failed recoveries.

Use Physical + WAL for Anything Important

For large or critical databases, base backups plus continuous WAL archiving (enabling PITR) are the standard. Logical dumps alone leave you with coarse recovery points and slow restores. Consider a purpose-built tool (pgBackRest, Barman, WAL-G) that manages base backups, WAL archiving, retention, and parallelism robustly.

Store Backups Off-Host and Encrypted

A backup on the same server (or same disk) is worthless when that server dies. Ship backups and WAL to separate, durable storage (object storage in another location/region), encrypt them, and control access. Backups are sensitive data — protect them like the database.

Know Your Managed Service's Guarantees

Managed Postgres (RDS, Cloud SQL, Neon, Supabase) automates backups and usually PITR — but the retention window and granularity vary. Confirm how far back you can restore, the effective RPO, and whether you also keep independent logical dumps for cross-provider portability or longer retention.

Automate and Monitor

Automate backups and WAL archiving, and monitor that they're succeeding — alert on a failed backup, a stalled WAL archive, or a growing gap. A backup process that silently stopped weeks ago is the nightmare scenario. Verify freshness continuously.

Common Mistakes

Never Testing the Restore

Backups on the Same Host as the Database

If the backup lives on the same server or disk as the data, a hardware failure or ransomware event takes both. Store copies off-host, ideally in another region, encrypted.

Relying on Nightly Dumps for a Critical DB

A once-a-day logical dump means up to 24 hours of data loss and a slow restore. For critical systems, that RPO/RTO is usually unacceptable — use physical backups with continuous WAL archiving for PITR.

FAQ

Should I use pg_dump or physical backups?

Use pg_dump (logical) for small-to-medium databases, migrations, and moving data across major versions — it's portable and selective. Use physical backups (pg_basebackup plus WAL archiving) for large or critical databases and whenever you need point-in-time recovery or fast full-cluster restore. Many setups use both: physical + WAL for disaster recovery, plus periodic logical dumps for portability and long-term retention.

What is point-in-time recovery (PITR)?

PITR restores the database to any specific moment — a timestamp or transaction — rather than just the last snapshot. It works by taking a base backup and continuously archiving the WAL afterward; recovery loads the base backup and replays WAL up to your chosen stop point. That granularity is what lets you recover from human error, like stopping recovery one second before an accidental DELETE.

How often should I back up?

Frequently enough to meet your RPO (how much data loss you can tolerate). Nightly logical dumps imply up to ~24 hours of potential loss; continuous WAL archiving brings RPO down to seconds. Set base-backup frequency and WAL retention to your tolerance and restore-time needs, not by rote — a critical transactional database needs continuous WAL, while a low-churn one may be fine with daily dumps.

Do managed databases handle backups for me?

Largely yes — RDS, Cloud SQL, Neon, and Supabase automate physical backups and usually offer PITR. But you must know the specifics: the retention window (how far back you can restore), the effective RPO, and any limits. Consider keeping independent logical dumps too, for portability across providers and for retention beyond the managed window. Don't assume; verify the guarantees.

Why does everyone stress testing restores?

Because backups fail silently in ways you only discover when restoring — corrupt archives, missing WAL segments, wrong permissions, an automation job that quietly stopped. A backup you've never restored is an untested assumption, not a safety net. Regular test restores to a scratch environment prove the backups work and reveal your real recovery time (RTO) before an incident forces the issue.