How can haproxy manage load balancing in case of primary goes down.

[Ashishveer0710]

How can we ensure in case of primary goes down and vip still points to the same server that my write operation always goes to primary server without failure.

If it is not possible can we have pgpool in this setup?

[vitabaks]

HAProxy checks the role of each server through the Patroni REST API, so it does not matter which balancing the connection is made through, since each haproxy will know about the current cluster topology.

[Ashishveer0710]

to ensure connection pooling and load balancing without write queries going to read replicas , how should I proceed ?

For connection pooling , I use below query :

psql -p 15432 (pgbouncer port)

and for haproxy :

psql -p 25432 (haproxy port) :: question : is this connection using connection pooling as well as explained in the above image and is it going to only master ?

I am getting " update cannot be done in read-only transaction" error .

[vitabaks]

is this connection using connection pooling as well as explained in the above image and is it going to only master ?

yes, if pgbouncer_install is true then the connection will be via pgbouncer. See the haproxy configuration there should be a pgbouncer port.

I am getting " update cannot be done in read-only transaction" error .

Perhaps your application maintains a permanent connection to the database and HAProxy does not break these connections when changing the Postgres role. \ In this scenario, I use a script in patroni_callbacks that will restart haproxy if the Postgres role has been changed. In this case, permanent connections will be forcibly terminated and the application will be able to make a new connection to the new Primary.

Example:

patroni.yml

  callbacks:
      on_role_change: '/etc/patroni/haproxy_restart.sh'

haproxy_restart.sh

#!/bin/bash
# /etc/patroni/haproxy_restart.sh
# restart haproxy on all servers in the cluster
for host in $(patronictl -c /etc/patroni/patroni.yml list -f json | jq -r '.[] | .Host'); do
  ssh \
  -o StrictHostKeyChecking=no \
  -o UserKnownHostsFile=/dev/null \
  postgres@"${host}" \
  sudo systemctl restart haproxy
done