0

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.

2 Answers 2

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.