In continuation to PostgreSQL 13 master with write-ahead log shipping replica post, the projecty thing required HAproxy added in front of the PostgreSQL databases to allow for failover in case of patching, etc. of the master.

PostgreSQL master and replica behind HAproxy

By the way when I was figuring this out I came across this page. The person was doing exactly the same, so that made my life quite easy. I did end up stealing his HAproxy check command - credit goes out to that person.

Make sure HAproxy version used is 1.8 or higher. Versions prior to 1.8 lack external-check, used to determine current PostgreSQL master. CentOS 7 has quite an old version of HAproxy included.

On the master server create a PostgreSQL user - haproxy to be used by HAproxy for monitoring. This user will be propagated to the replica.

# su - postgres
-bash-4.2$ psql
psql (13.3)
Type "help" for help.

postgres=# CREATE USER haproxy WITH PASSWORD 'haproxy';
CREATE ROLE
postgres=# quit
-bash-4.2$

On both, master and replica server, edit pg_hba.conf and add entry for PostgreSQL haproxy user that will be connecting from HAproxy server to and allow it to access postgres database. The IP address is the address of HAproxy server.

host    postgres        haproxy           192.168.100.100/32          scram-sha-256

Do not forget to restart PostgreSQL. To configure HAproxy add the following parameters to haproxy.cfg:

global
        # The following are needed to use external-check command in backend section below
        insecure-fork-wanted
        external-check

backend pgsql
        mode tcp
        external-check command /var/lib/haproxy/checkpg.sh
        option external-check
        server masterdb.unixpowered.com 10.10.10.10:5432 check inter 1s
        server replicadb.unixpowered.com 10.10.10.20:5432 check inter 1s

Above, checkpg.sh script is what figures out which PostgreSQL server is primary. The script looks at pg_is_in_recovery(). If true is returned then the server is in recovery, i.e. standby. Based on this value HAproxy can determine where to send database traffic.

#!/bin/bash

# These are variables that facilitate connection of PostgreSQL to check pg_is_in_recovery()
#
_PG_USER=haproxy
_PG_PASS=haproxy
_PG_DB=postgres
_PG_BIN=/usr/pgsql-13/bin/psql
#
# These are HAproxy virtual IP,port and real IP. These are passed as parameters to the check script.
# See https://web.archive.org/web/20211012185217/https://www.loadbalancer.org/blog/how-to-write-an-external-custom-healthcheck-for-haproxy/
_VIRT_IP=$1
_VIRT_PORT=$2
_REAL_IP=$3

if [ "$4" == "" ]; then
  _REAL_PORT=$_VIRT_PORT
else
  _REAL_PORT=$4
fi

STATUS=$(PGPASSWORD="$_PG_PASS" $_PG_BIN -qtAX -c "select pg_is_in_recovery()" -h "$_REAL_IP" -p "$_REAL_PORT" --dbname="$
_PG_DB" --username="$_PG_USER")

if [ "$STATUS" == "f" ]; then
  # We are in master mode
  exit 0
else
  exit 1
fi