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’.
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.
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’.
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.
Input the PostgreSQL dashboard id ‘xxx’ and click Load. The PostgreSQL dashboard id ‘9628’ is available on the Grafana Dashboard repository.
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’.
After the Grafana dashboard is imported, check your new dashboard and you should see the PostgreSQL monitoring dashboard as below.
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.