Re: How can we tell how far behind the standby is?

On Fri, Nov 5, 2010 at 2:46 PM, Josh Berkus <josh@agliodbs.com> wrote:
> I'm continuing in my efforts now to document how to deploy and manage
> replication on our wiki.  One of the things a DBA needs to do is to use
> pg_current_xlog_location() (and related functions) to check how far
> behind the master the standby is.
>
> However, there's some serious problems with that:
>
> (1) comparing these numbers is quite mathematically complex -- and, for
> that matter, undocumented.
>
> (2) pg_rotate_xlog and/or archive_timeout will create a "gap" in the
> xlog positions, quite a large one if it happens near the beginning of a
> file.  There is no way for any monitoring on the standby to tell the
> difference between a gap created by forced rotation as opposed to being
> most of a file behind, until the next record shows up.  Hello, nagios
> false alerts!
>
> (3) There is no easy way to relate a difference in log positions to an
> amount of time.
>
> I'll work on some tools to make this a bit more palatable, but I
> disagree with earlier assertions that we have the replication monitoring
> "done".  There's still a *lot* of work to do.

I've heard the same complaint, and I agree with your concerns.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company