# Postgres loadbalancing - no connections in logs

**URL:** <https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430>\
**Category:** Help!\
**Created:** [June 9, 2016, 9:00am UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430 "2016-06-09T09:00:21Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![sauron](https://avatars.discourse-cdn.com/v4/letter/s/ce7236/32.png) [@sauron](https://discourse.haproxy.org/u/sauron)\
**Post date:** [June 9, 2016, 9:00am UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430/1 "2016-06-09T09:00:21Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![lukastribus](https://avatars.discourse-cdn.com/v4/letter/l/7ea924/32.png) [@lukastribus](https://discourse.haproxy.org/u/lukastribus)\
**Post date:** [June 9, 2016, 3:21pm UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430/2 "2016-06-09T15:21:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sauron](https://avatars.discourse-cdn.com/v4/letter/s/ce7236/32.png) [@sauron](https://discourse.haproxy.org/u/sauron)\
**Post date:** [June 13, 2016, 11:10am UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430/3 "2016-06-13T11:10:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lukastribus](https://avatars.discourse-cdn.com/v4/letter/l/7ea924/32.png) [@lukastribus](https://discourse.haproxy.org/u/lukastribus)\
**Post date:** [June 13, 2016, 11:39am UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430/4 "2016-06-13T11:39:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sauron](https://avatars.discourse-cdn.com/v4/letter/s/ce7236/32.png) [@sauron](https://discourse.haproxy.org/u/sauron)\
**Post date:** [June 13, 2016, 11:44am UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430/5 "2016-06-13T11:44:30Z")

</div>

> [@lukastribus](#):
>
> he server, is that what you are saying?

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.

---

<div class="post-metadata">

**Author:** ![lukastribus](https://avatars.discourse-cdn.com/v4/letter/l/7ea924/32.png) [@lukastribus](https://discourse.haproxy.org/u/lukastribus)\
**Post date:** [June 13, 2016, 11:57am UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430/6 "2016-06-13T11:57:45Z")

</div>

So what did you actually mean when you said:

> [@sauron](#):
>
> I see SQL queries as well (ack packets)

---

<div class="post-metadata">

**Author:** ![sauron](https://avatars.discourse-cdn.com/v4/letter/s/ce7236/32.png) [@sauron](https://discourse.haproxy.org/u/sauron)\
**Post date:** [June 13, 2016, 12:21pm UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430/7 "2016-06-13T12:21:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lukastribus](https://avatars.discourse-cdn.com/v4/letter/l/7ea924/32.png) [@lukastribus](https://discourse.haproxy.org/u/lukastribus)\
**Post date:** [June 13, 2016, 3:24pm UTC](https://discourse.haproxy.org/t/postgres-loadbalancing-no-connections-in-logs/430/8 "2016-06-13T15:24:06Z")

</div>

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?