HA Proxy with Multiple Postgres Nodes(Using patroni)

Help!
1

I have a PostgreSQL setup with three nodes managed by Patroni for automated failover. When the primary node goes down, Patroni promotes a replica to primary via an election process.

I want to scale PostgreSQL queries effectively by routing read queries to the replicas and write queries to the primary.

Since there is no direct way to identify the query type in HAProxy, are there any indirect methods or scripts (like Lua) that can help determine the query type and make routing decisions? Suggestions are welcome!

I tried to reading req payload and lua script but not working

acl is_read_query req.payload(0,6) -m reg “^SELECT”

My haproxy.cfg


# Global settings
global
    log 127.0.0.1:514 local0
    user nobody
    group nogroup
    maxconn 2000

defaults
    log     global
    option  tcplog
    timeout connect 5000ms
    timeout client  50000ms
    timeout server  50000ms

# Frontend for handling PostgreSQL connections
frontend pg_frontend
    bind *:5500
    mode tcp
    option tcplog
    log global
    log 127.0.0.1:514 local0 info
    # Define ACL for read queries (SELECT)
    default_backend pg_primary_backend

# Backend for primary node (leader detection via Patroni API)
backend pg_primary_backend
    mode tcp
    option httpchk GET /patroni
    http-check expect rstring "\"role\":\s*\"primary\""
    balance roundrobin
    timeout check 5000ms
    log     global
    log 127.0.0.1:514 local0 info

    server node1 127.0.0.1:5432 check port 8008 maxconn 100
    server node2 127.0.0.1:5434 check port 8009 maxconn 100
    server node3 127.0.0.1:5436 check port 8010 maxconn 100

# Backend for replica node (replica detection via Patroni API)
backend pg_replica_backend
    mode tcp
    option httpchk GET /patroni
    http-check expect rstring "\"role\":\s*\"replica\""
    balance roundrobin
    timeout check 5000ms
    log     global
    log 127.0.0.1:514 local0 info

    server node1 127.0.0.1:5432 check port 8008 maxconn 100
    server node2 127.0.0.1:5434 check port 8009 maxconn 100
    server node3 127.0.0.1:5436 check port 8010 maxconn 100
2

No, haproxy is not aware of the PostgreSQL application protocol.

The distinction between read and write queries must be done elsewhere. Usually this is done in the application that opens 2 distinct ports on haproxy. One port is dedicated to read queries which can be load balanced across all replicas, and another port is dedicated to write queries that only hits your primary.

It’s possible there are sql aware middleware solutions that can make distinctions between read and write queries, however I would not know.