postgres_exporter

Introduction

postgres_exporter is a Prometheus exporter written in Go aiming to provide an easy way to export different metrics from a PostgreSQL database. While postgres_exporter can be used to collect different metrics about the health of a PostgreSQL cluster, the project does not (nor does it intend to) provide the queries to collect such health data.

How to build

Having installed a reasonably modern version of Go, run: go get github.com/trustly/postgres_exporter. This should produce a binary under $GOPATH/bin.

Exporter configuration

postgres_exporter does not currently support any command-line options. To configure how the database connection is established, use the PG* environment variables supported by pq.

The exporter listens to connections from Prometheus on TCP port 9187 on all interfaces. This is currently not configurable, but the port has been allocated, so there should be no reason to change it in most cases.

The exporter itself only exposes a handful of generally useful metrics. The real power of the exporter is in the user's capability to configure the list of functions metrics are collected from (metric functions) on the fly.

Metric functions

A metric function is a function with the following signature:

    CREATE OR REPLACE FUNCTION metrics.funcname()
    RETURNS TABLE (metric text, value float8, labels hstore)

All metric functions should reside in the metrics schema and should be callable by the user the exporter runs as. Conforming to the principle of least privilege, this user should not have any permissions other than to call these metric functions; therefore the metric functions are nearly always SECURITY DEFINER functions.

To add a new metric function, create it in the database first and then INSERT a row into the metrics.MetricFunctions table. This table has the following columns:

• MetricFunctionID (serial): Automatically generated; always use the default value.
• Datestamp (timestamptz): Automatically generated; always use the default value.
• FunctionName (text): The name of the function to call. The function name will be quoted when called by the exporter, so case matters here.
• Span (enum): Reserved. Must be 'GLOBAL' for now.
• Endpoint (text): The name of the endpoint which should expose the metrics returned by this function. NULL means the default endpoint. See below for more information
• ScrapeCacheLenth (interval): The amount of time the exporter should cache the data returned by this function. For example, setting this to '1 second' would mean that the function is not called more than once per second, even if Prometheus scrapes are happening more frequently. The intended use case is reducing database load in the presence of multiple scrapers, but so far this feature has not proved to be very useful in practice.

Endpoints

In order to support collecting different types of data from a single server, multiple endpoints can be configured. For example, you might want to poll the number of backend processes waiting on a lock every five seconds to get an accurate picture of what's going on in the live system -- but that could be a massive overkill for some other metrics, such as the number of registered users. Collecting these metrics in separate functions and setting them up on different endpoints from one another can save significant amounts of disk space and reduce the performance hit of monitoring.

The default endpoint (in line with Prometheus practices you might be familiar with) is /metrics, which always exists. This endpoint can be selected by setting the metrics.MetricFunctions.Endpoint column to NULL. The general statistics (such as the time at which the exporter was started) are only exposed in the default endpoint, so most setups want to always scrape it.