⚠️ Deprecated - Use https://github.com/prometheus-community/postgres_exporter⚠️

postgresql_exporter

A Prometheus exporter for some postgresql metrics.

Getting Started

You can add as many database connections as you like to the config.yml file, and run it with:

./postgresql_exporter -config=my/config.yml

Then you can add hostname:9111 to the prometheus scrapes config:

- job_name: 'postgresql'static_configs:
- targets: ['localhost:9111']

And voilá, metrics should be there and you should be able to query, graph and alert on them.

Setting up a restricted monitoring user

By default some stat views like pg_stat_statements and pg_stat_activity doesn't allow viewing queries run by other users, unless you are a database superuser. Since you probably don't want monitoring to run as a superuser, you can setup a separate monitoring user like this:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATESCHEMAmonitoring;
CREATE OR REPLACEFUNCTIONmonitoring.pgstattuple(IN relname text,
OUT table_len BIGINT,
OUT tuple_count BIGINT,
OUT tuple_len BIGINT,
OUT tuple_percent FLOAT8,
OUT dead_tuple_count BIGINT,
OUT dead_tuple_len BIGINT,
OUT dead_tuple_percent FLOAT8,
OUT free_space BIGINT,
OUT free_percent FLOAT8) AS $$
SELECT
table_len,
tuple_count,
tuple_len,
tuple_percent,
dead_tuple_count,
dead_tuple_len,
dead_tuple_percent,
free_space,
free_percent
FROMpublic.pgstattuple(relname)
$$ LANGUAGE SQL VOLATILE SECURITY DEFINER;
CREATE OR REPLACEFUNCTIONmonitoring.pgstattuple_approx(IN relname text,
OUT table_len BIGINT,
OUT scanned_percent FLOAT8,
OUT approx_tuple_count BIGINT,
OUT approx_tuple_len BIGINT,
OUT approx_tuple_percent FLOAT8,
OUT dead_tuple_count BIGINT,
OUT dead_tuple_len BIGINT,
OUT dead_tuple_percent FLOAT8,
OUT approx_free_space BIGINT,
OUT approx_free_percent FLOAT8) AS $$
SELECT
table_len,
scanned_percent,
approx_tuple_count,
approx_tuple_len,
approx_tuple_percent,
dead_tuple_count,
dead_tuple_len,
dead_tuple_percent,
approx_free_space,
approx_free_percent
FROMpublic.pgstattuple_approx(relname)
$$ LANGUAGE SQL VOLATILE SECURITY DEFINER;
CREATE ROLE monitoring WITH LOGIN PASSWORD 'mypassword' CONNECTION LIMIT5IN ROLE pg_monitor;
ALTER ROLE monitoring SET search_path = monitoring, pg_catalog, public;
GRANT CONNECT ON DATABASE {{database_name}} TO monitoring;
GRANT USAGE ON SCHEMA monitoring TO monitoring;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA monitoring TO monitoring;

Note that these statements must be run as a superuser (to create the SECURITY DEFINER function), but from here onwards you can use the monitoring user instead. The exporter will automatically use the helper methods if they exist in the monitoring schema, otherwise data will be fetched directly.

The default role pg_monitor only has in PostgreSQL 10 or later (See more details here). If you're running Postgres 9.6 or lower you need to create some other helper methods in the monitoring schema:

CREATE OR REPLACEFUNCTIONmonitoring.pg_stat_activity() RETURNS SETOF pg_stat_activity AS $$
SELECT*FROMpg_catalog.pg_stat_activity;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
CREATEVIEWmonitoring.pg_stat_activity ASSELECT*FROMmonitoring.pg_stat_activity();
CREATE OR REPLACEFUNCTIONmonitoring.pg_stat_statements() RETURNS SETOF pg_stat_statements AS $$
SELECT*FROMpublic.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
CREATEVIEWmonitoring.pg_stat_statements ASSELECT*FROMmonitoring.pg_stat_statements();
CREATE OR REPLACEFUNCTIONmonitoring.pg_stat_replication() RETURNS SETOF pg_stat_replication AS $$
SELECT*FROMpg_catalog.pg_stat_replication;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
CREATEVIEWmonitoring.pg_stat_replication ASSELECT*FROMmonitoring.pg_stat_replication();
CREATE OR REPLACEFUNCTIONmonitoring.pg_stat_progress_vacuum() RETURNS SETOF pg_stat_progress_vacuum AS $$
SELECT*FROMpg_catalog.pg_stat_progress_vacuum;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
CREATEVIEWmonitoring.pg_stat_progress_vacuum ASSELECT*FROMmonitoring.pg_stat_progress_vacuum();

Running it within Docker

docker run -p 9111 -v /path/to/my/config.yml:/config.yml caninjas/postgresql_exporter