PostgreSQL Tutorial: Monitoring PostgreSQL with Prometheus and Grafana

April 21, 2024

Summary: In this tutorial, you’ll set up Prometheus and Grafana Monitoring Platforms to monitor the PostgreSQL database server. You’ll install and set up the postgres_exporter to gather PostgreSQL server metrics and expose them to the Prometheus server. At the end of this tutorial, you’ll set up the dashboard monitoring for the PostgreSQL server via the Grafana data visualization platform.

Table of Contents

What is Prometheus?

Prometheus is an open-source tool that enables you to monitor databases, VMs, and basically anything else. With its cloud-native monitoring capabilities, there’s a time-series database that sweeps data from a bunch of exporters that you define, in order to export all of the resource-monitoring metrics that you need to properly monitor your database servers. The most popular exporter is node-exporter for Prometheus at system-level resource monitoring.

The Prometheus Alertmanager, helps to generate alerts for metric thresholds. What’s more, you can use Grafana to set up dashboards with Prometheus, observe the patterns and behaviors of the metrics you have collected. For PostgreSQL, you can also use a PostgreSQL exporter to export vital metrics such as active sessions, database locks, and replication.

What is Grafana?

Grafana is a multi-platform open source analytical and visualization tool consisting of multiple individual panels arranged in a grid. The panels interact with configured data sources like AWS CloudWatch, Microsoft SQL server, Prometheus, MySQL, InfluxDB, and many others. The design of Grafana is such that each panel is tied to a data source. The Grafana dashboards which contains multiple panels in a single grid, helps to visualize results from multiple data sources simultaneously.

Grafana ships with a built-in PostgreSQL data source plugin that allows you to query and visualize the data from a PostgreSQL compatible database. The PostgreSQL integration for Grafana’s prebuilt dashboard allows you to visualize important historical performance statistics in real time such as System Resource Monitoring (CPU, Memory, Storage, Network), Database Monitoring (Active session, Replication Status, Query Performance, etc).

Benefits of Prometheus and Grafana

  • They are Open Source based
  • Customizable and allows to create your own monitoring dashboard
  • With Time picker dropdown access relative time range options, auto refresh options and set custom absolute time ranges
  • Graph legend provides legend information from the graph
  • Automatic service discovery facility is available
  • Widely available community support
  • Supports both system and database performance metrics
  • Flexibility to configure custom metrics that are not supported by default in PostgreSQL exporter

Prerequisites

To complete this tutorial, you’ll need the following requirements:

  • A server with Prometheus and Grafana installed on top of it.
  • A target server with a PostgreSQL database server installed.
  • A non-root user with sudo/root administrator privileges.

Now let’s get started When all prerequisites are finished and in place.

PostgreSQL Server Configuration

The PostgreSQL server provides two different password encryption methods: md5 and scram-sha-256. Both password encryptions can be configured via the PostgreSQL config file ‘postgresql.conf’.

In this step, you’ll set up PostgreSQL to use the ‘scram-sha-256’ password encryption.

Open the configuration file ‘postgresql.conf’ via an editor. Uncomment the option ‘password_encryption’ and change the value to ‘scram-sha-256’.

password_encryption = scram-sha-256     # scram-sha-256 or md5

Next, open the config file ‘pg_hba.conf’. The file ‘pg_hba.conf’ is the configuration where password authentication methods are defined for hosts or IP addresses. Change the default authentication methods for the host ‘127.0.0.1/32’ and ‘::1/128’ to ‘scram-sha-256’. With this, the authentication method ‘scram-sha-256’ will be used for every client connection to the PostgreSQL server ‘127.0.0.1’.

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

Lastly, restart the PostgreSQL service and apply the changes. With the PostgreSQL server configured, you’ll next install the ‘postgres_exporter’ that allows you to gather metrics of the PostgreSQL server.

Downloading postgres_exporter

The ‘postgres_exporter’ is a prometheus exporter for the PostgreSQL server metrics. It supports multiple versions of PostgreSQL such as 9.4 ~ 14. The ‘postgres_exporter’ is a single binary file application, it’s mainly written in Go.

