HAProxy for PostgreSQL

HAProxy does not know which node in a PostgreSQL cluster is the primary, and it has no way of finding out by looking at the traffic. The wire protocol is opaque to it. What it can do is ask something that does know, and Patroni exposes exactly that: an HTTP endpoint per node that returns 200 or 503 depending on the node’s current role.

That is the whole design. HAProxy proxies raw TCP, decides where to send it based on an HTTP health check aimed at a different port, and re-decides whenever Patroni changes its answer. Everything below follows from those two sentences being true at the same time.

Why mode tcp, and what mode http breaks

mode tcp establishes a full-duplex connection between client and server with no layer 7 examination. mode http analyses the client request in depth before connecting to any server, and any request which is not RFC-compliant will be rejected.

The PostgreSQL frontend/backend protocol is not HTTP. The first thing a client sends is a startup packet: a length prefix followed by a protocol version and a list of key/value parameters. HAProxy in mode http will try to parse that as a request line, fail, and close the connection. The symptom is every client failing immediately at connect time, before authentication. It surfaces in the driver as an unexpected EOF or “server closed the connection unexpectedly”, not as anything that points at the proxy.

tcp is HAProxy’s default mode, so this only bites when a defaults section inherited from an existing HTTP configuration sets mode http and the database proxy silently picks it up. Set mode tcp explicitly in the database frontends and backends instead of relying on the default.

The cost of mode tcp is real and worth naming: no layer 7 routing, no content switching, no per-request logging. HAProxy sees bytes. It cannot distinguish a SELECT from a DELETE, so it cannot do read/write splitting on statement content. If you want that, it is Pgpool-II territory, not HAProxy’s.

The two-port pattern

The standard layout is two frontends over the same three backend servers:

Every node appears in both backends. The health check does the selecting. On port 5000, exactly one node answers 200 to /primary, so exactly one node is UP and receives all traffic; the other two are DOWN. On port 5001, the two replicas answer 200 to /replica and the primary answers 503, so reads spread across the replicas. When Patroni promotes a different node, the answers change and HAProxy follows within inter × fall seconds. Nothing in HAProxy’s configuration changes.

The part that confuses people: the health check speaks HTTP even though the proxied traffic is TCP. option httpchk is documented as working with plain TCP backends: it sends a complete HTTP request once the TCP connection is established and treats 2xx and 3xx as valid. The check is aimed at a different port from the traffic, via port 8008 on the server line, so HAProxy connects to Patroni’s REST API on 8008 to decide health and to PostgreSQL on 5432 to carry data. Two ports, one server line.

Because the check and the traffic go to different ports, they can disagree. Patroni’s REST API can be answering happily on 8008 while PostgreSQL refuses connections on 5432. The node is then marked UP and every client connection fails. This is the main failure mode of the pattern, and the reason to prefer /primary and /replica (which report Patroni’s view of the node’s role and running state) over a bare TCP check on 5432 (which reports only that a socket accepted).

Patroni’s health endpoints

Get these wrong and you route writes to a replica. The endpoints below are the ones relevant to load balancing; each returns a JSON body plus the status code, and HEAD or OPTIONS works if you do not want the body.

Three traps live in that table.

/health is the one people reach for because of its name, and it is almost always wrong for routing. It returns 200 whenever PostgreSQL is up, which is true on every node in a healthy cluster. Point port 5000 at it and HAProxy will happily load balance writes across all three nodes, two of which are read-only. Use /primary.

/read-only includes the primary. Sometimes that is what you want: a read pool that degrades to the primary when every replica is down instead of failing. Sometimes it is exactly what you do not want, because it puts analytical load on the writer at the worst moment. Choose deliberately.

/leader and /primary differ in standby clusters. /leader returns 200 for the standby_leader too, so in a standby cluster it identifies a node that cannot accept writes. Use /primary unless you specifically mean “whoever holds the lock”.

Adding ?lag=16MB to /replica is worth doing. It uses cluster.last_leader_operation from the DCS for the leader’s WAL position instead of querying the primary, so it is cheap, and it keeps a replica that has fallen badly behind out of the read pool instead of serving stale results. The trade-off is that a lagging replica silently reduces your read capacity, so alert on backend server count rather than assuming the pool is full.

