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 |
+-------------------------------------------------+

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s