PostgreSQL HA using HAProxy and Patroni

haproxypostgresql

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)