HAProxy and postgres

Question

My haproxy configuration is as below, in the backend there is pg_autoctl cluster, another VM (bunty4) hosts the monitor and haproxy is installed on it.

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 tcp
    bind *:7000
    stats enable
    stats uri /
listen ReadWrite
    bind *:5000
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server bunty1 bunty1:6001 maxconn 100 check port 23267
    server bunty2 bunty2:6002 maxconn 100 check port 23267
    server bunty3 bunty3:6003 maxconn 100 check port 23267
listen ReadOnly
    bind *:5001
    option httpchk
    http-check expect status 206
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server bunty1 bunty1:6001 maxconn 100 check port 23267
    server bunty2 bunty2:6002 maxconn 100 check port 23267
    server bunty3 bunty3:6003 maxconn 100 check port 23267

This runs fine:

postgres@bunty4:~$ psql -h bunty2 -p 6002
psql (14.6 (Ubuntu 14.6-1.pgdg22.04+1))
Type "help" for help.
postgres=# \q
postgres@bunty4:~$ psql -h bunty1 -p 6001
psql (14.6 (Ubuntu 14.6-1.pgdg22.04+1))
Type "help" for help.

When I try to connect frontend port 7000 (thru pgadmin or cli) it gives an error:

postgres@bunty4:~$ psql -h localhost -p 7000
psql: error: connection to server at "localhost" (127.0.0.1), port 7000 failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql -h 192.168.5.129 -p 7000   ## this also fails with same error.

Not sure why, haproxy.log doesn't throw up anything.

Nikita Kipriyanov · Accepted Answer · 2023-01-27T08:52:16.840

In this configuration of haproxy port 7000 listener on the balancer has no backends, instead it has stats enable. This means it is reserved for showing haproxy statistics. However, it is incorrectly configured to be in tcp mode; stats page requires http. So set it to mode http and try connecting to bunty4:7000 with the web browser. It will show a nice stats web page.

It seems psql or other PostgreSQL clients should connect to haproxy (bunty4) port 5000 for writing and reading or port 5001 for reading. However, the exact logic behind those ports is hidden in the service on port 23267 which decides somehow whether its instance is read-only or read-write, and reports the corresponding status 206 or 200; from what you've shown it is not evident what the role of those ports are. Their names are the only hints.

Also I don't get why different servers host the service on different ports. This makes the backend configuration less symmetric and therefore it is bad, because they should be ideally exact clones of each other. For me it is natural to host PostgreSQL on default port 5432 if it's the only service on the machine, which it seems to be true for all three backend servers.

HAProxy and postgres

1 Answers1