← All guides

Backing up large Postgres databases (100GB+) without pain

Directory-format parallel dumps, compression tradeoffs, replica-based dumping, and when logical backups stop scaling.

Parallel dump needs directory format

pg_dump "$URL" --format=directory --jobs=6 --compress=3 --file=dump_dir/

--jobs parallelizes per-table, so one giant table still bottlenecks — but typical schemas see near-linear speedups. Lower --compress (1–3): on big datasets, zlib level 6 CPU often costs more than the storage saves. Postgres 16+ supports --compress=zstd, which dominates zlib on both axes.

Dump from a replica

A long pg_dump on the primary holds a snapshot open — vacuum can't clean, bloat grows. Point backups at a streaming replica (set hot_standby_feedback off and accept query cancellation, or use a dedicated backup replica). Managed platforms: use read replicas or their pooler's read endpoints where offered.

When logical stops scaling

Somewhere past ~1 TB, dump-and-restore windows (index rebuilds!) stop fitting RTOs, and physical backups (pgBackRest, WAL-G) with PITR become the backbone. Keep periodic logical archives anyway for portability and per-table recovery — and restore-test whichever kind you rely on. An untested 2 TB backup is just a bigger hope.

A backup you've never restored is a hope, not a backup.

Firedrill restore-tests every backup it takes — on real infrastructure, with the report to prove it.