Page 14 of 14

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

EnterpriseDB’s Postgres Plus Advanced Server

EnterpriseDB’s Postgres Plus Advanced Server. It is really a nice product which has been launched by company.

I have worked on it and found suitable and replacement of the Oracle.

With Learning of Postgres Plus Advanced Server, people also learn about the PostgreSQL, world’s most advanced Open source database.

It has almost all feature, which is require for Oracle Developers.

Here is a link, which talks about PostgreSQL and Postgres Plus
http://www.enterprisedb.com/products/postgres_plus/overview.do

And following is a link, which has description of Oracle Compatibility provided by EnterpriseDB in Postgres Plus Advanced Server:
http://www.enterprisedb.com/docs/en/8.3R2/oracompat/Table%20of%20Contents.htm