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