I’m using HAproxy to balance connections to a PostgreSQL+repmgr master/slave configuration.
I use an
external-check command to verify which postgres server is the master via an SQL query.
In front of HAproxy there’s a django-based application; all the services (django application, haproxy, postgres+repmgr) are in their own docker container.
I use the following HAproxy configuration:
global insecure-fork-wanted external-check defaults log global mode tcp retries 2 timeout client 30m timeout connect 4s timeout server 30m timeout check 5s listen stats mode http bind *:18080 maxconn 10 timeout server 100s timeout client 100s timeout connect 100s timeout queue 100s stats enable stats hide-version stats refresh 30s stats show-node stats uri /proxystats frontend pg_frontend mode tcp bind *:5432 default_backend pg_backend backend pg_backend mode tcp option external-check external-check command /scripts/check_postgresql_master.sh default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg-0 pg-0:5432 check server pg-1 pg-1:5432 check
When a PostgreSQL server goes down, HAproxy detects the new master and the new connections are routed to the new master.
If I connect to HAproxy via
psql client, I can connect to new master with no problem; If I had yet a connection to database via
psql client before new master promotion, and I run a query I have the following message:
template1=# select client_addr,sync_state from pg_stat_replication; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. psql (11.9 (Debian 11.9-0+deb10u1), server 11.10)
If I re-run the query, it works with no problems.
The django application, instead, doesn’t work until the old master becomes up and running; maybe the
on-marked-down directive closes only the connection from
backend to remote servers? Is there a way to force the
frontend to close connections when the backend switch to another server?
Sorry for the text-wall;