Hello,
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;
Thank you,
Alfredo