prometheus-postgres-exporter.rst (5194B)
1 .. 2 This file is part of GNU TALER. 3 Copyright (C) 2014-2023 Taler Systems SA 4 5 TALER is free software; you can redistribute it and/or modify it under the 6 terms of the GNU Affero General Public License as published by the Free Software 7 Foundation; either version 2.1, or (at your option) any later version. 8 9 TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. 12 13 You should have received a copy of the GNU Affero General Public License along with 14 TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 16 @author Javier Sepulveda 17 18 19 Prometheus postgres-exporter 20 ############################ 21 22 .. contents:: Table of Contents 23 :depth: 1 24 :local: 25 26 27 Create a system user 28 ==================== 29 30 .. code-block:: console 31 32 # useradd -s /sbin/nologin --system postgres_exporter 33 # groupadd --system postgres_exporter 34 35 Download Prometheus node-exporter 36 ================================= 37 38 * Download 39 * Extract 40 * Copy to /usr/local/bin 41 * Set ownership and permissions 42 43 .. code-block:: console 44 45 # cd /tmp 46 # wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz 47 # tar -xzvf https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz 48 # cd postgres_exporter-0.12.0.linux-amd64 49 # cp postgres_exporter /usr/local/bin 50 # chown postgres_exporter:postgres_exporter /usr/local/bin/postgres_exporter 51 52 Create environment variable 53 =========================== 54 55 .. code-block:: console 56 57 # mkdir -p /opt/postgres_exporter 58 # cd /opt/postgres_exporter 59 # touch postgres_exporter.env 60 # Paste next content: 61 DATA_SOURCE_NAME="postgresql://postgres_exporter:PASSWORD@localhost:5432/?sslmode=disable" 62 63 64 Create postgres-exporter.sql file 65 ================================= 66 67 .. code-block:: console 68 69 # cd /opt/postgres_exporter 70 # touch postgres-exporter.sql 71 72 .. code-block:: 73 74 -- To use IF statements, hence to be able to check if the user exists before 75 -- attempting creation, we need to switch to procedural SQL (PL/pgSQL) 76 -- instead of standard SQL. 77 -- More: https://www.postgresql.org/docs/9.3/plpgsql-overview.html 78 -- To preserve compatibility with <9.0, DO blocks are not used; instead, 79 -- a function is created and dropped. 80 CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$ 81 BEGIN 82 IF NOT EXISTS ( 83 SELECT -- SELECT list can stay empty for this 84 FROM pg_catalog.pg_user 85 WHERE usename = 'postgres_exporter') THEN 86 CREATE USER postgres_exporter; 87 END IF; 88 END; 89 $$ language plpgsql; 90 91 SELECT __tmp_create_user(); 92 DROP FUNCTION __tmp_create_user(); 93 94 ALTER USER postgres_exporter WITH PASSWORD 'password'; 95 ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog; 96 97 -- If deploying as non-superuser (for example in AWS RDS), uncomment the GRANT 98 -- line below and replace <MASTER_USER> with your root user. 99 -- GRANT postgres_exporter TO <MASTER_USER>; 100 101 GRANT CONNECT ON DATABASE postgres TO postgres_exporter; 102 103 104 Login in Postgres 105 ================= 106 107 Login into Postgres and execute the previous file, as the postgres user. 108 109 .. code-block:: console 110 111 # su -c "psql" postgres 112 # \i postgres-exporter.sql 113 114 115 Systemd postgres-exporter service file 116 ====================================== 117 118 .. code-block:: systemd 119 120 # Path: /etc/systemd/system/postgres-exporter.service 121 122 [Unit] 123 Description=Prometheus exporter for Postgresql 124 Wants=network-online.target 125 After=network-online.target 126 127 [Service] 128 User=postgres_exporter 129 Group=postgres_exporter 130 WorkingDirectory=/opt/postgres_exporter 131 EnvironmentFile=/opt/postgres_exporter/postgres_exporter.env 132 ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=:9187 --web.telemetry-path=/metrics 133 Restart=always 134 135 [Install] 136 WantedBy=multi-user.target 137 138 139 140 Edit Prometheus configuration file 141 =================================== 142 143 * Add this new job to the /etc/prometheus/prometheus.yml configuration file. 144 145 .. code-block:: yaml 146 147 - job_name: 'postgres_exporter' 148 static_configs: 149 - targets: ['localhost:9187'] 150 151 152 Refresh systemd 153 =============== 154 155 .. code-block:: console 156 157 # systemctl daemon-reload 158 # systemctl enable --now postgres_exporter.service 159 # systemctl status postgres_exporter.service 160 # systemctl restart prometheus 161 162 Check if new postgres-exporter service, is up and running properly: http://ip:9187/ 163 164 Grafana control panel (GUI) 165 =========================== 166 167 You can now go to `Grafana dashboards <a href="https://grafana.taler.net/dashboards">`_ and easily 168 add a new dashboard for the Postgres Exporter program. Please make sure you choose the right Prometheus data source. 169 170 * Dashboard Id: 9628 171 * Dashboard URL: https://grafana.com/grafana/dashboards/9628-postgresql-database/ 172 173 174 More information: https://github.com/prometheus-community/postgres_exporter 175 176