PostgreSQL Monitoring Module

[cavaliercoder]

Hi team,

I'm pleased to release the first version of my PostgreSQL agent module and template, 'libzbxpgsql'.

Download: https://sourceforge.net/projects/libzbxpgsl/
Documentation: http://cavaliercoder.github.io/libzbxpgsql/
Sources: https://github.com/cavaliercoder/libzbxpgsql

Your feedback and experience on this project is highly appreciated, especially at this early stage of the project.

Features from the documentation (some of the language is a little ambitious I apologise):

• Powerful discovery of Table spaces, Databases, Namespaces, Tables and Indexes
• Exhaustive coverage of PostgreSQL Statistics Collector views
• Execute custom queries specific to your applications
• Significant performance benefit over UserParameter scripts
• No UserParameter declarations required
• No external dependencies (such as PostgreSQL client tools)
• Simple installation or bundling with your agent packages
• Detailed documentation
• Comprehensive sample Template included

[Colttt]

ohh thats looks nice.. i will try it..

[cavaliercoder]

I've just released v0.1.3 which includes packages for Debian and RHEL based OSes and allows for discovery and monitoring of changes to PostgreSQL configuration.

[Colttt]

Hello,

when i look to my database-log and i see a lot of errors like:

2015-04-14 06:34:51 CEST STATEMENT:  SELECT t.oid, t.spcname, a.rolname from pg_tablespace t JOIN pg_authid a ON a.oid = t.spcowner
2015-04-14 06:40:51 CEST ERROR:  permission denied for relation pg_authid

why you need access to pg_authid?
and how can i grant select only privileges to that?

on postgres 9.3 i dont get values from pg.setting = "ZBX_NOTSUPPORTED"

on Postgres 8.4 i don't get items like psql.db_deadlocks, psql.db_temp_bytes, psql.idle_tx_connections etc.. "Received value [] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]"

[cavaliercoder]

Colttt, Thanks for the feedback.

For all future issues, please use the issue tracker on GitHub.

pg_authid is used to get the friendly name of the owner of a tablespace (and other items) for discovery. I think we need a graceful fallback if the connected role does not have read permissions.

As per https://github.com/cavaliercoder/libzbxpgsql/pull/1, I might be changing from pg_authid to pg_roles soon but the same issue will probably persist.

pg.settings is only supported since module v0.1.3. Which version are you using?

I'll take a look at your issues for v8.4 this week

[Colttt]

ok tanks..

i will try the newer version next week..

kann you give me an example query fo the user foo to get all the recommended rights to the database? for security reasons only select-rights would be usefull..

[jackie]

I'm testing libzbxpgsql right now. I noticed there aren't any triggers setup in the template that is provided.

Does anyone have any guidance on what triggers they have setup when monitoring postgres? Would prefer not to reinvent the wheel if I don't need to.

Thanks for any info.

[cavaliercoder]

I semi-deliberately haven't defined any triggers because healthy conditions for a PostgreSQL instance are strongly influenced by capacity, configuration and workload characterisation. Perhaps I should still offer some sort of starting point though?

There's a few things I always trigger on in production:

• PostgreSQL connectivity and changes to uptime
• Backend connection exhaustion
• Excessively long queries (be sure to ignore vacuums)
• Aim to have checkpoints being written 90% of the time
• Make sure all buffers have sufficient capacity
• Make sure deadlocks and temp file usage remains low
• Ratio of cache hits to disk hits
• Ratio of index hits to scans for critical indices
• Unexpected, explosive growth in tables or databases
• Unexpected, explosive growth in request rates

[alyssenko]

help please

Hi guys- I'm attempting to implement this module with my zabbix 2.4.6 server- here is a gist that outlines my config:

https://gist.github.com/alyssenko/b3e8b8686dbe7e4a3399ea0eaa8c62dd

All I'm seeing in my database log are entries that look like this:

UTC LOG: could not receive data from client: Connection reset by peer

which I'm pretty sure are coming from zabbix- I'm not sure what I'm doing wrong here as this is the first time attempting to use a 3rd party zaabbix module- any help or insight would be much appreciated- thank you.