Monitoring

From PostgreSQL wiki

Jump to: navigation, search

Contents

PostgreSQL builtin & contrib

Statistics collector

PostgreSQL collects lots of data on its own and offers it via the pg_stat(io)_ system views

contrib extensions

The following extensions offer access to Postgres internals which may be of interest or collect additional information. Most of them are shipped with Postgres (the -contrib packages may need to be installed) and can be activated via the extension interface.

pg_stat_statements

pg_stat_statements tracks all queries that are executed on the server and records average runtime per query "class" among other parameters.

pg_stat_plans

pg_stat_plans extends on pg_stat_statements and records query plans for all executed quries. This is very helpful when you're experiencing performance regressions due to inefficient query plans due to changed parameters or table sizes.

pgstattuple

pgstattuple can generate statistics for tables and indexes, showing how much space in each table & index is consumed by live tuples, deleted tuples as well as how much unused space is available in each relation.

pg_buffercache

pg_buffercache gives you introspection into Postgres' shared buffers, showing how many pages of which relations are currently held in the cache.

External projects

CLI tools

pg_view

pg_view is a Python-based tool to quickly get information about running databases and resources used by them as well as correlate running queries and why they might be slow.

Checkers

check_postgres

check_postgres is a command line tool which is designed to be run from software like Icinga, MRTG or as a standalone tool. It can monitor many aspects of the database and trigger warnings when thresholds are violated.

Interfaces & collectors

These tools either offer an interface to PostgreSQL monitoring-relevant data or can aggregate and prepare them for other systems.

pgsnmpd

pgsnmpd can run as a standalone SNMP server and implements the RFC 1697 MIB which is generic RDBMS MIB

This is useful for network management systems which are limited to SNMP protocol.

pganalyze-collector

pganalyze-collector is a tool which collects pg_stat_plans query information as well as various performance-relevant database parameters and converts them into a JSON structure for easy ingestion in other systems.

Generic monitoring solutions with plugins

Munin

PostgreSQL Plugins developed in Perl are included in the Core Munin Distribution. The following plugins are included by default: postgres_bgwriter, postgres_locks_, postgres_tuples_, postgres_cache_, postgres_querylength_, postgres_users, postgres_checkpoints, postgres_scans_, postgres_xlog, postgres_connections_, postgres_size_, postgres_connections_db, postgres_transactions_

PyMunin includes a Multigraph Munin Plugin written in Python that implements the following graphs: pg_connections, pg_diskspace, pg_blockreads, pg_xact, pg_tup_read, pg_tup_write, pg_blockreads_detail, pg_xact_commit_detail, pg_xact_rollback_detail, pg_tup_return_detail, pg_tup_fetch_detail, pg_tup_delete_detail, pg_tup_update_detail, pg_tup_insert_detail

Detailed setup instructions for common Linux platforms can be found at highperfpostgres.com

Zabbix

pg_monz is a Zabbix monitoring template for Postgres.

NewRelic

NewRelic is a commercial SaaS application monitoring solution which offers a PostgreSQL plugin maintained by EnterpriseDB.

Cacti

There has been work done on building a Postgres template for Cacti, Details can be found at the Cacti page.

Postgres-centric monitoring solutions

EnterpriseDB Postgres Enterprise Manager

Postgres Enterprise Manager is a commercial application offered by EnterpriseDB which covers many aspects of Postgres operations & monitoring in large environments.

pganalyze

pganalyze is a commercial SaaS offering which focuses on performance monitoring and automated tuning suggestions.

pgwatch

pgwatch is a PHP web application which offers interactive graphs for relevant Postgres data.

pg_statsinfo & pg_stats_reporter

pg_statsinfo is a Postgres extension that collects lots of performance-relevant information inside the Postgres server which then can be aggregated by pg_stats_reporter instances which provide a web interface to the collected data. Both are FOSS software maintained by NTT.

PGObserver

PGObserver is a Python & Java-based Postgres monitoring solution developed by Zalando. It was developed with a focus on stored procedure performance but extended well beyond that.

pgCluu

pgCluu is a Perl-based monitoring solution which uses psql and sar to collect information about Postgres servers and render comprehensive performance stats.

PoWA

PoWA is a PostgreSQL Workload Analyzer that gathers performance stats and provides real-time charts and graphs to help monitor and tune your PostgreSQL servers.

Personal tools