Monday, October 4, 2010

PG9.0:: Monitoring Hot Standby

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.

No comments:

Post a Comment