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
