Patroni runs your PostgreSQL cluster and handles failover, promoting a replica the moment the primary dies and recording the change in its distributed store (etcd, Consul, or ZooKeeper). That part works on its own.<\/p>\n\n\n\n
Your applications still need one stable address to connect to, and they need writes to reach the primary while reads spread across replicas. HAProxy handles that routing, with a floating IP from Keepalived in front of it.<\/p>\n\n\n\n
Three components stands between your application and the database.<\/p>\n\n\n\n
Patroni<\/strong> manages replication and failover, and it runs an agent on every PostgreSQL node. Each agent exposes a small REST API (port 8008 by default) that reports that node’s role.<\/p>\n\n\n\n HAProxy<\/strong> accepts client connections and forwards them to the right node. It asks Patroni’s REST API which node is the primary and which are replicas, then sends each connection to a matching node.<\/p>\n\n\n\n Keepalived<\/strong> publishes a virtual IP that floats between your HAProxy hosts using VRRP. Your application connects to the VIP, so one HAProxy host going down doesn’t take the whole entry point with it.<\/p>\n\n\n\n Your application talks to the VIP. Keepalived points the VIP at a live HAProxy. HAProxy forwards the connection to whichever PostgreSQL node Patroni reports as healthy for that role.<\/p>\n\n\n\n HAProxy checks one port and routes to another.<\/p>\n\n\n\n Patroni’s REST API returns an HTTP status that depends on the node’s role:<\/p>\n\n\n\n In our case, HAProxy runs its health check against the API port (8008) and reads that status code, then forwards the SQL connection to the database port (5432). A node receives traffic only when its API answers A two-node setup with This example runs PostgreSQL on port 5432 and the Patroni API on 8008, so swap in whatever ports your deployment uses (the defaults are 5432 and 8008).<\/p>\n\n\n\n Line by line:<\/p>\n\n\n\n For a primary plus one or more replicas, this routes writes and reads to the right node and survives a failover.<\/p>\n\n\n\n Imagine a two-node cluster: one primary, one replica. The replica goes down. Maybe it crashed, maybe Patroni is mid-switchover and no standby exists for a few seconds.<\/p>\n\n\n\n Your read traffic hits port 5001. That listener marks a server up only when The primary is up the entire time, and it can serve those reads. Your config won’t send them there, because you told the read listener to look for replicas and nothing else. You’ve turned a degraded cluster that could still serve reads into a read outage. You feel this most on small clusters, and each failover passes through a window where the old primary becomes a replica and no standby is available yet. In the worst case, your replica is down, and one of your application is connecting to port 5001, resulting in errors.<\/p>\n\n\n\n Send reads to the primary when the read listener runs out of replicas, instead of dropping them.<\/p>\n\n\n This new line carries the whole fix:<\/p>\n\n\n That backend health-checks with Three names have to agree for this to work. The backend you define ( Patroni offers The cost is read load on the primary even when your replicas are healthy and idle. The fallback approach keeps reads off the primary until the replicas are gone, then leans on it as a safety net. You protect the primary’s write capacity during normal operation and still keep reads alive during a replica outage.<\/p>\n\n\n\n To keep lagging replicas out of the read pool, Patroni accepts a threshold on the replica check, for example One HAProxy host fronting the cluster moves the single point of failure up a layer. Run HAProxy on two hosts and let Keepalived float a virtual IP between them with VRRP. Your application connects to the VIP, and whichever HAProxy holds it answers.<\/p>\n\n\n\n A minimal The second HAProxy host runs the same file with Point your applications at Patroni keeps the cluster healthy and picks the leader. HAProxy turns Patroni’s REST API into routing, sending writes to the primary and reads to the replicas by health-checking the API port while forwarding to the database port. The naive read-only listener drops reads when the last replica goes down, even though the primary could serve them. Adding Writes reach the primary and reads spread across the replicas. Reads stay up as long as one node in the cluster is alive.<\/p>\n\n\n\n Let me know if you find any improvements to this configuration \ud83d\ude00 <\/p>\n","protected":false},"excerpt":{"rendered":" Patroni runs your PostgreSQL cluster and handles failover, promoting a replica the moment the primary dies and recording the change in its distributed store (etcd, Consul, or ZooKeeper). That part works on its own. Your applications still need one stable address to connect to, and they need writes to reach the primary while reads spread […]<\/p>\n","protected":false},"author":87,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,42],"tags":[1348,1491,4138,1024,1645,2602],"type_dbi":[2749],"class_list":["post-45342","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-operating-systems","tag-haproxy","tag-keepalived","tag-load","tag-load-balancer","tag-load-balancing","tag-postgresql-2","type-postgresql"],"acf":[],"yoast_head":"\nThe health-check method<\/h1>\n\n\n\n
\n
GET \/<\/code> returns 200<\/code> only on the leader (the primary). A non-leader node returns 503<\/code>.<\/li>\n\n\n\nGET \/primary<\/code> is the explicit name for the same leader check.<\/li>\n\n\n\nGET \/replica<\/code> returns 200<\/code> only on a running replica.<\/li>\n\n\n\nGET \/read-only<\/code> returns 200<\/code> on the primary or a replica, any node that can serve a read.<\/li>\n<\/ul>\n\n\n\n200<\/code> for the role that listener cares about. Point a listener’s check at \/<\/code> and it follows the primary. Point it at \/replica<\/code> and it follows the replicas. Patroni promotes a new leader, the status codes change, and HAProxy moves traffic to match within a couple of health-check cycles.<\/p>\n\n\n\nA first and simple working configuration<\/h1>\n\n\n\n
10.5.5.147<\/code> and 10.5.5.148<\/code> looks like this. One listener handles writes, the other handles reads.<\/p>\n\n\n\nlisten PG1\n bind *:5000\n option httpchk\n http-check expect status 200\n default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions\n server postgresql_10.5.5.147_5432 10.5.5.147:5432 maxconn 100 check port 8008\n server postgresql_10.5.5.148_5432 10.5.5.148:5432 maxconn 100 check port 8008\n\nlisten PG1_ro\n bind *:5001\n option httpchk GET \/replica\n http-check expect status 200\n default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions\n server postgresql_10.5.5.147_5432 10.5.5.147:5432 maxconn 100 check port 8008\n server postgresql_10.5.5.148_5432 10.5.5.148:5432 maxconn 100 check port 8008\n<\/pre><\/div>\n\n\n
\n
bind *:5000<\/code> and bind *:5001<\/code> are the two addresses your applications connect to. Send writes to 5000 and reads to 5001.<\/li>\n\n\n\noption httpchk<\/code> (with no path) on the first listener checks Patroni’s root endpoint. Only the leader answers 200<\/code>, so HAProxy sends port 5000 traffic to the current primary.<\/li>\n\n\n\noption httpchk GET \/replica<\/code> on the second listener checks the replica endpoint, so HAProxy sends port 5001 traffic to a replica.<\/li>\n\n\n\nhttp-check expect status 200<\/code> tells HAProxy that 200<\/code> means healthy and anything else means down.<\/li>\n\n\n\ninter 3s fall 3 rise 2<\/code> checks every 3 seconds, marks a server down after 3 failures, and brings it back after 2 successes.<\/li>\n\n\n\non-marked-down shutdown-sessions<\/code> kills existing connections to a server the instant HAProxy marks it down, so clients reconnect and get rerouted instead of hanging on a dead node.<\/li>\n\n\n\ncheck port 8008<\/code> is the trick in action: health checks hit the Patroni API on 8008 while HAProxy forwards traffic to PostgreSQL on 5432.<\/li>\n\n\n\nmaxconn 100<\/code> limit connections per server so you don’t exhaust PostgreSQL’s connection slots.<\/li>\n<\/ul>\n\n\n\nThe failure mode hiding in the read path<\/h1>\n\n\n\n
GET \/replica<\/code> returns 200<\/code>, and right now no node is a replica. HAProxy has zero usable servers in the pool, so it refuses the connection. Read queries start failing.<\/p>\n\n\n\nThe fix: fall back to the primary<\/h1>\n\n\n\n
\nlisten PG1\n bind *:5000\n option httpchk\n http-check expect status 200\n default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions\n server postgresql_10.5.5.147_5432 10.5.5.147:5432 maxconn 100 check port 8008\n server postgresql_10.5.5.148_5432 10.5.5.148:5432 maxconn 100 check port 8008\n\nlisten PG1_ro\n bind *:5001\n option httpchk GET \/replica\n http-check expect status 200\n use_backend PG1_ro_leader if { nbsrv(PG1_ro) eq 0 }\n default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions\n server postgresql_10.5.5.147_5432 10.5.5.147:5432 maxconn 100 check port 8008\n server postgresql_10.5.5.148_5432 10.5.5.148:5432 maxconn 100 check port 8008\n\nbackend PG1_ro_leader\n option httpchk GET \/primary\n http-check expect status 200\n default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions\n server postgresql_10.5.5.147_5432 10.5.5.147:5432 maxconn 100 check port 8008\n server postgresql_10.5.5.148_5432 10.5.5.148:5432 maxconn 100 check port 8008\n<\/pre><\/div>\n\n\n\nuse_backend PG1_ro_leader if { nbsrv(PG1_ro) eq 0 }\n<\/pre><\/div>\n\n\nnbsrv(PG1_ro)<\/code> counts the usable servers in the PG1_ro<\/code> pool, which here means the number of available replicas, since those servers pass the check only when GET \/replica<\/code> returns 200<\/code>. While at least one replica is up, the count stays above zero, the condition is false, and reads stay on the replicas. The moment the last replica drops, nbsrv(PG1_ro)<\/code> hits zero, the condition fires, and HAProxy diverts reads to the PG1_ro_leader<\/code> backend.<\/p>\n\n\n\nGET \/primary<\/code>, so the only server it counts as up is the current primary. HAProxy sends reads to the primary until a replica returns, then shifts them back to the replica pool once a replica passes its check again.<\/p>\n\n\n\nbackend PG1_ro_leader<\/code>), the backend you route to (use_backend PG1_ro_leader<\/code>), and the pool you count (nbsrv(PG1_ro)<\/code>) all reference the real section names. Drop in a stale name from an earlier version and HAProxy either refuses to start or counts the wrong pool.<\/p>\n\n\n\nThe \/read-only shortcut and what it costs<\/h2>\n\n\n\n
GET \/read-only<\/code>, which returns 200<\/code> on the primary and the replicas alike. Point the read listener there and both the primary and the replicas serve reads, no fallback backend needed.<\/p>\n\n\n\nGET \/replica?lag=10MB<\/code>, which fails any replica more than 10 MB behind. Pair that with the fallback and HAProxy drops the lagging replicas from rotation while reads still have somewhere to go.<\/p>\n\n\n\nKeepalived: removing HAProxy as a single point of failure<\/h1>\n\n\n\n
keepalived.conf<\/code> on the primary HAProxy host:<\/p>\n\n\n\nvrrp_script chk_haproxy {\n script "killall -0 haproxy" # succeeds while the haproxy process is alive\n interval 2\n weight 2\n}\n\nvrrp_instance VI_1 {\n interface eth0\n state MASTER\n virtual_router_id 51\n priority 101\n advert_int 1\n authentication {\n auth_type PASS\n auth_pass changeme\n }\n virtual_ipaddress {\n 10.0.0.1\n }\n track_script {\n chk_haproxy\n }\n}\n<\/pre><\/div>\n\n\nstate BACKUP<\/code> and a lower priority<\/code> (100). Both advertise over VRRP, and the higher priority holds the VIP. chk_haproxy<\/code> runs every two seconds. If HAProxy dies on the active host, its priority drops and the backup takes the VIP, so an HAProxy crash on one host no longer takes the entry point down with it.<\/p>\n\n\n\n10.0.0.1:5000<\/code> for writes and 10.5.5.100:5001<\/code> for reads. Your applications never see which physical HAProxy does the work.<\/p>\n\n\n\nSummary<\/h1>\n\n\n\n
use_backend ... if { nbsrv(...) eq 0 }<\/code> with a primary-checking backend closes that gap, and a lag threshold on the replica check keeps stale standbys out of rotation. Keepalived puts a floating VIP in front of two HAProxy instances so the proxy layer survives a host failure too.<\/p>\n\n\n\n