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
<<attachment: karl.vcf>>
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
