HAProxy community

PostgreSQL + HAProxy + Patroni - 1 HAProxy endpoint to listen to read and write. is it possible?

Hello,
I have the following topology:
4 postgresql servers running on 2 different datacenters, 2 on each site.
The production site needs to be read write and the DR site only read.
Patroni is replicating postgresql databases between all nodes and is working well.

The main goal is to accomplish HAproxy to listen on the same port for read and write.

On the statistics report, I see that HAproxy is showing the correct Master node and the correct replica node. i tested failover between the nodes and HAproxy recognized the failover.

The issue here is that HAproxy is not sending the write request to the master. it looks like it’s doing round robin or even sending the requests randomly between the master backend and the replica backend.

Hope somebody can help.
Thanks,
Amit

This is my configuration on the production site:

global
log 127.0.0.1:514 local0
maxconn 500

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 master_postgresql
bind *:5000
acl is_write method POST UPDATE DELETE
use_backend backend_master if is_write
default_backend backend_master

frontend replicas_postgresql
bind *:5000
acl is_read method GET
use_backend backend_replicas if is_read
default_backend backend_replicas

backend backend_master
option httpchk OPTIONS /master
server pg01 pg01:5432 check port 8008
server pg02 pg02:5432 check port 8008

backend backend_replicas
option httpchk OPTIONS /replica
server pg01 pg01:5432 check port 8008
server pg02 pg02:5432 check port 8008