How can we tell how far behind the standby is?

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