You’ll download the ‘postgres_exporter’ in these steps on the PostgreSQL server.

To start, run the below command to create a new system user ‘postgres_exporter’ on your PostgreSQL server.

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

After that, download the ‘postgres_exporter’ binary file via the wget command below. Be sure to check the ‘postgres_exporter’ GitHub page to get the latest version of ‘postgres_exporter’. At the time of this writing, the latest version of postgres_exporter is v0.15.

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz

Next, run the below tar command to extract the postgres_exporter package. Then, move the extracted directory of postgres_exporter to the ‘/opt/postgres_exporter’.

tar xf postgres_exporter*.tar.gz
mv postgres_exporter*/ /opt/postgres_exporter

Lastly, verify the list of files and directories on the ‘postgres_exporter’ directory. You should see the ‘postgres_exporter’ binary is available.

$ ls /opt/postgres_exporter
LICENSE  NOTICE  postgres_exporter

With the ‘postgres_exporter’ binary file downloaded, you’ll then configure the ‘postgres_exporter’ for gathering PostgreSQL server metrics.

Configuring postgres_exporter

In this step, you’ll configure the ‘postgres_exporter’ to gather PostgreSQL metrics, and this can be done by defining the PostgreSQL user and password. You’ll also set up and configure the systemd service for the ‘postgres_exporter’.

With the ‘postgres_exporter’, you can expose metrics for all available databases on the PostgreSQL server, or you can expose specific databases that you want to monitor. You can also use secure SSL mode or non-SSL mode.

Now create a new file ‘/opt/postgres_exporter/.env’. Add the following lines to the file. Also, be sure to change the details of the PostgreSQL user, password, and host. With this ‘.env’ file, you’ll scrape and gathers PostgreSQL metrics from all available databases. You can also gather metrics from a specific PostgreSQL database, and adjust the following config file.

# Format
# DATA_SOURCE_NAME=postgresql://username:password@localhost:5432/postgres?sslmode=disable

# Monitor all databases via postgres_exporter
DATA_SOURCE_NAME="postgresql://postgres:password@localhost:5432/?sslmode=disable"

# Monitor specific databases on the PostgreSQL server
# DATA_SOURCE_NAME="postgresql://username:password@localhost:5432/database-name?sslmode=disable"

Next, run the below chown command to change the ownership of the ‘/opt/postgres_exporter’ directory to the user ‘postgres_exporter’.

sudo chown -R postgres_exporter: /opt/postgres_exporter

After that, run the below command to create a new systemd service file ’/etc/systemd/system/postgres_exporter.service’. With this, you can easily manage the ‘postgres_exporter’ via the systemctl command utility.

