Backup Basics
Logical backups (pg_dump) export SQL or archive objects. Physical backups (base backup + WAL) capture disk blocks for fast restore and PITR. Production needs both strategies for different recovery scenarios.
//
Search across all documentation pages
Logical backups (pg_dump) export SQL or archive objects. Physical backups (base backup + WAL) capture disk blocks for fast restore and PITR. Production needs both strategies for different recovery scenarios.
Nightly logical dump for object restore; continuous physical backup for PITR and full cluster recovery.
# Logical: schema + data for one database
pg_dump -h localhost -U backup -Fc -f /backup/appdb_$(date +%F).dump appdb
# Physical: pgBackRest full (example)
pgbackrest --stanza=prod backup --type=fullWhen to reach for this: Day one of any production PostgreSQL 18.4 cluster, before the first schema migration.
Define a 3-2-1 backup posture for a Patroni-managed cluster:
#!/usr/bin/env bash
# nightly-logical.sh
DUMP_DIR=/backup/logical
mkdir -p "$DUMP_DIR"
pg_dump -h pg-primary.internal -U backup_role -Fc \
-f "$DUMP_DIR/appdb_$(date +%Y%m%d).dump" appdb-- Verify backup role can read all needed tables
GRANT pg_read_all_data TO backup_role;
-- Globals (roles, tablespaces) separate from pg_dump
-- pg_dumpall -g -h pg-primary.internal -U postgres > /backup/globals.sql# Physical WAL archive check
psql -h pg-primary.internal -c "SELECT archived_count, failed_count FROM pg_stat_archiver;"What this demonstrates:
pg_dump -Fc custom format supports parallel pg_restore and selective object restore.pg_dumpall -g) are separate from per-database dumps.archive_mode) enables PITR alongside logical dumps.| Type | Tool | Best for | Limitations |
|---|---|---|---|
| Logical | pg_dump, pg_dumpall | Object restore, smaller DBs, cross-version | Slow restore at TB scale |
| Physical base | pg_basebackup, pgBackRest | Full cluster restore, replicas | Needs WAL chain for PITR |
| Snapshot | EBS/LVM snapshot | Fast image | Crash consistent; pair with WAL archive |
| Replica | Standby | Read offload, not backup alone | Does not stop logical corruption |
| Scenario | Preferred backup |
|---|---|
| Drop one table | pg_restore from logical dump |
| Corrupted database | Physical restore + PITR |
| Region loss | Off-site physical + logical copies |
| Clone to staging | Logical dump or replica snapshot |
pg_dumpall -g on schedule.archive_mode or pgBackRest incremental.| Alternative | Use When | Don't Use When |
|---|---|---|
| pgBackRest only | Large self-hosted, PITR focus | Need cheap object-level restore only |
| Cloud automated backup | RDS/Aurora | Custom on-prem Patroni |
| Filesystem snapshot only | Fast image with WAL archive | No archive_mode configured |
Uses CPU and I/O; run off-peak or from standby with pg_dump -h standby (snapshot of standby data).
Logical daily or weekly; physical continuous (WAL) with weekly full base backup typical.
Logical dumps include extension objects in schema. Physical includes everything in data directory.
Run pgBackRest from any node with repository access; coordinate with patronictl pause only if required by tool.
Encrypt dumps at rest (S3 SSE, gpg) and in transit (TLS). See Encryption at Rest.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.
Reviewed by Chris St. John·Last updated Jul 18, 2026