Monitor PostgreSQL Server With Prometheus and Grafana

PostgreSQL is a relational database with a high reputation in reliability, data integrity, robust feature set, and extensibility. PostgreSQL offers more functionality to store and scale more complicated workloads by implementing both SQL and non-relational(JSON) queries

PostgreSQL was first developed in 1986 as part of the Postgres project at the University of California at Berkeley. This means that, the database system has been in active development for over 30 years. Due to its high performance and resilience, PostgreSQL finds use in innumerable areas such as storage for web data, and analytic and mobile apps.

Monitoring a database is important to identify its performance and health. Prometheus is a free and open-source tool used for event monitoring and alerting. It was developed in 2012 by SoundCloud and promoted in 2016 to the Cloud Native Computing Foundation. Prometheus works by collecting and storing the time-series data. The metrics collected are stored with key/value pairs and a timestamp at which they were collected.

Prometheus offers the following features:

• Single server nodes are autonomous and don’t rely on distributed storage
• Targets are discovered via service discovery or static configuration
• The time-series metrics collection happens via a pull model over HTTP
• It uses the PromQL query language which is flexible to leverage this dimensionality.
• Multi-dimensional data model where time series data is identified by metric name and key/value pairs
• It supports multiple graphing and dashboarding modes.

For Prometheus to be able to collect metrics from the PostgreSQL database, we need to install the postgres_exporter which exposes the data and metrics. It can publish about 450 Prometheus time series data by default. However, it can collect and publish more metrics than the set default. The other features supported by the PostgreSQL Server Exporter are:

• It supports the multi-target pattern. This makes it possible to run a single postgres_exporter instance for multiple PostgreSQL targets.
• Preconfigured auth modules are supported to avoid putting sensitive information like username and password in the URL for DSNs(DATA_SOURCE_NAMEs)

Once the logs have been collected, we need to visualize them using graphs, heatmaps, e.t.c to identify the performance of the PostgreSQL database. Grafana is a cross-platform tool that provides context-rich visualizations, through graphs and other data presentation methods. It supports metrics from several data sources such as ElasticSearch, InfluxDB, Amazon Timestream, Prometheus, PostgreSQL, MySQL, Graphite e.t.c

One of the amazing features of Grafana is that users can customize each dashboard to meet the desired needs of the project. The other feature is the Prometheus query builder which makes it easy to build and run PromQL queries.

In this guide, we will walk through how to monitor the PostgreSQL Server With Prometheus and Grafana

Prerequisites

For this guide, you need PostgreSQL 9.4 and above installed.

#1. Install Prometheus on Linux

Prometheus can be installed on the preferred Linux system using the aid provided in the guides below

• On Ubuntu
  • Install Prometheus Server on Ubuntu
• On Debian
  • Install Prometheus and node_exporter on Debian
• On RHEL 9/CentOS 9/Rocky Linux 9/Alma Linux 9
  • Install Prometheus with Node Exporter on Rocky Linux /AlmaLinux
• On RHEL 8/CentOS 8/Rocky Linux 8/Alma Linux 8
  • How To Install Prometheus on RHEL 8 / CentOS 8

Once Prometheus has been installed and configured, verify by accessing the WebUI with the URL http://IP_Address:9090

#2. Install and Configure PostgreSQL Server Exporter

The Prometheus exporter for PostgreSQL server metrics supports the following PostgreSQL versions 9.4+.

First, ensure the required packages have been installed.

##On Debian/Ubuntu
sudo apt -y install wget tar curl vim

##On CentOS/Rocky/Alma
sudo yum -y install wget tar curl vim

Now download the latest release file from the GitHub Releases. You can also download the latest version with the commands below:

Find and export the latest version:

VERSION=$(curl -s https://api.github.com/repos/prometheus-community/postgres_exporter/releases/latest|grep tag_name|cut -d '"' -f 4|sed 's/v//')

Create a working directory:

sudo mkdir /opt/postgres_exporter && cd /opt/postgres_exporter

Download the latest archive in the directory.

##For AMD64
sudo wget https://github.com/prometheus-community/postgres_exporter/releases/download/v$VERSION/postgres_exporter-$VERSION.linux-amd64.tar.gz

##For ARM64
sudo wget https://github.com/prometheus-community/postgres_exporter/releases/download/v$VERSION/postgres_exporter-$VERSION.linux-arm64.tar.gz

##For i386
sudo wget https://github.com/prometheus-community/postgres_exporter/releases/download/v$VERSION/postgres_exporter-$VERSION.linux-386.tar.gz

Once downloaded, extract the archive:

sudo tar -xf postgres_exporter-$VERSION.linux-*.tar.gz

Now copy the binary to your $PATH:

sudo cp postgres_exporter-$VERSION.linux-*/postgres_exporter /usr/local/bin

Create a configuration file

sudo vim postgres_exporter.env

In the file, provide the DATA_SOURCE_NAME(DSN) variable for the PostgreSQL instance. For example:

##To monitor all Databases##
DATA_SOURCE_NAME="postgresql://postgres:postgres@localhost:5432/?sslmode=disable"

###To monitor a Specific database, uncomment and edit the line below###
# DATA_SOURCE_NAME="postgresql://username:password@localhost:5432/database-name?sslmode=disable"

You can replace localhost with the IP address if you are running PostgreSQL on a remote host. You also need to ensure that remote connectivity is enabled on PostgreSQL.

Now create a systemd service for the PostgreSQL Server Exporter.

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

Add the below lines to the file:

Unit]
Description=Prometheus exporter for Postgresql
Wants=network-online.target
After=network-online.target

