..
  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