Postgres loadbalancing - no connections in logs

Help!
1

Hi Guys,

I tried to find more info regarding issue that I found. Hope it’s configuration issue rather than a bug in Haproxy. I have 2 postgres backends and 1 tcp frontend. Despite the fact that I see quit big tcp traffic incoming to frontend and balanced between each backends in tcpdump, I see no connections in Haproxy stats(checked with tcpdump -i eth0 -s 0 -l -w - dst port 5432 | strings)
However when I connect by myself using psql client to haproxy it shows an entry in logs, but balancer traffic from application to haproxy not.

HA-Proxy version 1.6.5 2016/05/10

Here is my conf:

global
log /dev/log local0 debug
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin
stats timeout 30s
user haproxy
group haproxy
daemon
maxconn 200

    # Default SSL material locations
    ca-base /etc/ssl/certs
    crt-base /etc/ssl/private

    # Default ciphers to use on SSL-enabled listening sockets.
    # For more information, see ciphers(1SSL). This list is from:
    #  https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/
    ssl-default-bind-ciphers ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:ECDH+3DES:DH+3DES:RSA+AESGCM:RSA+AES:RSA+3DES:!aNULL:!MD5:!DSS
    ssl-default-bind-options no-sslv3

defaults
log global
mode tcp
option tcplog
timeout connect 5000
timeout client 20m
timeout server 20m
errorfile 400 /etc/haproxy/errors/400.http
errorfile 403 /etc/haproxy/errors/403.http
errorfile 408 /etc/haproxy/errors/408.http
errorfile 500 /etc/haproxy/errors/500.http
errorfile 502 /etc/haproxy/errors/502.http
errorfile 503 /etc/haproxy/errors/503.http
errorfile 504 /etc/haproxy/errors/504.http

frontend ft_replication
bind someip:5433
mode tcp
default_backend bk_repl

backend bk_repl
mode tcp
option pgsql-check user postgres
balance roundrobin
server backend1 someip:5432 check
server backend2 someotherip:5432 check

listen haproxy-monitoring
bind someip:80
mode http
stats enable
stats show-legends
stats refresh 5s
stats uri /haproxy?stats
stats realm haproxy\ Statistics
stats auth xxx:xxx
stats admin if TRUE
~

Any clues ?

2

Do you see the application traffic connecting to haproxy while tcpdumping the frontend traffic (port 5433, not 5432)?

3

Yes. When run tcpdump on frontend port 5433 on haproxy I see SQL queries as well (ack packets). but still haproxy stats shows 0 sessions, 0 errors. Running it for couple days and see total session 7 - probably output from direct psql tests I made myself. That’s totally strange.

4

So you don’t see the incoming request, only the responses from the server, is that what you are saying?

5

No. What I’m saying is that I see incoming and outgoing traffic passing haproxy only in tcpdump, but not haproxy stats page or logs. From what investigate based on tcpdump traffic incoming traffic is coming to haproxy correctly to port 5433 and balanced by haproxy and sent to appropriate backend. Which means that haproxy is working correctly, however I don’t have coverage of current situation in logs or stats. Both shows no traffic at all.

6

So what did you actually mean when you said:

7

These are my queries I see with tcpdump:

Output from frontend sniffing

tcpdump -i eth0 -s 0 -l -w - dst port 5433 | strings

tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes

/40/SELECT MYQUERY GOES HERE
5976517

/60/SELECT ANOTHER QUERY HERE

Output from sniffint outbound traffic to backends:

tcpdump -i eth0 -s 0 -l -w - dst port 5432 | strings

tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes

user
postgres

/10/SELECT SOME QUERY HERE

And traffic sniffing with tcpdump ack’s

tcpdump -i eth0 tcp src or dst port 5433 -n

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
13:16:52.528100 IP APP_IP.60177 > HAPROXY_IP.5433: Flags [P.], seq 1410670024:1410671367, ack 3784817119, win 4220, length 1343
13:16:52.550165 IP HAPROXY_IP.5433 > APP_IP.60177: Flags [.], seq 1:2921, ack 1343, win 1452, length 2920
13:16:52.550215 IP HAPROXY_IP.5433 > APP_IP.60177: Flags [.], seq 2921:5841, ack 1343, win 1452, length 2920
13:16:52.550320 IP HAPROXY_IP.5433 > APP_IP.60177: Flags [P.], seq 5841:8193, ack 1343, win 1452, length 2352

8

I suggest you use haproxy logging to understand what happens here (not sure if thats what you are referring to with “log” in the initial post).

Make sure your logging configuration works fine first (you are using chroot and a unix-domain socket log destination, so your syslog deamon needs to listen in /var/lib/haproxy/dev/log; make sure the haproxy user has write permissions to it). You can also log via IP.

You said that “using psql client to haproxy it shows an entry in logs”, can you elaborate what the difference is between the working client, and the non-working client, and what exact logs you are referring to?