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:

    log        local2
    maxconn             4000

    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
  • Regarding your problem with PG-Pool II, did you ever try setting memory_cache_enabled = off? Others have reported problems when connecting from Tableau when this setting is on and setting it to offseems to do the trick: pgpool.net/mantisbt/view.php?id=118.
    – JohnP
    Dec 2, 2015 at 5:01
  • Thanks for your answer @JohnP . Sadly, yes i've seen that link before, but in my PgPool it was off by default. Later I contacted Tableau support and they said it don't support PgPool, looks like it's not transparent PgPool for Tableau. But HAProxy appears to be our solution. Thanks! Dec 2, 2015 at 19:34


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.