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