In this blog, I will show you how can we monitor your PostgreSQL database with the help of postgres_exporter and Prometheus

If you are not aware of how to set up Grafana and Prometheus you can follow my previous article:-

Grafana Prometheus

Let’s get started:-

Step 1:- Installing and setting up postgres_exporter

Download the latest binaries from the here

Create a directory

sudo mkdir /opt/postgres_exporter

Now download the binaries

sudo wget https://github.com/wrouesnel/postgres_exporter/releases/download/v0.8.0/postgres_exporter_v0.8.0_linux-amd64.tar.gz

Now extract the files

sudo tar -xzf postgres_exporter_v0.8.0_linux-amd64.tar.gz

Go to the directory and move the postgres_exporter file to /usr/local/bin

sudo mv postgres_exporter /usr/local/bin/

Now create a env file 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_v0.8.0_linux-amd64
sudo vi postgres_exporter.env

Inside the postgres_exporter.env put the following:

 DATA_SOURCE_NAME="postgresql://postgres:postgres@localhost:5432/?sslmode=disable" 

the above parameters will show u all the databases available in Postgresql. If you want to monitor only a particular database then use the below parameters:

 DATA_SOURCE_NAME="postgresql://username:password@localhost:5432/database-name?sslmode=disable" 

In my case, I am using the first parameter

Step 2:- Create a postgres_exporter service

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

Paste the below content:

[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=/metricsRestart=always[Install]
WantedBy=multi-user.target

Now, start and enable the service

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

Step 3:- Integrate postgres_exporter with Prometheus

Add the below parameters in the prometheus.yml file

 - job_name: 'postgres_exporter'
static_configs:
- targets: [‘ip_address:9187']

Restart the Prometheus service

 sudo systemctl restart prometheus 

Step 4:- Display PostgreSQL Metrics on Grafana dashboard

Import the dashboard ID 9628 and you will get the result

Congratulations you follow all the steps and set up monitoring for your database

Your email address will not be published. Required fields are marked *