Re: Streaming replication status

Greg Smith wrote:
> Stefan Kaltenbrunner wrote:
>> Greg Smith wrote:
>>>
>>> The other popular request that keeps popping up here is providing an
>>> easy way to see how backlogged the archive_command is, to make it
>>> easier to monitor for out of disk errors that might prove
>>> catastrophic to replication.
>>
>> I tend to disagree - in any reasonable production setup basic stulff
>> like disk space usage is monitored by non-application specific matters.
>> While monitoring backlog might be interesting for other reasons,
>> citing disk space usage/exhaustions seems just wrong.
>
> I was just mentioning that one use of the data, but there are others.
> Let's say that your archive_command works by copying things over to a
> NFS mount, and the mount goes down. It could be a long time before you
> noticed this via disk space monitoring. But if you were monitoring "how
> long has it been since the last time pg_last_archived_xlogfile()
> changed?", this would jump right out at you.

well from an syadmin perspective you have to monitor the NFS mount
anyway - so why do you need the database to do too(and not in a sane way
because there is no way the database can even figure out what the real
problem is and if there is one)?

>
> Another popular question is "how far behind real-time is the archiver
> process?" You can do this right now by duplicating the same xlog file
> name scanning and sorting that the archiver does in your own code,
> looking for .ready files. It would be simpler if you could call
> pg_last_archived_xlogfile() and then just grab that file's timestamp.

well that one seems a more reasonable reasoning to me however I'm not so
sure that the proposed implementation feels right - though can't come up
with a better suggestion for now.

>
> I think it's also important to consider the fact that diagnostic
> internals exposed via the database are far more useful to some people
> than things you have to setup outside of it. You talk about reasonable
> configurations above, but some production setups are not so reasonable.
> In many of the more secure environments I've worked in (finance,
> defense), there is *no* access to the database server beyond what comes
> out of port 5432 without getting a whole separate team of people
> involved. If the DBA can write a simple monitoring program themselves
> that presents data via the one port that is exposed, that makes life
> easier for them. This same issue pops up sometimes when we consider the
> shared hosting case too, where the user may not have the option of
> running a full-fledged monitoring script.

well again I consider stuff like "available diskspace" or "NFS mount
available" completely in the realm of the OS level management. The
database side should focus on the stuff that concerns the internal state
and operation of the database app itself.
If you continue your line of thought you will have to add all kind of
stuff to the database, like CPU usage tracking, getting information
about running processes, storage health.
As soon as you are done you have reimplemented nagios-plugins over SQL
on port 5432 instead of NRPE(or SNMP or whatnot).
Again I fully understand and know that there are environments where the
DBA does not have OS level (be it root or no shell at all) access has to
the OS but even if you had that "archiving is hanging" function you
would still have to go back to that "completely different group" and
have them diagnose again.
So my point is - that even if you have disparate groups of people being
responsible for different parts of a system solution you can't really
work around incompetency(or slownest or whatever) of the group
responsible for the lower layer by adding partial and inexact
functionality at the upper part that can only guess what the real issue is.

Stefan