A complete configuration

Three nodes, 10.0.2.11 - .13. PostgreSQL on 5432, Patroni’s REST API on 8008 (the port used throughout Patroni’s own documentation). HAProxy on 10.0.2.20.

global
    log         /dev/log local0
    chroot      /var/lib/haproxy
    pidfile     /run/haproxy.pid
    user        haproxy
    group       haproxy
    daemon
    maxconn     4000
    nbthread    4
    stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
    stats timeout 30s

defaults
    mode                tcp
    log                 global
    option              tcplog
    option              dontlognull
    retries             2
    timeout connect     5s
    timeout client      30m
    timeout server      30m
    timeout check       5s
    timeout queue       10s
    default-server      inter 3s fastinter 1s downinter 5s rise 2 fall 3 \
                        on-marked-down shutdown-sessions

listen stats
    mode http
    bind 10.0.2.20:7000
    stats enable
    stats uri /
    stats refresh 5s
    stats show-node

frontend pg_write
    bind 10.0.2.20:5000
    default_backend pg_primary

frontend pg_read
    bind 10.0.2.20:5001
    default_backend pg_replicas

backend pg_primary
    option httpchk GET /primary HTTP/1.0
    http-check expect status 200
    server pg1 10.0.2.11:5432 check port 8008 maxconn 400
    server pg2 10.0.2.12:5432 check port 8008 maxconn 400
    server pg3 10.0.2.13:5432 check port 8008 maxconn 400

backend pg_replicas
    balance roundrobin
    option httpchk GET /replica?lag=16MB HTTP/1.0
    http-check expect status 200
    server pg1 10.0.2.11:5432 check port 8008 maxconn 400
    server pg2 10.0.2.12:5432 check port 8008 maxconn 400
    server pg3 10.0.2.13:5432 check port 8008 maxconn 400

A few of those lines carry more weight than they look.

on-marked-down shutdown-sessions immediately terminates all connections to a server when it goes down, logged with a D termination code. Without it, a client holding an idle connection to a demoted primary keeps that connection until a timeout fires. With the long timeout client this page recommends, that could be half an hour of a client writing to a node that is now a replica. The documentation gives exactly this case as the motivating example: a health check that detects a stuck database where existing connections cannot be reused.

fastinter 1s shortens the interval while a server is transitioning between states. With inter 3s and fall 3, steady-state detection would take nine seconds; with fastinter, the second and third failing checks arrive a second apart, so detection is closer to five. rise 2 and fall 3 are the defaults, restated here so they are visible when someone tunes the interval.

retries 2 retries a failed connection attempt. Note that option redispatch, which would let a retry go to a different server, is deliberately not set: in pg_primary only one server is ever UP, so there is nowhere else to go, and in pg_replicas a redispatch is harmless but adds nothing that the next connection would not achieve anyway.

Timeouts

HAProxy does not ship defaults for the connection timeouts. An unspecified timeout client, timeout server or timeout connect results in an infinite timeout, and HAProxy emits a warning at startup because expired sessions then accumulate. timeout check is different: if unset, HAProxy uses inter as the complete check timeout (connect plus read), and inter itself defaults to 2000 ms.

The problem is not the absent defaults. It is the values everyone copies. The example configuration in HAProxy’s own manual uses timeout client 50000ms and timeout server 50000ms, and those numbers propagate into database proxies unchanged. Fifty seconds of inactivity is nothing for a web request and fatal for a database connection: an application pool that holds a connection open between requests, a session waiting on an advisory lock, a COPY staged behind a slow producer, or an overnight batch job that computes for two minutes then issues its next statement. All of them are idle from HAProxy’s point of view, and all of them get their socket cut with no error the client can interpret. The application sees a connection that was working a moment ago and is now gone, usually mid-transaction.

Keeping timeout client and timeout server equal is not stylistic advice. If they differ, one side of a connection is torn down while the other still believes it is open, which produces asymmetric half-open states that are unpleasant to diagnose. The manual calls out TCP mode specifically.

