PostgreSQL Patroni HA Cluster with Read/Write Load Balancing

A production-grade Docker Compose stack running a 5-node PostgreSQL 16 cluster with automatic failover, read/write splitting, connection pooling, and full observability. Goal of this repo is to provide a point of reference for bare metal setups

Architecture

graph TB
    subgraph Clients
        WEB[Web UI :5000]
    end

    subgraph Connection Pooling
        PBW[PgBouncer Write :6432]
        PBR[PgBouncer Read :6433]
    end

    subgraph Load Balancing
        HAW[HAProxy Write :5432]
        HAR[HAProxy Read :5433]
        HAS[HAProxy Stats :7000]
    end

    subgraph PostgreSQL Cluster
        PG1[pg-node-1]
        PG2[pg-node-2]
        PG3[pg-node-3]
        PG4[pg-node-4]
        PG5[pg-node-5]
    end

    subgraph Consensus
        E1[etcd-1]
        E2[etcd-2]
        E3[etcd-3]
    end

    WEB --> PBW
    WEB --> PBR
    PBW --> HAW
    PBR --> HAR
    HAW -->|primary only| PG1 & PG2 & PG3 & PG4 & PG5
    HAR -->|replicas only| PG1 & PG2 & PG3 & PG4 & PG5
    PG1 & PG2 & PG3 & PG4 & PG5 -->|leader election| E1 & E2 & E3

Loading

Monitoring

graph LR
    subgraph Targets
        PGE1[pg-exporter-1 :9187]
        PGE2[pg-exporter-2 :9187]
        PGE3[pg-exporter-3 :9187]
        PGE4[pg-exporter-4 :9187]
        PGE5[pg-exporter-5 :9187]
        PBBE[pgbouncer-exporter :9127]
        ETCD[etcd-1/2/3 :2379/metrics]
        PAT[patroni-1..5 :8008/metrics]
    end

    PROM[Prometheus :9090] --> PGE1 & PGE2 & PGE3 & PGE4 & PGE5
    PROM --> PBBE
    PROM --> ETCD
    PROM --> PAT
    GRAF[Grafana :3000] --> PROM

Loading

Request Flow

sequenceDiagram
    participant C as Client
    participant PB as PgBouncer
    participant HA as HAProxy
    participant PA as Patroni REST API
    participant PG as PostgreSQL

    Note over HA,PA: Health check loop (every 3s)
    HA->>PA: GET /primary (or /replica)
    PA-->>HA: 200 {"role":"master"} or 503

    C->>PB: SQL query
    PB->>HA: Forward (pooled connection)
    HA->>PG: Route to healthy backend
    PG-->>HA: Result
    HA-->>PB: Result
    PB-->>C: Result

Loading

Failover

sequenceDiagram
    participant PG1 as pg-node-1 (Leader)
    participant E as etcd cluster
    participant PG2 as pg-node-2 (Replica)
    participant HA as HAProxy

    PG1-xE: Leader dies / TTL expires
    E->>PG2: Lock available
    PG2->>E: Acquire leader lock
    PG2->>PG2: Promote to primary
    HA->>PG2: Health check: GET /primary -> 200
    HA->>PG1: Health check: timeout/503
    Note over HA: Write traffic rerouted to pg-node-2
    PG1->>PG2: Rejoins as replica (pg_rewind)

Loading

Containers

21 containers total across a single patroni-net bridge network.

etcd (3 nodes)

Distributed key-value store for Patroni leader election.

Container	Image	Internal Ports
etcd-1, etcd-2, etcd-3	gcr.io/etcd-development/etcd:v3.5.17	2379 (client), 2380 (peer)

• 3-node cluster for quorum (tolerates 1 node failure)
• Data persisted to named volumes (etcd-{1,2,3}-data)
• Exposes /metrics natively on port 2379 (scraped by Prometheus)
• Patroni stores leader lock, cluster config, and member registry here

PostgreSQL + Patroni (5 nodes)

PostgreSQL 16 on Alpine, managed by Patroni 3.3.2 for HA.

Container	Image	Internal Ports
pg-node-1 through pg-node-5	postgres:16-alpine + Patroni	5432 (PG), 8008 (Patroni REST)

• One node is elected leader (read-write), the other four are streaming replicas (read-only)
• Asynchronous replication with max_replication_slots: 10
• pg_rewind enabled for fast replica rejoin after failover
• password_encryption: md5 for PgBouncer compatibility
• max_connections: 200 with superuser_reserved_connections: 5
• Data persisted to named volumes (pg-node-{1..5}-data)
• Config mounted from ./patroni/patroni.yml (no rebuild needed for config changes)
• post_bootstrap.sh runs on first init: creates testdb, pgbouncer user, webuser, and the pgbouncer.get_auth() function

HAProxy

TCP proxy that routes write and read traffic to the correct PostgreSQL nodes.

