Re: Calculating Replication Lag - units

On Tue, Jun 26, 2012 at 6:21 AM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:
> On Mon, Jun 25, 2012 at 02:17:22PM -0700, Steve Crawford wrote:
> - On 06/25/2012 01:17 PM, David Kerr wrote:
> - >Howdy,
> - >
> - >When calculating Replication lag, I know that we have to compare the
> - >pg_current_xlog_location
> - >to pg_last_xlog_receive_location, etc. but what I'm trying to figure out
> - >is what are
> - >the units that I'm left with after the calculation.
> - >
> - >(i.e., does the xlog_location imply some time value?)
> - >
> - >Here's the output of the (slightly modified script)
> - >Master: 5003964876715
> - >Receive: 5003964876715
> - >Replay: 5003964765203
> - >
> - >receive.value 0
> - >apply.value 111512
> - >
> - >111512 isn't inherently useful to me on its own.
> - >
> - >Any tips?
> - >
> - How about now()-pg_last_xact_replay_timestamp() (however this can be a
> - large number if there have not been any recent transactions on the
> - master). I suppose you could do something like:
> -
> - case when pg_last_xlog_receive_location() =
> - pg_last_xlog_replay_location() then '0 seconds'::interval
> - else now()-pg_last_xact_replay_timestamp() end as log_delay;
>
> i don't know for sure that 111512 is a time value.. that's kind of
> what i'm wondering. If i knew that it was like miliseconds or something
> that would be helpful.

On the hot standby:

SELECT now()-pg_last_xact_replay_timestamp() AS lag;

This gives you the lag time as a PostgreSQL interval.

(It also might give you a value if you run it on a database that is
not a hot standby if it started in recovery mode).

It seems difficult or impossible to calculate this on the master.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/