haproxy: Monitor PostgreSQL for current master
Published (updated: ) in linux.
There are couple of methods for haproxy to monitor what PostgreSQL instance is currently master so that “dumb” clients can always write to a PostgreSQL instance (instead of connecting to the standby node).
First there is the send-binary method by replicating the PostgreSQL V3 protocol but requires trusted connection and is TCP/IP only (no SSL). Another issue is that haproxy terminates the connection dirty when connecting to standby instances.
Another method is letting haproxy call xinetd service that runs script to check if the current node is the master.
Third method is letting haproxy call monitor script directly using the external-check command. This requires that the “psql” program be installed on the haproxy server.
In haproxy.cfg the “external-check” has to be enabled in the global preferences
global external-check # chroot cannot be enabled # chroot /var/lib/haproxy ... frontend pg_frontend mode tcp bind *:6001 default_backend pg_backend backend pg_backend mode tcp option external-check external-check command /path/to/monitor/script/check_postgresql_master.sh server pg01 10.0.0.1:5432 check inter 1s server pg02 10.0.0.2:5432 check inter 1s server pg03 10.0.0.3:5432 check inter 1s
The monitor script can be customized since it uses the standard psql command.
#!/bin/bash PG_MONITOR_USER=haproxy PG_MONITOR_PASS=haproxy PG_MONITOR_DB=postgres PG_PSQL=/usr/bin/psql VIP=$1 VPT=$2 RIP=$3 if [ "$4" == "" ]; then RPT=$VPT else RPT=$4 fi STATUS=$(PGPASSWORD="$PG_MONITOR_PASS" $PG_PSQL -qtAX -c "select pg_is_in_recovery()" -h "$RIP" -p "$RPT" --dbname="$PG_MONITOR_DB" --username="$PG_MONITOR_USER") if [ "$STATUS" == "f" ]; then # Master exit 0 else exit 1 fi
Remember to add the monitor user to the pg_hba.conf so haproxy can log in.
Caveat: Haproxy can’t run in chroot mode unless all binaries and library are put into the chroot directory.