I am currently trying to deploy Load Balancing using HAProxy for my PostgreSQL cluster (used Patroni HA solution). Now, I have been able to successfully implement the setup and Load Balancing is working to an extent. My HAProxy configuration :
global
maxconn 100
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 /
listen read_write
bind *:5004
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server pg1 IP:5432 maxconn 100 check port 8008
server pg2 IP:5432 maxconn 100 check port 8009
listen read_only
bind *:5005
mode tcp
balance roundrobin
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server pg1 IP:5432 maxconn 100 check port 8008
server pg2 IP:5432 maxconn 100 check port 8009
However, my question is how will the application interact with HAProxy for read only requests. Does the application need to connect using port 5005 or is there any internal logic applied by HAProxy to do this.
This is my first time implementing a Load Balancing setup, hence would appreciate your patience. 🙂
Best Answer
your application must use port 5005 for each query known to be read-only (not only SELECT, because some SELECT can call custom functions)