Thread: Hot Standby Lag Calculation

>From: pgsql-general-owner@postgresql.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
lookwrong (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
comparingthe timestamp of the files on the master to the same files on the slave.  Is this incorrect?  If so, what's
thestandard 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
betricky when trying to have nagios alerts on slow velocity databases as you can get some large gaps in the numbers
whichcan make things look more laggy than they really are. (well not tricky, I just scripted the check to sleep more
andsee if its catching up still and not stalled, with a reasonable wan link and streaming replication this works, might
notapply 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