Re: Calculating Replication Lag - units

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;

But I'm wrapping my head around some replication issues myself so others
may have better ideas or corrections.

Cheers,
Steve