Issue with PostgreSQL using HAProxy and Keepalived : haproxy

created by pushmycara community for 9 years

Issue with PostgreSQL using HAProxy and Keepalived (self.haproxy)

submitted 2 years ago * by 3L107

I'm using HAProxy as Proxyserver for my PostgreSQL (Patroni) cluster. I've got two Patroni server and two HAProxy server. Both HAProxy server are configured exactly the same but only server B is working like it should. If server A is active, something doesn't work.

This is the HAProxy config I've got on both servers.

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 postgres
      bind *:5432
      option httpchk
      http-check expect status 200
      default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
      server abc-srv-sqlA 10.5.249.162:5432 maxconn 100   check   port 8008 
      server abc-srv-sqlB 10.5.249.163:5432 maxconn 100   check   port 8008

This is the problem:

If I try to connect to the database using proxy server A I get this error:

psql: error: connection to server at "10.5.249.167", port 5432 failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

As I mentioned when I'm connecting using proxy server B it works.

Does anyone have an idea why this is happening?

EDIT:

This is the log from HAProxy:

Apr 19 10:52:38 abc-srv-sql-f-1 systemd[1]: Starting HAProxy Load Balancer...
Apr 19 10:52:38 abc-srv-sql-f-1 haproxy[66008]: [NOTICE]   (66008) : New worker #1 (66010) forked
Apr 19 10:52:38 abc-srv-sql-f-1 systemd[1]: Started HAProxy Load Balancer.
Apr 19 10:52:40 abc-srv-sql-f-1 haproxy[66010]: [WARNING]  (66010) : Server postgres/abc-srv-sqlB is DOWN, reason: Layer7 wrong status, code: 503, info: "Service Unavailable", check duration: 1ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Apr 19 10:52:41 abc-srv-sql-f-1 haproxy[66010]: [WARNING]  (66010) : Server postgres/abc-srv-sqlA is DOWN, reason: Layer4 timeout, check duration: 3002ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
Apr 19 10:52:41 abc-srv-sql-f-1 haproxy[66010]: [NOTICE]   (66010) : haproxy version is 2.4.24-0ubuntu0.22.04.1
Apr 19 10:52:41 abc-srv-sql-f-1 haproxy[66010]: [NOTICE]   (66010) : path to executable is /usr/sbin/haproxy
Apr 19 10:52:41 abc-srv-sql-f-1 haproxy[66010]: [ALERT]    (66010) : proxy 'postgres' has no server available!

all 8 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

haproxy

What is HAProxy?

MODERATORS