Re: Streaming Recovery - Automated Monitoring

On 10/2/2010 11:40 PM, Rajesh Kumar Mallah wrote:

I hope u checked point #11  http://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use

• 11. You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using pg_current_xlog_location on the primary and the pg_last_xlog_receive_location/pg_last_xlog_replay_location on the standby, respectively.

$ psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host)pg_current_xlog_location 
--------------------------0/2000000
(1 row)

$ psql -c "select pg_last_xlog_receive_location()" -h192.168.0.20 (standby host)pg_last_xlog_receive_location 
-------------------------------0/2000000
(1 row)

$ psql -c "select pg_last_xlog_replay_location()" -h192.168.0.20 (standby host)pg_last_xlog_replay_location 
------------------------------0/2000000
(1 row)

Regds
Rajesh Kumar Mallah.

Yes, I did.

Now how do I get an arithmetic difference between the two?  There will (usually) be a small difference between the master and slave on a busy system - what I want to do is query both and if the difference in their locations is greater than some defined size, start raising hell (e.g. sending SMS to people, etc)

I can SEE the difference, but I don't see a way to COMPUTE a difference, and there does not appear to be a function that will accept the log file location as an argument for conversion - the one documented for offsets (which might otherwise work) does not work on the slave as I noted.

With Slony there was a set of system tables that would tell me how many unapplied changes were in the queue.  From this I could determine health - if the number was more than some reasonably-small amount, something was broken and alarms were to be raised.

I'm looking for a way to implement the same sort of functionality here.

ticker=# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location
------------------------------
 37A/327D1888
(1 row)

ticker=# select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 37A/3280DCB8
(1 row)

How do I get an arithmetic difference between these two programmatically, and will such always be monoatomically increasing (that is, will they ever roll over, thereby giving me a potential NEGATIVE difference?)

The offset function doesn't work on the slave, but that probably doesn't help me anyway since it appears to be file-relative (that is, if the prefix is different its useless anyway.)

If there is no internal Postgres functionality that can do this, then I need to know the computational rules for how to get an absolute offset between two different values returned by these functions.

-- Karl