How to configure HAProxy with external-check to query PostgreSQL database directly

Querying the database replication state directly from PgBouncer connection pool in HAProxy

A key feature of HAProxy is its ability to perform health checks, ensuring that only healthy servers receive traffic. This blog post will guide you through configuring HAProxy with an external check to query PostgreSQL, providing an extra layer of assurance that your database servers are functioning correctly.

Our motivation for not using the Patroni REST API and instead querying the database directly was due to the decision to use PgBouncer on the database hosts itself.

When it comes to choosing the right architecture for your high-availability PostgreSQL database environment, you have lots of the different options. One option is for the application to connect to a connection pooler (PgBouncer), which then connects to HAProxy, and HAProxy connects to the PostgreSQL database.

The other option is for the application to connect to the load balancing solution (HAProxy), which then connects to the connection pooler, with the pooler operating in front of the database. Both options have different pros and cons.

Application -> Pgbouncer -> HAProxy -> PostgreSQL Database(patroni)

Application -> HAproxy -> Pgbouncer -> PostgreSQL Database(patroni)

Since PgBouncer is a lightweight service that requires database administrators’ expertise to configure optimally and also reduces network latency, we chose to deploy PgBouncer directly on the PostgreSQL database servers. However, with this architecture, if we relied on health checks from the Patroni REST API, there was a risk of not detecting issues with the pooler application (PgBouncer). Therefore, we decided to use an external query for health checks in HAProxy by executing a simple query like the one below.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

Patroni maintains a permanent connection to PostgreSQL and, with every heartbeat, it calls a few functions located in pg_catalog, such as pg_is_in_recovery() and pg_current_wal_lsn(). Similarly, we will perform these checks directly from PgBouncer to ensure that both PgBouncer and the PostgreSQL database are functioning correctly.

First, we will create a database user specifically for querying the database for health checks.

postgres=#  create user haproxy with encrypted password 'haproxy';
postgres=#  grant execute  on pg_is_in_recovery to haproxy;

Here is the sample configuration of HAProxy for external checks. HaProxy server should have psql client for querying database directly. We will install postgresql16.

[root@etcd02 ~]# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@etcd02 ~]# yum --disablerepo="*" --enablerepo="pgdg14" -y install postgresql16
[root@etcd02 ~]# cat /etc/haproxy/haproxy.cfg
global
    maxconn 4096
    insecure-fork-wanted
    external-check
defaults
    log     global
    mode    tcp
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s
listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
listen primary
    bind 192.168.60.120:3500
    maxconn 2048
    option external-check
    external-check command /etc/haproxy/primary-check.sh
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server posvt01.localdomain posvt01.localdomain:3535 check
    server posvt02.localdomain posvt02.localdomain:3535 check
listen standby
    bind 192.168.60.120:3501
    maxconn 2048
    option external-check
    external-check command /etc/haproxy/standby-check.sh
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server posvt01.localdomain posvt01.localdomain:3535 check
    server posvt02.localdomain posvt02.localdomain:3535 check

[root@etcd02 ~]# cat /etc/haproxy/primary-check.sh
#!/bin/bash
VIP=$1
VPT=$2
RIP=$3
PG_MONITOR_USER=haproxy
PG_MONITOR_PASS=haproxy
PG_MONITOR_DB=haproxy
if [ "$4" == "" ]; then
  RPT=$VPT
else
  RPT=$4
fi
STATUS=$(PGPASSWORD="$PG_MONITOR_PASS" /usr/bin/psql -qtAX -c "select pg_is_in_recovery()" -h "$RIP" -p "$RPT" --dbname="$PG_MONITOR_DB" --username="$PG_MONITOR_USER")
#echo "$@ status=$STATUS"
if [[ "$STATUS" == "f" ]]
then
  exit 0
else
  exit 1
fi
[root@etcd02 ~]# chmod +x /etc/haproxy/primary-check.sh

[root@etcd02 ~]# cat /etc/haproxy/standby-check.sh
#!/bin/bash
VIP=$1
VPT=$2
RIP=$3
PG_MONITOR_USER=haproxy
PG_MONITOR_PASS=haproxy
PG_MONITOR_DB=haproxy
if [ "$4" == "" ]; then
  RPT=$VPT
else
  RPT=$4
fi
STATUS=$(PGPASSWORD="$PG_MONITOR_PASS" /usr/bin/psql -qtAX -c "select pg_is_in_recovery()" -h "$RIP" -p "$RPT" --dbname="$PG_MONITOR_DB" --username="$PG_MONITOR_USER" )
if [[ "$STATUS" == "t" ]]
then
  exit 0
else
  exit 1
fi
[root@etcd02 ~]# chmod +x /etc/haproxy/standby-check.sh

By deploying PgBouncer directly on PostgreSQL database servers, we minimized the network latency between PgBouncer and the database, which led to slightly faster query processing times compared to running PgBouncer on separate servers. This simplified the application architecture and reduced the need for coordination with application teams. Additionally, we have configured PgBouncer to connect to the database locally (using Unix sockets), which is more secure and performant than configuring remote connections.

Hope it helps.