How to configure HaProxy to balance read requests to all nodes of a PostgreSQL cluster?

Question

I have a PostgreSQL cluster on Patroni (Haproxy+Keepalived+etcd) - one primary node and two standby nodes.

For now, Haproxy is configured in this way:

• port 5000 to connect to the primary node
• port 5001 to connect to the standby nodes

How can I configure Haproxy so that the port 5001 is used to connect to the standby nodes as well as the primary node?

This is my haproxy.cfg below:

global
maxconn 1000
nbproc 2

defaults
log global
mode tcp
retries 2
timeout client 30m
timeout connect 4s
timeout server 30m
timeout check 5s

listen stats
  mode http
  bind *:7000
  stats enable
  stats uri /

frontend ft_postgresql
bind *:5000
default_backend postgres-patroni

frontend ft_postgresql_replica
bind *:5001
default_backend postgres-patroni-replica

backend postgres-patroni
  option httpchk OPTIONS /master

  http-check expect status 200
  default-server inter 3s fall 3 rise 2

  server node_one ip.to.node.one:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions
  server node_two ip.to.node.two:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions
  server node_three ip.to.node.three:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions

backend postgres-patroni-replica
  option httpchk OPTIONS /replica

  http-check expect status 200
  default-server inter 3s fall 3 rise 2

  server node_one ip.to.node.one:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions
  server node_two ip.to.node.two:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions
  server node_three ip.two.node.three:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions

Answer 1

In a patroni documentation I found the /health endpoint patroni rest-api:

returns HTTP status code 200 only when PostgreSQL is up and running.

I tried to use that endpoint in haproxy configuration, and it works like expected, patroni give all 3 nodes when all nodes alive, and don't give nodes that aren't in running state

So, if you want to add all nodes to haproxy balance, create a new backend in haproxy.conf

backend postgres-patroni-all
  option httpchk OPTIONS /health

  http-check expect status 200
  default-server inter 3s fall 3 rise 2

  server node_one ip.to.node.one:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions
  server node_two ip.to.node.two:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions
  server node_three ip.two.node.three:5432 maxconn 1000 check port 8008 on-marked-down shutdown-sessions

And frontend for this backend, for example in 5002 port:

frontend ft_postgresql_all
bind *:5002
default_backend postgres-patroni-all