[Service]
User=postgres_exporter
Group=postgres_exporter
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

On RHEL-based systems, you need to configure SELinux as shown:

sudo /sbin/restorecon -v /usr/local/bin/postgres_exporter

Create a system user for the service:

sudo adduser -M -r -s /sbin/nologin postgres_exporter

Allow the user to own the created directory:

sudo chown -R postgres_exporter:postgres_exporter /opt/postgres_exporter

Reload the daemon:

sudo systemctl daemon-reload

Start and enable the service:

sudo systemctl enable --now postgres_exporter

Verify if the service is running:

$ systemctl status postgres_exporter
● postgres_exporter.service
     Loaded: loaded (/etc/systemd/system/postgres_exporter.service; enabled; vendor preset: disabled)
     Active: active (running) since Mon 2022-09-05 09:48:38 CEST; 5s ago
   Main PID: 33323 (postgres_export)
      Tasks: 4 (limit: 23441)
     Memory: 11.0M
        CPU: 12ms
     CGroup: /system.slice/postgres_exporter.service
             └─33323 /usr/local/bin/postgres_exporter --web.listen-address=:9187 --web.telemetry-path=/metrics

If you have a firewall running, allow the port through it:

##For UFW
sudo ufw allow 9187

##For Firewalld
sudo firewall-cmd --add-port=9187/tcp --permanent
sudo firewall-cmd --reload

#3. Configure Prometheus for Postgres Exporter

To be able to store the metrics, we need to configure Prometheus as shown:

sudo vim /etc/prometheus/prometheus.yml

Create a new job for the Postgres Exporter as shown:

scrape_configs:
.......
  - job_name: 'postgres_exporter'
    scrape_interval: 5s
    static_configs:
      - targets: ['SERVER-IP:9187']

Provide the server IP on which postgres_exporter is running. Save the file and restart the Prometheus service:

sudo systemctl restart prometheus

#4. Install and Configure Grafana on Linux

To install Grafana, you also need to add the repositories to your system. This can be done with the command:

• On Debian/Ubuntu

sudo apt-get install -y software-properties-common wget
wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -
echo "deb https://packages.grafana.com/oss/deb stable main" | sudo tee -a /etc/apt/sources.list.d/grafana.list

• On RHEL/CentOS/Rocky/Alma

$ sudo vim /etc/yum.repos.d/grafana.repo
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=0
enabled=1
gpgcheck=0
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt

Once the repositories have been added, install Grafana with the command:

##On Debian/Ubuntu
sudo apt-get update && sudo apt install grafana

##On Rhel/CentOS/Rocky Linux/Alma Linux
sudo yum install grafana

Once complete, reload the daemon:

sudo systemctl daemon-reload

Start and enable the service:

sudo systemctl start grafana-server
sudo systemctl enable grafana-server

Check if the service is running:

$ systemctl status grafana-server
● grafana-server.service - Grafana instance
     Loaded: loaded (/usr/lib/systemd/system/grafana-server.service; enabled; vendor preset: disabled)
     Active: active (running) since Mon 2022-09-05 09:51:38 CEST; 10s ago
       Docs: http://docs.grafana.org
   Main PID: 34373 (grafana-server)
      Tasks: 12 (limit: 23441)
     Memory: 40.8M
        CPU: 658ms
     CGroup: /system.slice/grafana-server.service
             └─34373 /usr/sbin/grafana-server --config=/etc/grafana/grafana.ini --pidfile=/var/run/grafana/grafana-server.pid --packaging=r

Allow the Grafana port through the firewall:

##For UFW
sudo ufw allow 3000

##For Firewalld
sudo firewall-cmd --add-port=3000/tcp --permanent
sudo firewall-cmd --reload

#5. Access the Grafana Dashboard

Now access the Grafana dashboard using the URL http://IP_Address:3000

Log in using the default creds:

username: admin
password: admin

Once logged in, you will see the below dashboard.

#6. Add Prometheus Data Source to Grafana

Now add Prometheus as a data source on Grafana by clicking on Data sources-> Prometheus and providing the below details.

Click on save and test to see if the added data source is working.

We are now set to import and visualize dashboards on Grafana.

There are several PostgreSQL dashboards on Grafana.com. But for this guide, we will import two dashboards Lucas Estienne’s dashboard and Patsevanton’s dashboard

Using the Dashboard ID, proceed as shown.

Select Prometheus as the data source and import the dashboard.

Once imported, Lucas Estienne’s dashboard will appear as shown.

Use similar steps to add Patsevanton’s dashboard, once added, it appears as shown.

That is it!

Recommended books:

• Best Books To Learn PostgreSQL Databases

Verdict

You can now use the added dashboards to monitor the performance and health of your PostgreSQL Server. You can also configure alerting on Grafana to be able to detect and identify issues on time.

See more:

• Secure Prometheus Server With Basic Password Authentication
• Monitor Percona MySQL / Percona XtraDB Cluster With Prometheus & Grafana
• Setup Prometheus and Grafana on Kubernetes using prometheus-operator
• How To Monitor Linux Server Uptime with Prometheus