PGPool II - Failover when current master node dies

Question

I have a 2 node high-availability postgresql 9.5 cluster. I'm using streaming replication, PGPool II (3.5.4 [ekieboshi]), and a virtual (or floating) secondary IP all hosted on aws to accomplish this.

I've configured the PGPool Watchdog and it is running well but only seems to be executing a failover script on the failed node when the postgresql service instance dies.

I'd like to be able to execute a failover script on the slave node when the master becomes unreachable. Is there any hook for this in PGPool II? If not, is this not really even a good idea? Thanks in advance for your help.

Answer 1

Sounds like you might have incorrect values for your failover_command value. And Typically the failover script is run on pgpool server, not the database node.

failover_command = '/etc/pgpool-II/failover.sh %d %H %P /tmp/postgresql.trigger.failover [hostname of failover pgpool server]'

This configuration above is passing the new master candidate and an argument to my failover script which is executed on pgpool but ultimately promotes the master candidate (which is a hot standby) to a master using postgresql.

Answer 2

The hook you need is failover_command.

All the scripts defined in PgPool's configuration - failover_command, follow_master_command, failback_command, etc - are executed by the pgpool master process. You can check which one is master using pcp_watchdog_info utility.

It means that if you want to execute commands on PostgreSQL node you need to setup password-less auth from pgpool into postgres nodes and use ssh inside the script. To give an example, in my failover.sh I have this:

#!/bin/bash
node=$1
host=$2
port=$3
pgdata=$4
newmasternode=$5
newmasterhost=$6
oldmasternode=$7
oldprimarynode=$8
newmasterport=$9
newmasterpgdata=${10}

echo "$0 params: node=$node host=$host port=$port pgdata=$pgdata newmasternode=$newmasternode newmasterhost=$newmasterhost oldmasternode=$oldmasternode oldprimarynode=$oldprimarynode newmasterport=$newmasterport newmasterpgdata=$newmasterpgdata"

ssh_opts="-o BatchMode=yes -o StrictHostKeyChecking=no -o CheckHostIP=no -o ConnectTimeout=10"

echo "$0 Disabling postgresql on $host"
ssh $ssh_opts -l root $host 'systemctl mask postgresql --now'

if [ $newmasternode -ne $oldmasternode ]; then
    echo "$0 Promoting postgresql on $newmasterhost"
    ssh $ssh_opts -l root $newmasterhost 'pg_ctlcluster 10 main promote'
fi