Thirty minutes is a starting point, not a law. Set it above the longest legitimate idle period you can justify, and no higher: an infinite timeout means a client that vanishes without a FIN leaves a connection and a Postgres backend pinned indefinitely. If your application genuinely idles for hours, the fix is TCP keepalives on the client and the server, not an unbounded proxy timeout.

Diagnostically, this class of failure shows up in option tcplog output as sessions ending with a c (client-side timeout) or s (server-side timeout) termination flag and a session duration that matches your configured timeout almost exactly. A cluster of disconnections all at 50000 ms is not a coincidence.

Draining a node with the runtime API

The stats socket line in global exposes HAProxy’s runtime API. The level parameter controls what may be issued: user reads non-sensitive stats only, operator is the default and permits non-sensitive changes, admin permits everything. enable server is restricted to admin, so the configuration above sets it, with mode 660 and group ownership doing the access control.

Use socat to talk to it:

# What HAProxy currently believes
echo "show stat" | socat stdio /run/haproxy/admin.sock | cut -d, -f1,2,18,19
echo "show servers state pg_replicas" | socat stdio /run/haproxy/admin.sock

# Take pg3 out of the read pool, keeping health checks running
echo "set server pg_replicas/pg3 state drain" | socat stdio /run/haproxy/admin.sock

# Let existing queries finish, then cut what is left
echo "shutdown sessions server pg_replicas/pg3" | socat stdio /run/haproxy/admin.sock

# Full maintenance mode: no traffic and no health checks
echo "disable server pg_replicas/pg3" | socat stdio /run/haproxy/admin.sock

# Back in service
echo "enable server pg_replicas/pg3" | socat stdio /run/haproxy/admin.sock

The distinction between drain and maint is the one to internalise:

drain removes the server from load balancing but keeps checking it, which is what you want for a rolling maintenance: the node stops receiving new work, you watch the check stay green, you do the work, you set it back to ready. maint (equivalently disable server) stops checks entirely, so HAProxy has no idea what state the node is in until you re-enable it. A server DOWN for maintenance shows as MAINT on the stats page.

shutdown sessions server terminates all streams attached to a server immediately, logged with a K flag. drain on its own does not touch established connections. With a 30-minute timeout client, an idle client could sit on the draining node for the full half hour. Drain, wait for the session count to fall, then shut down the remainder.

Changes made through the runtime API do not survive a restart. show servers state dumps the current state in the format server-state-file consumes, which is how you make them persist across a reload.

Where HAProxy sits relative to PgBouncer

Both orderings work. They fail differently.

Client → HAProxy → PgBouncer → Postgres, with PgBouncer colocated on each database node and HAProxy’s server lines pointing at PgBouncer’s port 6432 rather than 5432. HAProxy still health checks Patroni on 8008. This is the arrangement to prefer. Routing happens before pooling, so a failover changes which PgBouncer instance receives traffic and each pooler only ever talks to the local PostgreSQL over loopback. A demoted node’s pooler simply stops receiving connections. The cost is that pool sizes are per node: three nodes at default_pool_size 25 can open 75 backends in total, and you must size max_connections against the sum, not against one pooler’s configuration.

Client → PgBouncer → HAProxy → Postgres, with one central pooler. Pooling happens before routing, which means PgBouncer’s server connections are the ones traversing HAProxy. Those connections are long-lived by design. That is the entire point of a pool. When Patroni promotes a new primary, PgBouncer is holding open server connections that HAProxy established to the old one, and PgBouncer has no notion of the node’s role changing underneath it. Until those connections are recycled by server_lifetime or killed, transactions are dispatched to a node that is now a replica and fail with read-only errors. Recovering cleanly needs PAUSE/RESUME on the pooler as part of the failover procedure. A central pooler is also a single-threaded process in front of your entire cluster.

The trade-off is centralisation against failover cleanliness. One central PgBouncer gives you one place to configure pool modes, one set of SHOW POOLS output, and a genuinely shared pool. Poolers per node give you correct failover behaviour and horizontal scaling, at the cost of managing three configurations and doing the pool-size arithmetic yourself. Unless you have a specific reason for the central pooler, put HAProxy first.

