Database Technologies

As discussed in previous Blogs about Hot Standby in following links: 1. Monitoring Hot Standby 2. HotStandby in PostgreSQL 9.0. I came up with the following Monitoring Script for Hot Standby. #!/bin/bash # Filename: monitor_hotstandby # Usage is $0 -m master:port -s slave:port -b "PostgreSQL Bin Directory" # Author: Vibhor Kumar # Date: Jan 4th 2011 # E-mail: vibhor.aim@gmail.com while getopts "m:s:b:" opt;do case $opt in m) h1=`echo $OPTARG|cut -d":" -f1` p1=`echo $OPTARG|cut -d":" -f2`;; s) h2=`echo $OPTARG|cut -d":" -f1` p2=`echo $OPTARG|cut -d":" -f2`;; b) PGHOME="$OPTARG" esac done PSQL=$PGHOME/psql function usage() { if [ -z $h1 ];then echo "USAGE: " echo "$0 -m master:port -s slave:port -b pg bin directory" exit 1 fi if [ -z $h2 ];then echo "USAGE: " echo "$0 -m master:port -s slave:port -b pg bin directory" exit 1

Now PG9.0 is in Market with new feature of Hot Standby and Streaming Replication. So, I have started to explore the way of monitoring the Hot Standby. I was in process of writing my own code for Monitoring the Hot Standby. For this purpose I have written a shell script to find the way of calculating lag. In pgpool-II, Developer has used following formula to calculate the lagging: lsn = xlogid * 16 * 1024 * 1024 * 255 + xrecoff; Following is an explanation of meaning of xlogid and xrecoff: postgres=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 0/13000078 (1 row) 0: is xlogid and xrecoff is 13000078 With this, Concept of implementation of finding the lagging is to calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. These can be find using pg_current_xlog_location function on the primary and the pg_last_xlog_receive_location/pg_last_xl