Load Balancing using HAProxy for Postgresql 9.4

Question

I have made a setup of multi-master replication of PostgreSQL using BDR (Bi-Directional Replication) among 4 nodes (virtual machines).

Now i want to put a load-balancer for High Availability. For this i have installed and configured "HAProxy" on a different virtual machine, which is listening over 5432/tcp to connect. The haproxy configuration is as follows:

listen pgsql_bdr *:5432
    mode tcp
    option  httpchk
    balance roundrobin
    server master 192.168.123.1:5432 check backup
    server slave1 192.168.123.2:5432 check
    server slave2 192.168.123.3:5432 check
    server slave3 192.168.123.4:5432 check

The ip address of HAProxy server is 192.168.123.5

I have use the IP address of HAproxy server in my application to connect with the database (that must redirect the connection to actual database servers). But at that time i am getting following error:

Error connecting to the server: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

And note that i have try to resolve the problem using 2 manner. 1st I disabled the firewall on all servers (HAProxy and all postgres servers) and also i try to replace configuration with the following:

listen pgsql_bdr 0.0.0.0:5432

or

listen pgsql_bdr 127.0.0.1:5432

or

listen pgsql_bdr localhost:5432

But all was not working in my case.

Please help me to solve the problem. what i am doing wrong in this scenario?

Thanks in advance!

Answer 1

Check your haproxy /stats, I am guessing that all backends are marked down, due to wrong check - postgres probably won't respond with 200 - option httpchk. Use pgsql-check

option pgsql-check user healcheckuser

Also I am not aware of BDR requirements/limitations, but it is common in slaves setups, to have some pooler e.g. pgbouncer - next to client, or in front of each backend, or whatever that is needed.

edit

To check stats you can use stats socket:

# stats about all frontents and backends in a csv format
echo "show stat" | socat unix-connect:/run/haproxy/admin.sock stdio

# version, PID, current connections, session rates, tasks, etc
echo "show info" | socat unix-connect:/run/haproxy/admin.sock stdio

# sessions with the used backend/frontend, the source
echo "show sess" | socat unix-connect:/run/haproxy/admin.sock stdio

# informations about errors if there are any
echo "show errors" | socat unix-connect:/run/haproxy/admin.sock stdio

As I tested with postgres 9.4, (some slaves, no BDR), haproxy 1.5.14, no checks

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

listen pgsql *:15432
    mode tcp
    balance roundrobin
    server master dev1.db.fed:5432
    server slave1 dev2.db.fed:5432

And I was able to connect without any issue:

root@lb:/# echo "show sess" | socat unix-connect:/run/haproxy/admin.sock stdio
0x20d89f0: proto=tcpv4 src=10.163.74.109:46815 fe=pgsql be=pgsql srv=slave1 ts=08 age=3m8s calls=3 rq[f=848202h,i=0,an=00h,rx=,wx=,ax=] rp[f=048202h,i=0,an=00h,rx=,wx=,ax=] s0=[7,8h,fd=1,ex=] s1=[7,8h,fd=2,ex=] exp=
0x20e11a0: proto=unix_stream src=unix:1 fe=GLOBAL be=<NONE> srv=<none> ts=09 age=0s calls=2 rq[f=c08200h,i=0,an=00h,rx=30s,wx=,ax=] rp[f=008002h,i=0,an=00h,rx=,wx=,ax=] s0=[7,8h,fd=3,ex=] s1=[7,0h,fd=-1,ex=] exp=30s

Queries are running, data is coming, seems OK.

But again, I'm not able to test BDR at the moment.