commit c3afc6121416c31b23f7b1cd8f913c9ad13a43c7
parent e71b05ea303d8024e2d52f1e5662dbace73abca0
Author: Javier Sepulveda <javier.sepulveda@uv.es>
Date: Fri, 5 Jul 2024 14:10:59 +0200
Prometheus postgres-exporter tutorial
Diffstat:
4 files changed, 179 insertions(+), 2 deletions(-)
diff --git a/system-administration/index.rst b/system-administration/index.rst
@@ -28,3 +28,4 @@ System Administration tutorials
prometheus
nginx-prometheus-exporter
prometheus-node-exporter
+ prometheus-postgres-exporter
diff --git a/system-administration/nginx-prometheus-exporter.rst b/system-administration/nginx-prometheus-exporter.rst
@@ -143,7 +143,7 @@ Restart everything
Create a new dashboard in Grafana
=================================
-You can now go to our `grafana.taler.net dasboards <a href="https://grafana.taler.net/dashboards">`_ and easily
+You can now go to the `Grafana dashboards <a href="https://grafana.taler.net/dashboards">`_ and easily
add the new dashboard for the Nginx Exporter program. Please make sure you choose the right Prometheus data source.
* Dashboard Id: 11199
diff --git a/system-administration/prometheus-node-exporter.rst b/system-administration/prometheus-node-exporter.rst
@@ -102,7 +102,7 @@ http://ip:9100/
Grafana control panel (GUI)
===========================
-You can now to go `our Grafana dashboards <a href="https://grafana.taler.net/dashboards">`_ and easily
+You can now go to the `Grafana dashboards <a href="https://grafana.taler.net/dashboards">`_ and easily
add a new dashboard for the Node Exporter program. Please make sure you choose the right Prometheus data source.
* Dashboard Id: 1860
diff --git a/system-administration/prometheus-postgres-exporter.rst b/system-administration/prometheus-postgres-exporter.rst
@@ -0,0 +1,176 @@
+..
+ This file is part of GNU TALER.
+ Copyright (C) 2014-2023 Taler Systems SA
+
+ TALER is free software; you can redistribute it and/or modify it under the
+ terms of the GNU Affero General Public License as published by the Free Software
+ Foundation; either version 2.1, or (at your option) any later version.
+
+ TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+ WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+ A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details.
+
+ You should have received a copy of the GNU Affero General Public License along with
+ TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+
+ @author Javier Sepulveda
+
+
+Prometheus postgres-exporter
+############################
+
+.. contents:: Table of Contents
+ :depth: 1
+ :local:
+
+
+Create a system user
+====================
+
+.. code-block:: console
+
+ # useradd -s /sbin/nologin --system postgres_exporter
+ # groupadd --system postgres_exporter
+
+Download Prometheus node-exporter
+=================================
+
+* Download
+* Extract
+* Copy to /usr/local/bin
+* Set ownership and permissions
+
+.. code-block:: console
+
+ # 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
+
+Create environment variable
+===========================
+
+.. code-block:: console
+
+ # 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"
+
+
+Create postgres-exporter.sql file
+=================================
+
+.. code-block:: console
+
+ # cd /opt/postgres_exporter
+ # touch postgres-exporter.sql
+
+.. code-block::
+
+ -- 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;
+
+
+Login in Postgres
+=================
+
+Login into Postgres and execute the previous file, as the postgres user.
+
+.. code-block:: console
+
+ # su -c "psql" postgres
+ # \i postgres-exporter.sql
+
+
+Systemd postgres-exporter service file
+======================================
+
+.. code-block:: systemd
+
+ # 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
+
+
+
+Edit Prometheus configuration file
+===================================
+
+* Add this new job to the /etc/prometheus/prometheus.yml configuration file.
+
+.. code-block:: yaml
+
+ - job_name: 'postgres_exporter'
+ static_configs:
+ - targets: ['localhost:9187']
+
+
+Refresh systemd
+===============
+
+.. code-block:: console
+
+ # 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/
+
+Grafana control panel (GUI)
+===========================
+
+You can now go to `Grafana dashboards <a href="https://grafana.taler.net/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
+
+
