xf.is blog

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.