Thursday, December 29, 2011

Postgresql - pgpool - a wary pgpool_failover script

I came up with this script to issue automatic failover of the master node in the cluster.

#!/bin/bash
# Failover command for streaming replication
# If standby goes down, does nothing. 
# If primary goes down, create a trigger file so that one standby
# takes over the primary node role

failed_node=$1
old_master=$2
trigger_file=$3

standby1=10.0.0.201
standby2=10.0.0.202
standby3=10.0.0.113

if [ $failed_node = $old_master ]; then
#create the trigger file in the first available standby
#test for the next available standby 
CMD=`ssh postgres@$standby1 "pg_ctl status | grep PID"`
ANSWER=`echo $CMD`
if [ -n "$ANSWER" ]; then
 CMD=`ssh postgres@$standby1 "touch $trigger_file"`
 echo $CMD
 exit 0;
fi
CMD=`ssh postgres@$standby2 "pg_ctl status | grep PID"`
ANSWER=`echo $CMD`
if [ -n "$ANSWER" ]; then
 CMD=`ssh postgres@$standby2 "touch $trigger_file"`
 echo $CMD
 exit 0;
fi
CMD=`ssh postgres@$standby3 "pg_ctl status | grep PID"`
ANSWER=`echo $CMD`
if [ -n "$ANSWER" ]; then
 CMD=`ssh postgres@$standby3 "touch $trigger_file"`
 echo $CMD
 exit 0;
fi
exit 1;
fi

I say it's wary as it tests whether the first standby node is up and running, if not tests if the second is,then the thrid... else returns a failure code (non cero integer).
Note: when a standby node (in constant recovery state) identifies the existence of the trigger file defined in the recovery.conf file it stops replicating from the allegedly dead master and becomes an independent server and enables write capabilities.

I used this piece of advice:
http://www.panix.com/~elflord/unix/bash-tute.html

Using Quotes to enclose your variables

Sometimes, it is a good idea to protect variable names in double quotes. This is usually the most important if your variables value either (a) contains spaces or (b) is the empty string. An example is as follows:

#!/bin/bash
X=""
if [ -n $X ]; then # -n tests to see if the argument is non empty
echo "the variable X is not the empty string"
fi


This script will give the following output:

the variable X is not the empty string

Why ? because the shell expands $X to the empty string. The expression [ -n ] returns true (since it is not provided with an argument). A better script would have been:

#!/bin/bash
X=""
if [ -n "$X" ]; then # -n tests to see if the argument is non empty
echo "the variable X is not the empty string"
fi


In this example, the expression expands to [ -n "" ] which returns false, since the string enclosed in inverted commas is clearly empty.

Sometimes scripts are a reflexion of the writer's personality =-)

No comments:

Post a Comment