Hi all,
I wondered if any experts can help me out?
I currently monitor Postgresql's replication status by looking at two metrics.
First I check to see if the current slave xlog replay is equal to the master
-- if so, it's up to date.
If it's not equal, then I look at pg_last_xact_replay_timestamp().
I can compare this with now() to get a duration, which I believe should
indicate how far behind the slave is tracking.
Most of the time this works quite well -- the slave might fall behind by some
seconds under heavy load, but that's fine.
However, occasionally this replay timestamp will report times many hours or
days behind! This goes on for a few minutes, then suddenly recovers.
My best guess for what is going on is:
- There has been no activity for hours or days, and so the oldest replayed
transaction on the slave is genuinely quite old.
- Something has happened on the master that causes its
pg_current_xlog_location() to be updated, but not in a way that is sent to the
slave until the end of a long-running transaction.
Could anyone suggest how to do this in a manner that avoids the problem?
It's annoying because when it happens, because for 5-10 minutes monitoring
alerts get fired off about catastrophic amounts of lag on the read-only slave!
Cheers
Toby