Monitoring and collecting data from PostgreSQL

PostgreSQL is a powerful, open source object-relational database system. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

More information on: https://www.postgresql.org/

How it works

This plugin analyzes the performance of your PostgreSQL database server.

We gather global statistics (eg. number of connections, open files, …) by using the pg_stat_statements PostgreSQL extension. This extension is not installed by default.

Query statistics such as queries per second, latencies and errors are retrieved from the slow query log. By default, PostgreSQL does not track slow queries, the PostgreSQL configuration has to be changed to enable this.

The minimal supported version of PostgreSQL is 9.0.

Installation

The plugin needs to be installed together with a CoScale agent, instructions on how to install the CoScale agent can be found here. If you want to monitor PostgreSQL inside Docker containers using CoScale, check out the instructions here.

Configuration

Enable pg_stat_statements

Global stats are not enabled in PostgreSQL by default. Enabled PostgreSQL pg_stat_statements using the following steps.

Install the postgresql-contrib package:

sudo apt-get install postgresql-contrib

Add the following lines to /etc/postgresql/<version>/main/postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all

PostgreSQL must be restarted for this to take effect.

Enable the extension in psql for your database. That means, if you wish to gather statistics about the mydb database, connect to “mydb” database and execute:

CREATE EXTENSION pg_stat_statements;

Query performance

The slow query log can be used to log all queries executed on the PostgreSQL machine by applying the following configuration in /etc/postgresql/<version>/main/postgresql.conf.

log_destination = 'stderr'
logging_collector = on
log_file_mode = 0600
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_min_duration_statement = -1
log_duration = on
log_line_prefix = '%m %u@%d %p %r %e '
log_statement = 'all'

The line prefix must end in a space. Minimum requirements are the database %d and either the process id %p or the session id %c. The logger will not work without this.

Options:

  1. The log file and directory can be changed, but these settings should be reflected in the plugin configuration below.
  2. If you do not wish to log all queries, but chose to log only slow queries, the log_min_duration should be set to the minimum duration (in milliseconds).
  3. If you are already using csv logging (log_destination), set the ‘Log Statement’ below to ‘csv’ instead of ‘all’.

PostgreSQL must be restarted for this to take effect.

Log rotation has to be configured manually. More information about log rotation can be found on the knowledge base.

Active checks

This plugin can be configured to perform a SQL query on your PostgreSQL. This active monitoring allows us to calculate the uptime of the service and the response time of the query.

A valid SQL query (eg. select count(*) from my_table;) should be provided.

Metrics

PostgreSQL

Name Unit Dimensions
Number of PostgreSQL processes procs  
PostgreSQL active check latency s  
PostgreSQL active check uptime %  
PostgreSQL bytes read b  
PostgreSQL bytes written b  
PostgreSQL connections conn  
PostgreSQL CPU Usage s  
PostgreSQL queries queries  
PostgreSQL uptime s  
Total PostgreSQL query time s  

Events

Name Description Attributes
POSTGRESQL service state watcher State watcher status
POSTGRES Service state watcher State watcher status