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

Photos via Pexels

Introduction

Setting up the Postgres exporter

Download the Postgres Exporter Binary

mkdir /opt/postgres_exportercd /opt/postgres_exporterwget https://github.com/wrouesnel/postgres_exporter/releases/download/v0.5.1/postgres_exporter_v0.5.1_linux-amd64.tar.gztar -xzvf postgres_exporter_v0.5.1_linux-amd64.tar.gzcd postgres_exporter_v0.5.1_linux-amd64sudo cp postgres_exporter /usr/local/bin

Prepare the env File

cd /opt/postgres_exportersudo 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

sudo useradd -rs /bin/false postgres
sudo nano /etc/systemd/system/postgres_exporter.service
[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
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

sudo systemctl stop prometheus
sudo nano /etc/prometheus/prometheus.yml
— job_name: ‘postgres_exporter’
static_configs:
— targets: [‘xxx.xxx.xxx.xxx:9187’]
sudo systemctl start prometheus
sudo systemctl status prometheus

Display the metrics on Grafana Dashboard

Grafana Dashboard

References

A Tech Writer, passionate about Network and Systems with a high interest in Cloud,Virtualization and Machine Learning.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store