haproxy: Monitor PostgreSQL for current master

September 24, 2019 - Reading time: 2 minutes

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.


Convert PostgreSQL cluster to use page checksums

February 26, 2019 - Reading time: 3 minutes

When upgrading PostgreSQL cluster from one major version to another you cannot use pg_upgrade tool to upgrade/convert cluster with page checksums disabled to a cluster with page checksums enabled.

Instead you have to do dump and restore.

On Debian/Ubuntu based systems this is made easier using the pg_createcluster / pg_dropcluster commands.

Here I'm converting a PostgreSQL 11 cluster named 11/main (with no page checksums enabled) to new cluster named 11/data with page checksums enabled.

Start by editing /etc/postgresql-common/createcluster.conf and change

#initdb_options = ''

to

initdb_options = '--data-checksums'

pg_lsclusters should only show one cluster running:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

Create a new cluster with

$ pg_createcluster 11 data

This creates a empty cluster with page checksums enabled on another port (usually port 5433). Verify it with pg_lsclusters:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

11  data    5433 online postgres /var/lib/postgresql/11/data /var/log/postgresql/postgresql-11-data.log

Verify that page checksums are enabled with

$ psql -p 5433 -c "show data_checksums"

 data_checksums
----------------
 on
(1 row)

To dump and reload it is simplest to do (as the postgres user)

$ pg_dumpall --port=5432 --clean | psql --port=5433

This dumps all users and databases and restores them to the new cluster.

After this is done (assuming no transactions happened in the meantime) remember to copy pg_hba.conf and other settings from /etc/postgresql/11/main to /etc/postgresql/11/data.

Drop the old cluster with

$ pg_dropcluster 11 main

and change the port on 11/data to 5432.