Suggestion to Monitoring Tool

On Wed, May 27, 2020 at 10:15:49PM +0530, postgann2020 s wrote:

Environment Details:
OS: RHEL 7.2
Postgres: 9.5.15
Master-Slave with Streaming replication

We are planning to implement the monitoring tool for our environment.

Could someone please suggest the Monitoring Tool based on your experience.

I suggest you read up on the fine manual first. It covers a
lot of ground already.

And to stick to one major project at a time.

Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

You can try nagios

Sent from my iPhone

Hi
I also suggest Nagios (for immediate monitoring) layered with OPM (longer term and historical analysis), followed by PGBadger for stats snapshots (if your logging format policy permits), and also PG_activity for the Operations team (if security policies permit), the latter is great when handling locking amongst other things.

Cheers

Peter Goodwin

Hi !

Le mer. 27 mai, vers 22:15, postgann2020 s exprimait :

Thanks for your support.

Environment Details:
OS: RHEL 7.2
Postgres: 9.5.15
Master-Slave with Streaming replication

We are planning to implement the monitoring tool for our environment.

Could someone please suggest the Monitoring Tool based on your experience.

Last year, i setup prometheus with :
- postgres exporter for generic metrics
- some custom queries via postgres exporter to compute replication lag
and few other things
- script exporter to explore logs and other things
- node exporter for disk filling.
- alertmanager to send slack notifications
- grafana dashboards for a nice view
- kibana / sentry manage logs and used as datasource for prometheus.

Regards,
--
Stéphane KANSCHINE - https://www.hexack.fr./ - https://www.nuajik.io./
＠ stephane@hexack.fr
 +33 6 64 31 72 52

Give pgwatch2 a try:
https://pgwatch.com/

On Wed, May 27, 2020 at 11:46 AM postgann2020 s <postgann2020@gmail.com>
wrote:

Hi Team,

Thanks for your support.

Environment Details:
OS: RHEL 7.2
Postgres: 9.5.15
Master-Slave with Streaming replication

We are planning to implement the monitoring tool for our environment.

Could someone please suggest the Monitoring Tool based on your experience.

We are looking to cover the below areas.

1. Monitoring metrics and alerting.
2. Monitoring events and alerting.
3. Consolidate all the PROD DB logs and provide insights on log data.
4. logging explain plan and insights on explain plans. (Something like
store explain plan and compare plans and send alerts on deviations)
5. Logging audit data and insights from audit data.

Thanks & Regards,
Postgann.

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

On Wed, May 27, 2020 at 12:46 PM postgann2020 s <postgann2020@gmail.com>
wrote:

Hi Team,

Thanks for your support.

Environment Details:
OS: RHEL 7.2
Postgres: 9.5.15
Master-Slave with Streaming replication

We are planning to implement the monitoring tool for our environment.

Could someone please suggest the Monitoring Tool based on your experience.

We are looking to cover the below areas.

1. Monitoring metrics and alerting.
2. Monitoring events and alerting.
3. Consolidate all the PROD DB logs and provide insights on log data.
4. logging explain plan and insights on explain plans. (Something like
store explain plan and compare plans and send alerts on deviations)
5. Logging audit data and insights from audit data.

Thanks & Regards,
Postgann.

For items 1 & 2, we've been working on a monitoring solution for PostgreSQL
using Prometheus & Grafana called pgMonitor

https://github.com/CrunchyData/pgmonitor

We went this approach so it's not just confined to monitoring only
PostgreSQL. You can monitor any other part of your infrastructure as well
with the same programs.

For log monitoring (item 3), I would recommend pgBadger.
https://github.com/darold/pgbadger

To get explain plans to be part of your logs (item 4), you can look at the
auto_explain contrib module -
https://www.postgresql.org/docs/current/auto-explain.html. I'm not aware of
anything that would alert you on deviation from plans, and that would be
quite tricky to implement. You can monitor for long running queries with
pgMonitor and pgBadger can provide you with further insights on which
queries are running long.

For auditing, I would suggest the pg_audit extension -
https://github.com/pgaudit/pgaudit

So, there is not a single monitoring solution that provides all the items
you're looking for, but there are solutions to each item available.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com