Hi HAProxy community,
We have a fairly widely-used architecture to support HA postgres. This consists of haproxy sitting in front of two postgres servers set up with patroni and streaming replication. haproxy detects the cluster leader via the bgw_replstatus postgres plugin which advertises via a known port. When I force a patroni failover (essentially an automated switch of leader/replica for those not using patroni) haproxy detects this with no problems and new connections are routed as required.
My question is to do with how long this takes. I have a simple test script which does a table insert once every second. This will fail during a failover as the connection drops but I wait and retry after an interval. If I wait 5-10 seconds, all is well, but anything less than that and I risk picking up a connection to the replica instance and having failed inserts.
Is it typical to have to wait so long or am I missing something in terms of configuring haproxy or my client db libraries etc?
Thanks for any insight
m