Either way, PostgreSQL sees the proxy’s address, not the client’s. pg_hba.conf entries and any IP-based auditing must be written against the HAProxy or PgBouncer host, and the real client address is only recoverable from HAProxy’s own logs. Pool modes, auth_query and sizing are covered on the PgBouncer page.

HAProxy and keepalived

HAProxy health checks and load balances. It is also, on its own, a single point of failure: every client connects to one address on one host, and if that host dies the cluster behind it is fully healthy and completely unreachable. Patroni will not help - it manages PostgreSQL, not the proxy.

The conventional answer is two HAProxy instances with keepalived holding a VRRP virtual IP between them. Clients connect to the VIP; keepalived moves it if the active HAProxy host fails; each keepalived node runs a vrrp_script that checks the local HAProxy process and lowers its priority or enters FAULT state when it is not running. The two tools solve genuinely different problems:

Get the keepalived weight arithmetic right, or you will build a pair where HAProxy is dead on the node holding the VIP and keepalived is behaving exactly as configured. That is covered in detail on the keepalived page. Note also that VRRP does not work on cloud virtual networks; there, the equivalent is a managed load balancer in front of the HAProxy instances, or in front of the database nodes directly with the health probe pointed at Patroni’s REST API.

Diagnostics

HAProxy exposes a runtime stats interface. Enable it in the configuration:

listen stats
    bind *:8404
    stats enable
    stats uri /stats
    stats refresh 5s

The stats page shows per-backend and per-server status, current sessions, queue depth, and health check results. For scripted checks, use the Unix socket:

echo "show stat" | socat stdio /run/haproxy/admin.sock | cut -d',' -f1,2,18,19

Backend servers showing DOWN. Check the health check configuration. For PostgreSQL with Patroni, the httpchk must hit the correct endpoint:

# From the HAProxy host, manually test the health endpoint:
curl -s http://<postgres-host>:8008/primary
# Should return 200 for the primary, 503 otherwise

If the endpoint returns 503 but the node is actually primary, Patroni’s REST API may be unreachable (firewall, wrong bind address, or Patroni is down while PostgreSQL is still running).

Queue depth growing. Visible in stats as qcur (current queued requests). This means all maxconn slots on the backend are occupied. Either the backend is slow (check PostgreSQL), or maxconn on the server line is too low. Raising it beyond PgBouncer’s max_client_conn or PostgreSQL’s max_connections just moves the queue to the backend.

Connection resets during failover. Expected behaviour: HAProxy closes connections to a server that fails its health check. To minimise disruption, use on-marked-down shutdown-sessions to immediately notify clients rather than waiting for timeout, and set retries 3 with option redispatch so clients are retried against a healthy server.

Timeouts causing 504 errors. The three timeouts to check:

timeout connect 5s    # time to establish TCP to backend
timeout client  30s   # inactivity from client side
timeout server  30s   # inactivity from server side

For long-running PostgreSQL queries, timeout server must exceed the longest expected query time. A VACUUM or reporting query that runs for 10 minutes against a 30-second timeout produces a 504.

Where HAProxy is the wrong tool

• Statement-level read/write splitting. In mode tcp HAProxy cannot see SQL. Splitting by port requires the application to hold two connection pools and know which to use. If you want the proxy to make that decision per query, that is Pgpool-II, and it costs you a SQL parser in the data path.
• Connection pooling. HAProxy multiplexes sockets; it does not reduce the number of PostgreSQL backend processes. One client connection through HAProxy is one Postgres backend. Use PgBouncer.
• Failover. HAProxy reacts to a promotion; it cannot cause one, and it has no consensus store, so it cannot safely decide anything during a partition. Patroni does that.
• Guaranteeing read-your-writes across the two ports. Port 5001 routes to replicas with asynchronous replication lag. A write on 5000 followed immediately by a read on 5001 may not see it. ?lag= bounds the staleness; it does not eliminate it.
• Sharding. There is no request-level routing to make sharding decisions with in TCP mode.
• Being highly available by itself. See above: pair it with keepalived or a managed load balancer.