Re: Replication Monitoring

glynastill(at)yahoo(dot)co(dot)uk (Glyn Astill) writes:
> [posted again as it found it's way into another thread]
>
> Hi people,
>
> I intend to set up two slave servers, one using WAL shipping and one
> using Slony I.
>
> Are there any good tools, or scripts that'll help us check that both
> replication methods are working?
>
> I know theres Nagios - but what does this actually allow us to
> monitor?
>
> Also if I want to make backups from the slaves to save master
> downtime / load what are my options?

The Slony-I documentation describes how one might use MRTG to graph
the output of the "sl_status" view, which can be used to analyze, over
time, how far behind the Slony-I subscriber nodes are.

You could correspondingly set up a query against sl_status to warn of
problems.

In practice, we've often found it more useful to look at this from an
application standpoint, that is, to find some table that is always
getting updated, such as an ongoing transaction log, and raise alerts
if the age of the last entry grows higher than expected.

Unfortunately, that may not work for everyone - we have, in our online
systems, some mandated monitoring that introduces transactions every
5-10 minutes, whether anything "real" is going on or not. If you
don't have that, and there is a quiet period when the system sees zero
activity for 8 hours, alerts are likely to go off :-(.

vis-a-vis grabbing backups from slaves...

You can NOT grab schema dumps there, because they're hacked up
(pg_catalog gets modified a fair bit), but you could grab the schema
from the master node, and do just a dump of data from a subscriber
node.

I'd want to test the reload process before signing off on any promises
that it's working, but it should all be fine...
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/lisp.html
"NT 5.0 is the last nail in the Unix coffin. Interestingly, Unix isn't
in the coffin... It's wondering what the heck is sealing itself into a
wooden box 6 feet underground..." -- Jason McMullan