PostgreSQL backups: logical dumps versus point-in-time recovery

What it is

The PostgreSQL manual describes three approaches: SQL dumps (pg_dump, pg_dumpall), file-system-level backups of the data directory (only with the server stopped or through a consistent snapshot), and continuous archiving, which combines a base backup with archived write-ahead log segments and allows recovery to any point in time.

Why it matters

Without a backup, a lost volume is a lost database; this wiki's operations guide states exactly that. The choice determines recovery point (how much data can be lost) and recovery time (how long a restore takes), and both must match what the operator has decided.

How to apply

• Small databases with an acceptable loss window of hours: scheduled pg_dump in custom format to another host or storage, retained by policy, encrypted if it contains personal data.
• Larger or write-heavy databases: continuous archiving with regular base backups; monitor archive lag.
• Rehearse restores into a scratch instance on a schedule and time them; a backup that has never been restored is an assumption.
• Keep backups off the same disk and, ideally, the same provider as the primary.

Pitfalls

Dumping while the schema is being migrated. Backing up the data directory of a running server without snapshots produces inconsistent copies. Retention policies that keep every dump forever, or none. Storing the encryption key next to the backups.

What it is

The PostgreSQL manual describes three approaches: SQL dumps (pg_dump, pg_dumpall), file-system-level backups of the data directory (only with the server stopped or through a consistent snapshot), and continuous archiving, which combines a base backup with archived write-ahead log segments and allows recovery to any point in time.

Why it matters

Without a backup, a lost volume is a lost database; this wiki's operations guide states exactly that. The choice determines recovery point (how much data can be lost) and recovery time (how long a restore takes), and both must match what the operator has decided.

How to apply

• Small databases with an acceptable loss window of hours: scheduled pg_dump in custom format to another host or storage, retained by policy, encrypted if it contains personal data.
• Larger or write-heavy databases: continuous archiving with regular base backups; monitor archive lag.
• Rehearse restores into a scratch instance on a schedule and time them; a backup that has never been restored is an assumption.
• Keep backups off the same disk and, ideally, the same provider as the primary.

Pitfalls

Dumping while the schema is being migrated. Backing up the data directory of a running server without snapshots produces inconsistent copies. Retention policies that keep every dump forever, or none. Storing the encryption key next to the backups.

Choosing between logical and physical backups

Logical dumps restore slowly (hours for tens of gigabytes) and cannot restore to a point in time. When the recovery time objective is short or the database is large, use physical base backups with continuous WAL archiving (directly or through a tool built on them), which restore faster and allow point-in-time recovery. Keep a logical dump as well for schema portability and partial restores.