Re: Hot Standby Lag Calculation

>From: pgsql-general-owner(at)postgresql(dot)org On Behalf Of Sam Nelson
>Sent: Wednesday, August 03, 2011 11:03 AM
>To: pgsql-general
>Subject: [GENERAL] Hot Standby Lag Calculation

>Hi, List,

>We're trying to calculate the amount of time that a Hot Standby slave is lagging behind its master, and our results look wrong (average of 7 seconds, with some over 1 minute), so we were thinking that we're probably calculating it wrong.

>We're currently just using the timestamps from ls (mtimes, I believe?) against the files in the pg_xlog directory and comparing the timestamp of the files on the master to the same files on the slave. Is this incorrect? If so, what's the standard way of calculating the lag of a hot standby?
>---
>===========================
>Samuel Nelson
>Consistent State
>www.consistentstate.com
>303-955-0509
>===========================

Haven't seen anyone else reply so....

I think the standard way people are using right now is to calculate the number of blocks behind they are. Things could be tricky when trying to have nagios alerts on slow velocity databases as you can get some large gaps in the numbers which can make things look more laggy than they really are. (well not tricky, I just scripted the check to sleep more and see if its catching up still and not stalled, with a reasonable wan link and streaming replication this works, might not apply elsewhere).

http://archives.postgresql.org/pgsql-hackers/2010-11/msg00198.php
&
-> http://archives.postgresql.org/pgsql-hackers/2010-11/msg00252.php

I didn't see this the last time I was looking but: https://github.com/psoo/pg_standby_status/blob/master/pg_standby_status.pl

(I have never used this perl so caveat emptor)

-Mark