HAProxy and postgres

uozzyy

1/27/24, 4:06 AM

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.

905

1 + 0

postgresql

database

high-availability

haproxy

postgresql-cluster

Score:0

Server

Nikita Kipriyanov

1/27/24, 8:31 AM

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.

+ 0

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: HAProxy and postgres

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.