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_xlog_replay_location functions on the standby, respectively.

Following is also a code made by Karl Denninger:
http://www.denninger.net/check-replication.c

Above program, check the status of Master and Slave, if Both are in sync then it will be silent, if there is lagging then it will give proper message to stdout.

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