I85PREP
⌘ K
EDB PostgreSQL Professional study materials
EDB PostgreSQL Professional study resources

EDB PostgreSQL Professional: High Availability, Replication and Backup/Recovery

EDB PostgreSQL Professional · Work through your exam, one topic at a time.

All guide sections

High Availability, Replication and Backup/Recovery

This domain tests the durability chain: WAL archiving, point-in-time recovery, streaming replication, replication slots, failover, and choosing the right backup tool.

Backup types

  • pg_dump/pg_dumpall are logical, consistent-snapshot exports; they cannot replay WAL.
  • pg_basebackup -X stream -R is physical and writes standby settings via -R.
  • archive_mode = on plus archive_command (or pg_receivewal, or EDB's BART) builds the WAL archive that PITR depends on.

PITR mechanics

Restore the base backup, provide restore_command, set recovery_target_time/_lsn/_name (restore points come from pg_create_restore_point), create recovery.signal, and set recovery_target_action - promote is the usual choice. Recovery target timestamps are interpreted in the server timezone unless you include an offset.

Replication

Primary needs wal_level = replica, max_wal_senders, and standby needs primary_conninfo plus hot_standby = on. Synchronous replication pairs synchronous_commit with synchronous_standby_names; ANY 1 (s1, s2) avoids committing stalls when one standby dies.

Worked example

A bad DELETE lands at 14:32. Reasoning: logical dumps cannot stop mid-stream, so use physical PITR. Restore the most recent base backup, point restore_command at the WAL archive, set recovery_target_time = '2026-01-14 14:31:00+00' and recovery_target_action = 'promote', start, verify row counts, then repoint applications. Keep the old primary's data directory for forensics.

Traps that cost marks

Believing pg_dump enables PITR; never rehearsing a restore; promoting a standby whose LSN lags the primary; leaving a replication slot active on a dead standby until the primary disk fills; forgetting wal_log_hints or data checksums, which pg_rewind needs.