sudo tee /etc/systemd/system/postgres_exporter.service<<EOF
[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/.env
ExecStart=/opt/postgres_exporter/postgres_exporter --web.listen-address=:9187 --web.telemetry-path=/metrics
Restart=always

[Install]
WantedBy=multi-user.target
EOF

Now run the below systemctl command to reload the systemd manager and apply the changes.

sudo systemctl daemon-reload

After the systemd manager is reloaded, start and enable the ‘postgres_exporter’ service via the systemctl command utility below.

sudo systemctl start postgres_exporter
sudo systemctl enable postgres_exporter

The ‘postgres_exporter’ should be running and scrape metrics from the PostgreSQL server. Also, it should be enabled and will be run automatically upon the bootup.

Lastly, run the below command to verify the ‘postgres_exporter’ service status. Also, ensure that the ‘postgres_exporter’ is running and enabled.

sudo systemctl status postgres_exporter

You’ll receive the output similar to this - the ‘postgres_exporter’ service is running and it’s enabled.

At this point, the ‘postgres_exporter’ is running on the default TCP port ‘9187’ on the PostgreSQL server. This exposes PostgreSQL metrics to the Prometheus server, but before that, you’ll also need to set up the firewalld on the PostgreSQL server.

Setting up Firewall

In this step, you’ll set up the firewall to open the default port of ‘postgres_exporter’ - TCP 9187. After that, you’ll verify that the ‘postgres_exporter’ metrics is accessible via the web browser.

For Ubuntu systems that used UFW as the firewall, run the below ufw command to add port 9187 to the ufw firewall. Then, reload the firewall to apply the changes.

sudo ufw allow 9187/tcp
sudo ufw reload

You can now verify the list of ports on UFW via the ufw command below.

sudo ufw status

You’ll receive the output like the following - the postgres_exporter port 9187 is added to the firewalld.

For RHEL-based distributions that use firewalld as the default firewall, run the below firewall-cmd command to add port 9187 to the firewalld. Then, reload the firewalld to apply new changes.

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

After that, run the below command to verify that the postgres_exporter port 9187 is added.

sudo firewall-cmd --list-ports

You should now see that port 9187 is added to the firewall.

Lastly, open your web browser and visit your PostgreSQL server IP address followed by the ‘postgres_exporter’ port 9187 (i.e: http://192.168.5.21:9187/metrics).

You should see the details of PostgreSQL server metrics gathered by the ‘postgres_exporter’.

pstgres_exporter metrics

With the firewall configured and the ‘postgres_exporter’ is accessible, you’ll next add the ‘postgres_exporter to the Prometheus ‘scrape_configs’ target.

Adding postgres_exporter to Prometheus

Back to the Prometheus server, then open the Prometheus config file ‘/etc/prometheus/prometheus.yml’. Under the ‘scrape_configs’ section, add the following lines to the file. In this example, you’ll set up a new job for gathering PostgreSQL server metrics with the name ‘postgres_exporter’, and the target server of PostgreSQL is ‘192.168.5.21:9187’, which is the metrics that are exposed by the ‘postgres_exporter’ service.

  # PostgreSQL Monitoring with postgres_exporter
  - job_name: 'postgres_exporter'
    scrape_interval: 5s
    static_configs:
      - targets: ['192.168.5.21:9187']

Now run the below systemctl command to restart the Prometheus service and apply the changes.

sudo systemctl restart prometheus

With the new scrape_configs created and the Prometheus server restarted, you’ll then verify the ‘postgres_exporter’ via the Prometheus web dashboard.

open up your web browser and visit the Prometheus server IP address followed by the default port 9090 (i.e: https://192.168.5.100:9090).

Log in to the Prometheus server with your username and password, when you have the basic_auth enabled.

On the Prometheus dashboard, click the ‘browser’ icon to get the list of Prometheus queries. Then, input ‘pg’ to filter queries starting with ‘pg’. You’ll then receive the output like the following screenshot.

pg queries

Next, click on the Status menu and select Targets, and you should see the job name ‘postgres_exporter’ is up and running on the PostgreSQL server with IP address ‘192.168.5.21:9187’.

postgres_exporter prometheus

Now that the ‘postgres_exporter’ is added to the Prometheus server and the metric from the PostgreSQL server is now available on Prometheus.

In the next steps, you’ll set up the Grafana dashboard for monitoring the PostgreSQL server.

Setting up Dashboard Monitoring via Grafana

Open your Grafana domain name or the server IP address, then log in to the Grafana dashboard using your username and password.

After logging in, click on the Dashboard menu and select Import.

dashboard import

Input the PostgreSQL dashboard id ‘xxx’ and click Load. The PostgreSQL dashboard id ‘9628’ is available on the Grafana Dashboard repository.

import dashboard id

Next, input the new dashboard and select ‘Prometheus’ as the data source. Then click Import to apply.

In this example, you’ll create a new dashboard with the name ‘PostgreSQL Monitoring’.

coinfigure new dashboard

After the Grafana dashboard is imported, check your new dashboard and you should see the PostgreSQL monitoring dashboard as below.

PostgreSQL dashboard monitoring

Conclusion

Congratulations! You’ve now finished the PostgreSQL Server monitoring with Prometheus and Grafana via the ‘postgres_exporter’. You’ve also created a specific dashboard for your PostgreSQL Server monitoring. With this, you can get insightful details about what is going on in your PostgreSQL.

You can now check the Prometheus Community GitHub page to get details of available exporters on Prometheus. This allows you to monitor your applications via Grafana and Prometheus Monitoring Stack.

See more

Metrics in PostgreSQL monitoring with Prometheus

PostgreSQL Monitoring