I'm trying to load balance between servers running Postgresql (Master-Slave configuration with Streaming Replication). First I've tried setting up PgPool-II, and it worked fine when I connected some app we're using here, but we use Tableau (some tool for reporting) and Tableau can't connect correctly, so now I'm searching for alternatives to load balance, and I've read in many places that HAProxy is an good alternative to load balance, but can't find a good guide to configure it to function with Postgres.
Following some articles I think I managed to configure something very basic, I managed to start HAProxy running in port 10001, but when I try to connect via psql, I get the error:
psql: The server has closed the connection unexpectedly
(I translated it from Spanish, so may not be the exact message in English).
I get that error immediately, so I'm not sure if it's something in the server running HAProxy or in the server running Postgresql.
At the moment, for testing, I'm running HAProxy in one server and configured to only connect to one server.
My configuration in the HAProxy server is like this:
global
log 127.0.0.1 local2
maxconn 4000
defaults
mode tcp
log global
option dontlognull
retries 3
timeout connect 1m
timeout client 1m
timeout server 1m
maxconn 3000
listen psql_cluster :10001
mode tcp
server psql1 [ip_server_postgres]:5432
Both servers are running Centos 7, I'm running HAProxy 1.5.15 and Postgresql 9.4.
For the record, the communication between the server HAProxy and the master with Postgres works fine with user Postgres...
PS: While writing this, I thought that may be because the new user haproxy can't connect to the master server? Should I be able to ssh from one to another?. If I try "su haproxy" it says "This account is currently not available". Maybe is one of this things?
EDIT I found in the logs of the HAProxy Server that indeed my connection is getting received by the server:
psql_cluster postgres/psql1 1/-1/0 0 SC 0/0/0/0/3 0/0
