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