Monitor PG WarmStandBy

While working with Database, some people have asked to write a Monitoring script for WarmStandby. Here is a script which can be use for it:

#!/bin/bash

BIN=/opt/PostgresPlus/8.4SS/bin
WARMDATA=/sata/data
PSQL=$BIN/psql
PRIM_HOST=primarserver.ic
ST_LOGFILE=/usr/local/pgsql/pg_standby/standby.log
STANDBYLOGLOC=/sata/backups/pg8.4/standby_wal
echo -e ""
echo -e "+-----------------------------------------------------------+"
echo -e "|Finding the Minimum Recovery:                              |"
echo -e "+-----------------------------------------------------------+"
MINRECOV=`$BIN/pg_controldata $WARMDATA |grep 'Minimum recovery'|awk -F": " '{print $2}'|sed 's/ *//g'`
MINRECOVWAL=`$PSQL -h  $PRIM_HOST -t -p 5432 -c "select  pg_xlogfile_name('$MINRECOV');"|sed 's/ *//g'`
if [ -n $MINRECOVWAL ];then
echo -e "|Minimum Recovery WAL file require: $MINRECOVWAL|"
echo -e "+-----------------------------------------------------------+"
fi
ls -1 $STANDBYLOGLOC >/tmp/WAL.lis
WALNUM=`cat -n /tmp/WAL.lis|grep $MINRECOVWAL|awk '{print $1}'`
TOTALWAL=`ls -1 $STANDBYLOGLOC|wc -l`
NOTAPWAL=`expr $TOTALWAL - $WALNUM`
CURRENT_XLOG=`$PSQL -h $PRIM_HOST -t -p 5432 -c "select pg_xlogfile_name(pg_current_xlog_location());"|sed 's/ *//g'`
if [ $NOTAPWAL -eq 0 ];then
echo -e ""
echo -e "+-----------------------------------------+"
echo -e "|       WARM STANDBY is in SYNC           |"
echo -e "+-----------------------------------------+"
echo -e "|Current Status On Warm Standb:           |"
echo -e "+-----------------------------------------+"
echo -e "|All Archived WAL Restored on Warm Standby|"
echo -e "+-----------------------------------------+"
echo ""
echo -e "+-------------------------------------------------+"
echo -e "|Current Status On Production:                    |"
echo -e "+-------------------------------------------------+"
echo -e "|Current_XLOG of Primary: $CURRENT_XLOG|"
echo -e "|Unarchived WAL SEGMENT: $CURRENT_XLOG |"
echo -e "+-------------------------------------------------+"
else
echo -e "+-----------------------------------------------------+"
echo -e "|WARM STANDBY IS NOT in Sync                          |"
echo -e "+-----------------------------------------------------+"
echo -e "|List of Archived WAL which has not been implemented: |"
echo -e "+-----------------------------------------------------+"
grep -A $NOTAPWAL $MINRECOVWAL /tmp/WAL.lis|grep -v $MINRECOVWAL|awk '{printf "|%s                             |\n",$1}'
echo -e "+-----------------------------------------------------+"
fi

Above script will give you the following information:
+-----------------------------------------------------------+
|Finding the Minimum Recovery: |
+-----------------------------------------------------------+
|Minimum Recovery WAL file require: 000000010000015A00000071|
+-----------------------------------------------------------+

+-----------------------------------------+
| WARM STANDBY is in SYNC |
+-----------------------------------------+
|Current Status On Warm Standb: |
+-----------------------------------------+
|All Archived WAL Restored on Warm Standby|
+-----------------------------------------+

+-------------------------------------------------+
|Current Status On Production: |
+-------------------------------------------------+
|Current_XLOG of Primary: 000000010000015A00000072|
|Unarchived WAL SEGMENT: 000000010000015A00000072 |
+-------------------------------------------------+