Monitoring PostgreSQL Databases using Postgres exporter along with Prometheus and Grafana.

Photos via Pexels

Introduction

In my previous articles , i have covered monitoring using Prometheus along with Grafana. and alerting using the AlertManager integrated to Prometheus.

In today’s article, i will mainly cover the topic of integrating the Postgres exporter to Prometheus in order to get PostgreSQL databases metrics for a monitoring purpose.

Setting up the Postgres exporter

Download the Postgres Exporter Binary

First, you need to download the postgres exporter binary tar archive and untar it as follows

mkdir /opt/postgres_exporter
cd /opt/postgres_exporter
wget https://github.com/wrouesnel/postgres_exporter/releases/download/v0.5.1/postgres_exporter_v0.5.1_linux-amd64.tar.gz
tar -xzvf postgres_exporter_v0.5.1_linux-amd64.tar.gz
cd postgres_exporter_v0.5.1_linux-amd64
sudo cp postgres_exporter /usr/local/bin

Prepare the env File

Then, you need to prepare an env file for the postgres exporter where we will set a variable known as DATA_SOURCE_NAME.

Metrics from all defined databases in DATA_SOURCE_NAME will be collected.

cd /opt/postgres_exporter
sudo nano postgres_exporter.env
# Inside the postgres_exporter.env put the following:
DATA_SOURCE_NAME="postgresql://username:password@localhost:5432/database-name?sslmode=disable"
# or you can use the following to monitor all the databases available on localhost 
DATA_SOURCE_NAME="postgresql://postgres:postgres@localhost:5432/?sslmode=disable"

Setup the Postgres Exporter Service

First, create the user postgres

sudo useradd -rs /bin/false postgres

Then the service

sudo nano /etc/systemd/system/postgres_exporter.service

Next, put the following inside

[Unit]
Description=Prometheus exporter for Postgresql
Wants=network-online.target
After=network-online.target
[Service]
User=postgres
Group=postgres
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

Finally, enable and start the service

sudo systemctl daemon-reload
sudo systemctl start postgres_exporter
sudo systemctl enable postgres_exporter
sudo systemctl status postgres_exporter

Integrate the Postgres exporter to Prometheus

First, you need to edit prometheus.yml configuration file.

sudo systemctl stop prometheus
sudo nano /etc/prometheus/prometheus.yml

Then, add the new targets to scrape

— job_name: ‘postgres_exporter’
    static_configs:
    — targets: [‘xxx.xxx.xxx.xxx:9187’]

Finally, reload the service

sudo systemctl start prometheus
sudo systemctl status prometheus

Display the metrics on Grafana Dashboard

There is plenty of interessting Grafana Dashboard for PostgreSQL monitoring:

• PostgreSQL Database (which i found the most adapted for me)
• Postgres Overview
• Postgres Overview
• You can also use the 6742 ID

Choose the Prometheus datasource and get your result.

Grafana Dashboard

References

All databases on the eyes. Postgres exporter + Prometheus + Grafana.