Mental model
Replication streams database changes from a Primary node to Replica nodes to enable read scaling, disaster recovery, and high availability.
Primary DB Node Writes WAL
walreceiver fetches WAL stream
Apply WAL on Physical Replica
DCS Health Check (Patroni/etcd)
Automated Primary Failover
Theory
- Physical Streaming Replication: Streams byte-for-byte WAL records. The replica is an exact read-only copy of the primary.
- Logical Replication: Decodes WAL records into logical data change events (
INSERT/UPDATE/DELETE). Allows selective table replication across different PostgreSQL versions or target schemas.
Automatic high availability (HA) frameworks like Patroni use a Distributed Consensus Store (DCS like etcd or Consul) to elect a new primary node when health checks fail.
sql(8 lines)
1-- Check replication lag on Primary node
2SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
3 pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
4FROM pg_stat_replication;
5
6-- Create a logical publication for specific tables
7CREATE PUBLICATION app_events_pub FOR TABLE events, audit_logs;
Alternatives and trade-offs
- Physical Replication: Simple configuration, zero logical translation overhead; replica must be read-only and identical version.
- Logical Replication: Supports cross-version upgrades and partial table subsets; higher CPU decoding overhead on primary.
Failure modes and misconceptions
- Split-Brain Scenario: Network partitioning causing two nodes to act as Primary simultaneously. Prevent split-brain by requiring quorum consensus via DCS (etcd/Consul).
- Replication Slot Disk Exhaustion: A disconnected standby holding an active physical replication slot causes the Primary to retain all WAL files indefinitely until disk fills up.
Reflect before revealing the guide
Decision scenario
Deploy Patroni with etcd consensus for automated primary failover in mission-critical PostgreSQL database clusters requiring 99.99% availability.
Learning outcomes
- Compare Physical Streaming Replication with Logical Replication mechanics.
- Monitor replication lag using LSN byte diffs (
pg_wal_lsn_diff). - Prevent split-brain scenarios using distributed consensus managers.
Trade-offs
Replication provides high availability and read scaling, but asynchronous replication introduces small replication lag windows where read replicas may reflect slightly stale data.