Backups, Replicas, and the Settings That Matter

A backup strategy that hasn't been restored from isn't a backup — it's a hope. Postgres gives you two real options and a handful of settings that decide whether they work when you need them.

pg_dump is a logical snapshot

pg_dump -Fc produces a portable, restorable export — perfect for dev databases, small prod, and "copy prod to staging." Its limit: it's a logical dump, so restore time scales with data size and it captures a point-in-time snapshot of whatever it saw when it ran.

PITR is for real recovery

Point-in-time recovery = base backup (pg_basebackup or a filesystem snapshot) + continuous WAL archiving (archive_command copying WAL segments to object storage). Restore is "recover to 2025-06-15 14:32:07" — you get back every committed transaction up to the failure. Tools: pgBackRest, WAL-G, Barman.

Streaming replication

A replica replays the primary's WAL in near-real-time. hot_standby = on makes it readable — reports, analytics, read-heavy queries can hit the replica. Synchronous mode (synchronous_commit = remote_apply) waits for the replica to confirm before returning to the client: the price is latency, the payoff is zero data loss on primary failure.

The knobs that matter most

• shared_buffers — page cache; 25% of RAM is the default starting point.
• work_mem — per-sort/hash memory; too low and joins spill to disk, too high and you exhaust RAM.
• max_connections — each is a process; >500 is a smell — use a pooler (PgBouncer).
• effective_cache_size — a hint to the planner about OS cache; set it to ~75% of RAM.
• autovacuum_* — tune per-table (ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.05)) when defaults are too lazy.

Field notes

• pg_stat_replication on the primary shows lag — a replica >5min behind is a PagerDuty page, not a metric to watch.
• Test restore quarterly: restore to a scratch instance, run a checksum or a row count, document the RTO.
• The recovery_target_time in a PITR restore is your real RPO — the WAL archive's lag is how much you lose on a bad primary.