PostgreSQL High Availability with Patroni

Overview

Production-grade PostgreSQL HA architecture using Patroni for automatic failover, etcd for distributed consensus, HAProxy for connection routing, and PgBouncer for connection pooling.

This is the standard PostgreSQL HA stack used by companies like Zalando (creators of Patroni), GitLab, and many others running PostgreSQL at scale.

Architecture

Application Layer

Application servers connect through HAProxy, never directly to PostgreSQL. This abstracts away which node is currently Primary.

Connection Layer

• HAProxy exposes two endpoints:
  • Port 5000: Read-Write traffic, routed to Primary only
  • Port 5001: Read-Only traffic, load-balanced across Standbys
• PgBouncer provides connection pooling, multiplexing 100 client connections into ~20 PostgreSQL connections. This prevents connection exhaustion (PostgreSQL default: max_connections=100).

PostgreSQL Cluster

• Primary: The single read-write node. Runs WAL sender processes for streaming replication.
• Standby 1 (Synchronous): synchronous_commit=on guarantees zero data loss (RPO=0). The Primary waits for this standby to confirm WAL flush before acknowledging commits.
• Standby 2 (Asynchronous): Lower write latency but may lose recent transactions during failover. Useful for read scaling and disaster recovery.

Each PostgreSQL node runs a Patroni agent that manages the node lifecycle and participates in leader election.

etcd Cluster

Three-node etcd cluster using Raft consensus. Stores the Patroni leader key with a 30-second TTL. The current Primary must renew this key every 10 seconds. If it fails, the key expires and a new election begins.

WAL Archive

Continuous WAL archiving to S3/NFS enables Point-in-Time Recovery (PITR). The archive_command ships completed WAL segments (16MB each) to durable storage.

Key Concepts

Write-Ahead Log (WAL)

Every change is first written to WAL before being applied to data files. WAL records are identified by Log Sequence Number (LSN), e.g., 0/16A8B30. Streaming replication works by shipping WAL records from Primary to Standbys.

Synchronous vs Asynchronous Replication

• Synchronous: Primary waits for standby to flush WAL before confirming commit. Zero data loss, but adds ~1-5ms latency per write.
• Asynchronous: Primary confirms immediately after local WAL flush. Faster writes, but standby may lag behind. Data loss possible during failover.

Patroni Failover Process

1. Primary stops updating leader key TTL in etcd
2. After 30s, key expires -- cluster has no leader
3. All standby Patroni agents detect the expired key
4. Each agent compares its LSN -- the one with the latest data wins
5. Winner runs pg_ctl promote, creating a new timeline
6. Winner acquires the leader key in etcd
7. Other standbys reconfigure to follow the new Primary
8. HAProxy health checks detect the change and reroute traffic

Timeline IDs

When a standby is promoted, PostgreSQL creates a new timeline (incrementing the timeline ID). This prevents split-brain scenarios where the old Primary might try to rejoin with conflicting data.

Scenarios

1. Write Query Flow -- Full INSERT path through HAProxy, PgBouncer, WAL, sync replication
2. Read Query Load Balancing -- HAProxy distributes reads across standbys
3. Automatic Failover -- Primary crash, TTL expiration, promotion, traffic rerouting
4. Streaming Replication Deep Dive -- WAL generation, shipping, replay, LSN tracking
5. Patroni Leader Election -- Distributed consensus with etcd compare-and-swap