Skip to content

Phase 06: High Availability, Replication & Disaster Recovery

Chapter 25: Physical Streaming Replication Internals

25.1 Architecture: The WAL Pipeline

Physical streaming replication creates an exact, byte-for-byte physical clone of the primary cluster.

+------------------------------------+             +------------------------------------+
|          PRIMARY CLUSTER           |             |          STANDBY REPLICA           |
+------------------------------------+             +------------------------------------+
| Backend processes generate WAL     |             | Startup Process                    |
|                |                   |             | (Continuously replaying WAL)       |
|                v                   |             |                ^                   |
|           wal_buffers              |             |                |                   |
|                |                   |             |                |                   |
|                v                   |             |                v                   |
|       Local Disk ($PGDATA/pg_wal)  |             |      Standby Disk ($PGDATA/pg_wal) |
|                |                   |             |                ^                   |
|                v                   |             |                |                   |
|            walsender (process) ----+---(TCP)----+----> walreceiver (process)          |
+------------------------------------+             +------------------------------------+

25.2 Synchronous Commit Tiers (synchronous_commit)

When configuring synchronous replication (synchronous_standby_names = 'replica1'), you control the exact durability/latency tradeoff:

  1. off: Commit returns before writing to local WAL disk. (Fastest, risk of ~200ms data loss on crash).
  2. local: Commit blocks until written to local primary disk. Replicas receive WAL asynchronously.
  3. remote_write: Commit blocks until the replica acknowledges it has received the WAL bytes and written them to its OS kernel buffer (not yet fsynced to disk on replica).
  4. on: Commit blocks until the replica has flushed the WAL bytes to physical disk (fsync). Survives complete primary server destruction with zero data loss.
  5. remote_apply: Commit blocks until the standby's Startup Process has actually replayed the WAL and made changes visible to read-only queries on the replica. (Eliminates read-after-write replication lag inconsistency).

25.3 Physical Replication Slots (Preventing WAL Eviction)

  • Without a replication slot, if a standby falls behind due to network partition, the primary will eventually delete old WAL segments (max_wal_size limit). The standby will fatally disconnect with: FATAL: requested WAL segment has already been removed.
  • Replication Slots: Tell the primary: "Do not delete any WAL segments until this specific standby confirms it has consumed them."
  • The Choke Point (The 2 AM Disk Full Alert): If a standby crashes and stays down, and a replication slot is active, the primary will never delete WAL. It will keep accumulating 16MB WAL files until 100% of the primary's disk space is consumed, crashing the entire database!
  • Fix: Always configure max_slot_wal_keep_size (e.g., 50GB). If the replica falls behind by more than 50GB, the primary drops the slot and protects its own disk.

Chapter 26: Logical Replication & CDC (Change Data Capture)

26.1 Physical vs Logical Replication

Feature Physical Streaming Replication Logical Replication (Pub/Sub)
Granularity Entire cluster (all databases) Selective (individual tables or schemas)
Cross-Version Strict (Primary and Standby must be exact same major version) Supported (can replicate from PG 12 to PG 16)
Writeability Standby is strictly Read-Only Subscriber is fully Read/Write
DDL Support Yes (all DDL replicated automatically) No (schema changes must be run manually on both)
Sequence Sync Yes No (sequences do not advance automatically)

26.2 How Logical Decoding Works

  1. Primary writes standard physical WAL records.
  2. A Logical Replication Slot reads WAL records and passes them through an Output Plugin (e.g., pgoutput or test_decoding).
  3. The plugin decodes low-level heap tuple byte deltas into high-level logical change events (INSERT, UPDATE, DELETE) with typed column values.
  4. Foundation for Debezium, Kafka streaming pipelines, and microservice events.

26.3 Conflict Resolution Gotchas

If the subscriber database receives an INSERT for id = 100, but a row with id = 100 was already inserted locally on the subscriber: * Logical replication halts immediately. * The worker process crashes in a loop with a Unique Constraint Violation. * Re-starting replication requires manual intervention (deleting the offending row or setting ALTER SUBSCRIPTION ... SKIP (lsn = ...) in PG 15+).

Chapter 27: High Availability & Cluster Failover (Patroni)

27.1 Why Built-in Postgres Cannot Auto-Failover

Postgres has no built-in leader election consensus mechanism. If a primary fails, an external authority must decide: 1. Is the primary truly dead, or is it just a transient network hiccup? 2. Which standby is the most up-to-date (highest LSN)? 3. How to prevent Split-Brain (where both old and new primary accept writes)?

27.2 The Patroni Architecture (Industry Standard)

                     +----------------------------------------+
                     | Distributed Consensus Store (DCS)     |
                     |          (etcd / Consul)               |
                     |  Leader Key: /service/batman/leader    |
                     +----------------------------------------+
                                  ^              ^
                       Heartbeat  |              |  Heartbeat
                       (Lease TTL)|              |  (Watch)
                                  v              v
                   +-------------------+    +-------------------+
                   |     Patroni 1     |    |     Patroni 2     |
                   |  (Node: Primary)  |    |  (Node: Replica)  |
                   +---------+---------+    +---------+---------+
                             |                        |
                             v                        v
                   +-------------------+    +-------------------+
                   |    PostgreSQL     |===>|    PostgreSQL     |
                   |    (Read/Write)   |WAL |    (Read-Only)    |
                   +-------------------+    +-------------------+
  1. Leader Lock Lease: Patroni maintains a leader key in etcd with a short Time-To-Live (e.g., 10 seconds).
  2. Heartbeat: Every 2 seconds, the primary Patroni node renews its lease.
  3. Automatic Failover: If the primary crashes, its lease expires. The remaining standby Patroni nodes hold an election via etcd. The replica with the highest LSN wins, promotes its local Postgres instance to primary, and updates the leader key.
  4. Fencing (STONITH): The old primary must be aggressively fenced (cut off from network or restarted) to prevent split-brain writes.

Chapter 28: Backup & Disaster Recovery (PITR)

28.1 Why pg_dump is NOT a Disaster Recovery Solution

  • pg_dump is a logical backup. It runs a SELECT * across every table in a single transaction.
  • On a 5TB database:
  • Takes 10 hours to export.
  • Takes 30 hours to restore (must recreate all indexes from scratch!).
  • Point of recovery is fixed at the moment the dump started. Everything after is lost.
  • Use Case: Exporting schemas, migrating to another database engine, or archiving small tables.

28.2 Physical Backups & Point-In-Time Recovery (PITR)

True enterprise disaster recovery uses Continuous Archiving + Base Backups:

[Base Backup (Sunday 00:00)] =====> WAL Stream (Sun -> Wed 14:32:00) =====> [Ransomware / DROP TABLE]
                                                                                       ^
                                                                        Stop replay HERE: 14:31:59
  1. Base Backup (pg_basebackup / pgBackRest): Takes a raw physical snapshot of the $PGDATA files while the database is live.
  2. Continuous WAL Archiving: Every completed 16MB WAL file is shipped immediately to Amazon S3 or Google Cloud Storage via archive_command or pgBackRest.
  3. The Recovery (Time Machine):
  4. Restore the Sunday Base Backup.
  5. Provide the WAL archive.
  6. Configure recovery_target_time = '2026-10-14 14:31:59 UTC'.
  7. Postgres starts up, replays millions of WAL transactions up to 1 second before the junior engineer ran DROP TABLE users;, and promotes to an active database. Zero data loss.