14.9. Prometheus postgres-exporter¶

14.9.1. Create a system user ¶

# useradd -s /sbin/nologin --system postgres_exporter
# groupadd --system postgres_exporter

14.9.2. Download Prometheus node-exporter ¶

Download
Extract
Copy to /usr/local/bin
Set ownership and permissions

# cd /tmp
# wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
# tar -xzvf https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
# cd postgres_exporter-0.12.0.linux-amd64
# cp postgres_exporter /usr/local/bin
# chown postgres_exporter:postgres_exporter /usr/local/bin/postgres_exporter

14.9.3. Create environment variable ¶

# mkdir -p /opt/postgres_exporter
# cd /opt/postgres_exporter
# touch postgres_exporter.env
# Paste next content:
DATA_SOURCE_NAME="postgresql://postgres_exporter:PASSWORD@localhost:5432/?sslmode=disable"

14.9.4. Create postgres-exporter.sql file ¶

# cd /opt/postgres_exporter
# touch postgres-exporter.sql

-- To use IF statements, hence to be able to check if the user exists before
-- attempting creation, we need to switch to procedural SQL (PL/pgSQL)
-- instead of standard SQL.
-- More: https://www.postgresql.org/docs/9.3/plpgsql-overview.html
-- To preserve compatibility with <9.0, DO blocks are not used; instead,
-- a function is created and dropped.
CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
BEGIN
  IF NOT EXISTS (
          SELECT                       -- SELECT list can stay empty for this
          FROM   pg_catalog.pg_user
          WHERE  usename = 'postgres_exporter') THEN
    CREATE USER postgres_exporter;
  END IF;
END;
$$ language plpgsql;

SELECT __tmp_create_user();
DROP FUNCTION __tmp_create_user();

ALTER USER postgres_exporter WITH PASSWORD 'password';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;

-- If deploying as non-superuser (for example in AWS RDS), uncomment the GRANT
-- line below and replace <MASTER_USER> with your root user.
-- GRANT postgres_exporter TO <MASTER_USER>;

GRANT CONNECT ON DATABASE postgres TO postgres_exporter;

14.9.6. Systemd postgres-exporter service file ¶

# Path: /etc/systemd/system/postgres-exporter.service

[Unit]
Description=Prometheus exporter for Postgresql
Wants=network-online.target
After=network-online.target

[Service]
User=postgres_exporter
Group=postgres_exporter
WorkingDirectory=/opt/postgres_exporter
EnvironmentFile=/opt/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=:9187 --web.telemetry-path=/metrics
Restart=always

[Install]
WantedBy=multi-user.target

14.9.7. Edit Prometheus configuration file ¶

Add this new job to the /etc/prometheus/prometheus.yml configuration file.

- job_name: 'postgres_exporter'
  static_configs:
  - targets: ['localhost:9187']

14.9.8. Refresh systemd ¶

# systemctl daemon-reload
# systemctl enable --now postgres_exporter.service
# systemctl status postgres_exporter.service
# systemctl restart prometheus

Check if new postgres-exporter service, is up and running properly: http://ip:9187/

14.9.9. Grafana control panel (GUI)¶

You can now go to Grafana dashboards and easily add a new dashboard for the Postgres Exporter program. Please make sure you choose the right Prometheus data source.

Dashboard Id: 9628
Dashboard URL: https://grafana.com/grafana/dashboards/9628-postgresql-database/

More information: https://github.com/prometheus-community/postgres_exporter

14.8. Prometheus node-exporter

14.10. Grafana Loki

GNU Taler

14.9. Prometheus postgres-exporter¶