การกำหนดเส้นทางการเชื่อมต่อด้วย PgBouncer และ HAProxy

After a failover the primary moves to a new host. Applications need a stable endpoint so they do not have to be reconfigured each time. Connection routing solves this.

PgBouncer is a lightweight connection pooler. It multiplexes many client connections onto a small set of server connections, reducing backend load.

PgBouncer offers three pool modes:

• session — connection held for the whole client session
• transaction — returned after each transaction (most common)
• statement — returned after each statement

A minimal pgbouncer.ini points at the database and sets the pool mode.

HAProxy can health-check backends and forward traffic only to the node that is currently the primary, giving clients one fixed write endpoint.

HAProxy uses an HTTP health check against a tool like Patroni's REST API. Only the primary returns 200 on the leader endpoint.

Define a second HAProxy frontend that balances across replicas (the /replica health check), so read-only queries scale across standbys.

Applications connect to port 5432 for writes (primary) and 5433 for reads (replicas). Many drivers and ORMs support separate read/write data sources.

Replicas can lag behind the primary. Route only queries that tolerate slightly stale data to replicas; read-your-own-write paths should hit the primary.

A common stack: app -> PgBouncer -> HAProxy -> Patroni-managed PostgreSQL cluster. PgBouncer pools, HAProxy routes by role, Patroni manages failover.

On failover, Patroni promotes a new primary, HAProxy health checks flip the leader within seconds, and pooled connections reconnect to the new endpoint with minimal disruption.

You learned to route connections for high availability: PgBouncer pools connections, HAProxy health-checks to find the primary and balance reads across replicas, and the whole stack flips automatically on failover.