PostgreSQL 13 master and replica behind HAproxy
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.
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