As per the “State of PostgreSQL 2021” survey by timescale for the best visualization tool, Grafana stood first with 38.7% responders.

In this post, we are going to configure Grafana to monitorPostgreSQL Database 

There are 8 major steps involved in Installing and Configuring Grafana for PostgreSQL.

• Install Grafana(Monitoring server) # monitoring tool.
• Install prometheus(Monitoring server) # To store the metrics collected from servers.
• Install node_exporter(PostgreSQL Server) # To collect Linux metrics and send to prometheus.
• Integrate prometheus with node_exporter(Monitoring server)
• Install postgres_exporter(PostgreSQL Server) # To collect database metrics and send to prometheus.
• Integrate prometheus with postgres_exporter(Monitoring server)
• Configure dashboards for Linux and PostgreSQL metrics(Monitoring server)
• View the dashboard(Monitoring server)

In the below demonstration, we have used PostgreSQL 13 on CentOS 8 machine.

STEP 0 – Install PostgreSQL 13 on CentOS 8

Login to the database cluster and ensure the host connectivity is enabled, do the following.

1. Disable firewall

systemctl stop firewalld
systemctl disable firewalld

1. listen_address=’*’ in postgresql.conf
2. 0.0.0.0/0 in pg_hba.conf

STEP 1 – Install Grafana for PostgreSQL

1. Create a repository file

2. Install and start Grafana using yum:

upon successful installation, verify with

http://192.168.72.129:3000/

Here the username and password is ‘admin’

STEP 2: Download and Install Prometheus as a data source

Verify successful installation with

prometheus –version

Next, we need to edit YAML file and start the prometheus service. We will do that in sometime.

STEP 3: Configuring Node Exporter on Postgres servers (to get Linux metrics like like CPU, Memory etc.,)

Start node_exporter on the PostgreSQL server as a daemon:

Validate the metrics being collected using curl:

curl http://localhost:9100/metrics

STEP 4 Add the metrics being collected using node_exporter to Prometheus

in the /root/prometheus-files/prometheus.yml add postgresql server IP details as below

Start Prometheus upon modifying the configuration file:

Go to the following URL and see whether the new target is visible:

http://192.168.72.129:9090/targets

STEP 5: Collecting PostgreSQL metrics using postgres_exporter on Postgres servers

Validate by connecting to the database using the connection URI used by the exporter.

psql -d “postgresql://postgres:postgres@192.168.72.129:5432/postgres?sslmode=disable”

Prepare the env File : You need to prepare an env file for the postgres exporter where we will set a variable known as DATA_SOURCE_NAME. Metrics from all defined databases in DATA_SOURCE_NAME will be collected.

example., export DATA_SOURCE_NAME=”postgresql://login:password@hostname:port/dbname”

Start the postgres_exporter

STEP 6 Add the Postgres server and the port used by postgres_exporter to prometheus

in the /root/prometheus-files/prometheus.yml add postgresql server IP details as below

Final YAML should look like this

Reload using SIGHUP or restart Prometheus:

Validate the targets using the following URL on the browser:

http://192.168.72.129:9090/targets

STEP 7: Importing dashboard

• Add datasource as prometheus

• Import linux metrics -> dashboard URL ID : 1860
• Import PostgreSQL Metrics -> dashboard URL ID : 9628

Upon successful configuration, you should see the dashboard with the values.

Bonus

Adding prometheus, node_exporter and postgres_exporter as services to operating system

Adding prometheus as a service

Create prometheus service file with below content

Enable and start the service

Adding node_exporter  as a service

Create node_exporter service file with below content

Adding postgres_exporter as a service

Create a datasource file

Create postgres_exporter service file with below content

Reboot the machine and confirm everything is working.