HAProxy + pgbouncer server closed the connection unexpectedly

Question

I have an architecture of haproxy on web server which is routing to slave servers, where I have pgbouncer over postgre. HAPoxy config:

global
    log 127.0.0.1 local1 debug
    user haproxy
    group haproxy

defaults
    log global
    retries 3
    timeout connect 1s
    timeout server 20m
    timeout client 20m

listen pgsql-cluster
    bind 127.0.0.1:5433
    mode tcp
    balance leastconn
    #option pgsql-check user postgres - 
    default-server inter 1s downinter 1s rise 2 fall 1
    server pgsql-1 10.5.8.14:6432 check
    server pgsql-2 10.5.8.21:6432 check

Pgbouncer config is default. First problem, pgsql-check is not working. pgboucer.log:

2016-11-23 12:47:37.805 17068 WARNING C-0x23bb760: (nodb)/(nouser)@10.5.8.13:48898 Pooler Error: No such database: postgres
2016-11-23 12:47:37.805 17068 LOG C-0x23bb760: (nodb)/(nouser)@10.5.8.13:48898 login failed: db=postgres user=postgres

But the biggest problem, is that after some period of time i've got many errors in my site (Yii2). Log:

2016-11-22 16:37:42 [10.5.33.135][-][-][error][application] SQLSTATE[08006] [7] server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request. in file /var/www/copy-search/vendor/yiisoft/yii2/db/Connection.php on line 547

I've got the feeling, that haproxy just breaks the session.

quasar · Answer 1 · 2017-11-16T15:32:25.030

To answer the first problem make sure that a user named postgres is defined in your PG Bouncer configs. I currently have HA Proxy setup to distribute load between two PG Bouncer processes and have a postgres user and a postgres database defined in my configs and the health checks work. As for the second problem I am currently getting the same error.

Update:

I increased the timeouts on HA Proxy and this resolved my issue.

listen pgsql-cluster
   bind 127.0.0.1:5433
   mode tcp
   balance leastconn
   timeout client 30h
   timeout server 30h
   #option pgsql-check user postgres - 
   default-server inter 1s downinter 1s rise 2 fall 1
   server pgsql-1 10.5.8.14:6432 check
   server pgsql-2 10.5.8.21:6432 check

HAProxy + pgbouncer server closed the connection unexpectedly

1 Answers1