Re: Enhancement Request

Rob,

* Rob Brucks (rob(dot)brucks(at)rackspace(dot)com) wrote:
> I'd like to propose two enhancements to the PostgreSQL code, but I'm not sure if this is the correct mailing list. So if it's not then please let me know where I need to post this.

This is the correct place. I don't know why people are suggesting third
party sites, but the correct place is -general, as you've done, which is
fantastic.

> These are monitoring-centric enhancement requests since I'm trying to implement accurate monitoring in a secure fashion.

I've been working on exactly this problem and 9.6 will (finally) have
the start of work to improve PostgreSQL in this area. Your thoughts and
use-cases are exactly what we need to continue that effort and get to a
point where monitoring solutions can depend on PostgreSQL to provide the
features, capabilities, and information which they need, without
requiring the monitoring user to be a superuser.

> * General monitoring:
> We have a need for a "monitoring" role in PostgreSQL that has read-only access to any "pg_stat" view. As of 9.4, only a super-user can read all columns of "pg_stat_activity", "pg_stat_replication", and "pg_stat_archiver" (there may be other restricted views as well). These views provide critical insight on how well the cluster is operating and what is going on.

That was proposed and was called 'pg_monitor'. Unfortunately, through a
lack of support and questions about such a role possibly being "too
broad", it ended up not being included for 9.6. I'd very much like to
work through those issues and find a solution for post-9.6 (note that we
are past the feature freeze point for 9.6, so any further changes will
be for later versions).

> * Streaming Replication Monitoring:
> Make the "pg_stat_replication" view more persistent (maybe keep the rows for 24 hours or have a registration process?).

I believe this is improved when working with replication slots in recent
versions.

> If anyone can provide insight on how I could accomplish these in a simple manner by other means then I'm all ears!

Please continue to engage with the PostgreSQL community on these issues.
I agree that these are critical features which we really need to have
and will continue to work on them, but support from users, particularly
with detailed real-world use-caes, goes a very long way.

Thanks!

Stephen