Connection Proxies

During failover, something must send client connections to the new primary. HAProxy (or a cloud load balancer) routes TCP; PgBouncer 1.x pools sessions. They solve different layers.

Recipe

HAProxy TCP frontends for write (primary) and read (replicas); PgBouncer in transaction pool mode for app workers.

# HAProxy backend checks Patroni REST (port 8008) for leader
# frontend pg_write -> backend patroni_primary (only leader passes check)
# frontend pg_read   -> backend patroni_replicas (standbys pass replica check)

# pgbouncer.ini snippet
[databases]
appdb = host=pg-write.internal port=5432 dbname=appdb
 
[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50
server_lifetime = 300

When to reach for this: More than a handful of app servers connecting to PostgreSQL; failover must not require app config changes.

Working Example

HAProxy Patroni check script and PgBouncer reload after failover:

#!/usr/bin/env bash
# patroni-primary-check.sh - exit 0 if this node is Patroni leader
LEADER=$(curl -sf http://127.0.0.1:8008/patroni | jq -r .role)
[[ "$LEADER" == "master" || "$LEADER" == "primary" ]]

# haproxy.cfg excerpt
backend patroni_primary
  mode tcp
  option tcp-check
  tcp-check connect port 8008
  tcp-check send GET\ /primary\ HTTP/1.0\r\n
  tcp-check expect string 200
  server pg1 10.0.1.11:5432 check port 8008
  server pg2 10.0.1.12:5432 check port 8008
  server pg3 10.0.1.13:5432 check port 8008

# After failover: bounce PgBouncer to drop stale server connections
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "RECONNECT;"
# or: systemctl reload pgbouncer

What this demonstrates:

• HAProxy uses Patroni REST /primary and /replica endpoints for health routing.
• Apps connect to pg-write.internal:6432 (PgBouncer) not individual node IPs.
• RECONNECT or reload clears pools pointing at old primary.

Deep Dive

Layer Responsibilities

Read/Write Split

• Writes: Single backend that passes Patroni primary check.
• Reads: Replicas via /replica check; tolerate slight lag for reporting.
• Caution: Read-your-writes requires primary or sync-aware routing, not any replica.

PgBouncer Modes

Gotchas

• PgBouncer only, no HAProxy - Pool still aims at one hostname; DNS must update on failover. Fix: Patroni + HAProxy or reliable DNS TTL + health checks.
• Prepared statements in transaction mode - Errors after pool reuse. Fix: pool_mode = session for affected apps or disable prepared statements.
• HAProxy checking PostgreSQL port only - Both primary and replica accept TCP; wrong routing. Fix: Patroni HTTP health checks on 8008.
• Long server_lifetime - Stale primary connections for minutes. Fix: Lower lifetime; RECONNECT in Patroni on_role_change callback.
• RDS Proxy with self-hosted Patroni - Different products; do not mix patterns. Fix: Pick managed or self-hosted stack per environment.

Alternatives

FAQs

Related

• Patroni & etcd/Consul - leader election
• HA Basics - client retry requirements
• HA Testing - validate routing in drills
• Connection Math - pool sizing

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.