Folks,
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.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com