Container	Image	Exposed Ports
haproxy	haproxy:2.9-alpine	5432 (write), 5433 (read), 7000 (stats)

• Write backend (:5432): routes to the Patroni leader only
• Read backend (:5433): round-robin across replicas only
• Health checks via TCP connection to Patroni REST API (port 8008)
  • Write: matches "role": "master" in response body
  • Read: matches "role": "replica" in response body
• on-marked-down shutdown-sessions kills stale connections on failover
• maxconn 150 per server to stay within PostgreSQL's max_connections
• Stats dashboard at http://localhost:7000 (credentials: admin/admin)

PgBouncer (2 instances)

Connection pooler sitting between clients and HAProxy.

Container	Image	Internal Ports
pgbouncer-write	pgbouncer/pgbouncer:latest	6432
pgbouncer-read	pgbouncer/pgbouncer:latest	6433

• Transaction pooling mode (pool_mode = transaction)
• max_client_conn: 400, default_pool_size: 40
• Authentication via auth_query using pgbouncer.get_auth() SECURITY DEFINER function
• The pgbouncer user is the auth_user -- its password is the only entry in userlist.txt
• All other users (e.g. webuser) are authenticated dynamically against pg_shadow via the auth function
• server_reset_query = DISCARD ALL ensures clean connection reuse

Prometheus Exporters

Container	Image	Internal Ports	Scrapes
pg-exporter-1..5	postgres-exporter:v0.16.0	9187	One per PG node
pgbouncer-exporter	pgbouncer-exporter:v0.10.2	9127	pgbouncer-write stats

Prometheus

Container	Image	Exposed Ports
prometheus	prom/prometheus:v3.2.1	9090

Scrape targets:

• postgres -- 5 pg-exporters (connections, transactions, cache, replication lag, DB size)
• pgbouncer -- pool utilization, query rate, wait times
• etcd -- leader status, raft proposals, disk sync latency, peer RTT
• patroni -- node roles, WAL position, timeline

Grafana

Container	Image	Exposed Ports
grafana	grafana/grafana:11.5.2	3000

Credentials: admin / admin

Pre-provisioned dashboards:

• PostgreSQL Cluster Overview -- connections, TPS, cache hit ratio, row operations, replication lag, DB size
• etcd Cluster Overview -- leader, gRPC rates, WAL fsync latency, raft proposals, peer RTT
• Patroni HA Status -- node roles, WAL positions, timeline tracking
• PgBouncer Connection Pooling -- pool utilization, client waiting, query rate, avg duration, network throughput

Web UI

Container	Image	Exposed Ports
web-ui	python:3.12-alpine + Flask	5000

• Connects as webuser through PgBouncer (write on :6432, read on :6433)
• Insert button writes through the primary via pgbouncer-write
• Read button queries a replica via pgbouncer-read
• Shows which backend node served each request
• Source mounted from ./web-ui with Flask debug mode for live reload

File Structure

.
├── docker-compose.yml
├── haproxy/
│   └── haproxy.cfg
├── patroni/
│   ├── Dockerfile
│   ├── patroni.yml
│   ├── entrypoint.sh
│   ├── post_bootstrap.sh
│   └── setup_pgbouncer_auth.sql
├── pgbouncer/
│   └── userlist.txt
├── web-ui/
│   ├── Dockerfile
│   ├── app.py
│   ├── requirements.txt
│   └── templates/
│       └── index.html
└── monitoring/
    ├── prometheus/
    │   └── prometheus.yml
    └── grafana/
        ├── provisioning/
        │   ├── datasources/
        │   │   └── prometheus.yml
        │   └── dashboards/
        │       └── provider.yml
        └── dashboards/
            ├── postgresql.json
            ├── etcd.json
            ├── patroni.json
            └── pgbouncer.json

Usage

Start

docker compose up -d --build

Access

Service	URL	Credentials
Web UI	http://localhost:5000	--
HAProxy Stats	http://localhost:7000	admin / admin
Grafana	http://localhost:3000	admin / admin
Prometheus	http://localhost:9090	--
PostgreSQL (write)	localhost:5432	webuser / webpassword
PostgreSQL (read)	localhost:5433	webuser / webpassword

Check cluster state

docker compose exec pg-node-1 patronictl -c /etc/patroni/patroni.yml list

Test failover

# Kill the current leader
docker compose stop pg-node-2

# Watch a replica promote (check within 30-40 seconds)
docker compose exec pg-node-1 patronictl -c /etc/patroni/patroni.yml list

# Bring it back (rejoins as replica automatically)
docker compose start pg-node-2

Tear down

docker compose down -v

Database Users

User	Password	Purpose
postgres	secretpassword	Superuser (Patroni internal)
replicator	replpassword	Streaming replication
pgbouncer	pgbouncerpass	PgBouncer auth_user (runs auth_query)
webuser	webpassword	Application user (web-ui)