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.

Recipe

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=full

When to reach for this: Day one of any production PostgreSQL 18.4 cluster, before the first schema migration.

Working Example

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.
Globals (pg_dumpall -g) are separate from per-database dumps.
Physical archiving (archive_mode) enables PITR alongside logical dumps.

Deep Dive

Backup Types

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

Recovery Scenarios

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

3-2-1 Rule

3 copies of data
2 different media types
1 off-site copy (different region/account)

Gotchas

Replica as only backup - Replication replays corruption and deletes. Fix: Versioned logical or physical backups independent of replica.
Untested backups - Archives exist but restore fails. Fix: Monthly restore drill to scratch instance.
Plain SQL dump for large DBs - Single-threaded, huge files. Fix: Directory or custom format with parallelism.
Forgetting globals - Roles missing after restore. Fix: pg_dumpall -g on schedule.
Backing up only data, not WAL - Point-in-time recovery impossible. Fix: Enable archive_mode or pgBackRest incremental.

Alternatives

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

FAQs

pg_dump on primary load impact?

Uses CPU and I/O; run off-peak or from standby with pg_dump -h standby (snapshot of standby data).

How often logical vs physical?

Logical daily or weekly; physical continuous (WAL) with weekly full base backup typical.

Does backup include extensions?

Logical dumps include extension objects in schema. Physical includes everything in data directory.

Patroni and backups?

Run pgBackRest from any node with repository access; coordinate with patronictl pause only if required by tool.

Encryption?

Encrypt dumps at rest (S3 SSE, gpg) and in transit (TLS). See Encryption at Rest.

pg_dump & pg_restore - formats and parallelism
Backup Verification - restore drills
PITR Basics - WAL timeline
pgBackRest - physical automation

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+.

Backup Basics

Recipe

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=full

When to reach for this: Day one of any production PostgreSQL 18.4 cluster, before the first schema migration.

Working Example

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.
Globals (pg_dumpall -g) are separate from per-database dumps.
Physical archiving (archive_mode) enables PITR alongside logical dumps.

Deep Dive

Backup Types

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

Recovery Scenarios

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

3-2-1 Rule

3 copies of data
2 different media types
1 off-site copy (different region/account)

Gotchas

Replica as only backup - Replication replays corruption and deletes. Fix: Versioned logical or physical backups independent of replica.
Untested backups - Archives exist but restore fails. Fix: Monthly restore drill to scratch instance.
Plain SQL dump for large DBs - Single-threaded, huge files. Fix: Directory or custom format with parallelism.
Forgetting globals - Roles missing after restore. Fix: pg_dumpall -g on schedule.
Backing up only data, not WAL - Point-in-time recovery impossible. Fix: Enable archive_mode or pgBackRest incremental.

Alternatives

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

FAQs

pg_dump on primary load impact?

Uses CPU and I/O; run off-peak or from standby with pg_dump -h standby (snapshot of standby data).

How often logical vs physical?

Logical daily or weekly; physical continuous (WAL) with weekly full base backup typical.

Does backup include extensions?

Logical dumps include extension objects in schema. Physical includes everything in data directory.

Patroni and backups?

Run pgBackRest from any node with repository access; coordinate with patronictl pause only if required by tool.

Encryption?

Encrypt dumps at rest (S3 SSE, gpg) and in transit (TLS). See Encryption at Rest.

pg_dump & pg_restore - formats and parallelism
Backup Verification - restore drills
PITR Basics - WAL timeline
pgBackRest - physical automation

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+.

Backup Basics

Buscar en la documentación

Backup Basics