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.
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.
Benefits of Grafana and Prometheus
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
How to Configure Grafana for PostgreSQL Monitoring?
Today, let’s deep dive into the type of monitoring that we need to choose for PostgreSQL database server and the various monitoring tools. 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 performance heuristics in real time such as System Resource Monitoring (CPU, Memory, Storage, Network), Database Monitoring (Active session, Replication Status, Query Performance, etc).
Now, let’s configure Grafana for PostgreSQL Monitoring by following the key steps mentioned below:
Download and Install Grafana
First we need to create Grafana repositories to download the grafana-server package.
# vi /etc/yum.repos.d/grafana.repo
[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crtInstall and start Grafana service by using systemctl command.
# yum install grafana -y
# systemctl enable grafana-server
# systemctl start grafana-serverPost that we need to verify whether the Grafana application can be accessed, using the below mentioned url or not.
http://:3000/Download and install Prometheus
Follow our instruction before.
Download and configure the Postgres_exporter
Create a directory for Postgres_exporter and download the binary of Postgres_exporter
# mkdir /opt/postgres_exporter
# cd /opt/postgres_exporter
# wget https://github.com/wrouesnel/postgres_exporter/releases/download/v0.5.1/postgres_exporter_v0.5.1_linux-amd64.tar.gzExtract the Postgres_exporter file and copy to the user binary directory.
# tar -xzvf postgres_exporter_v0.5.1_linux-amd64.tar.gz
# cd postgres_exporter_v0.5.1_linux-amd64
# cp postgres_exporter /usr/local/bin
# cd /opt/postgres_exporterCreating the postgres_exporter env file using vi text editor.
# sudo vi postgres_exporter.env
DATA_SOURCE_NAME=”postgresql://:@:/?sslmode=disable”Below mentioned is the script to create Postgres_exporter service file using vi text editor.
# vi /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
Restart=always
[Install]
WantedBy=multi-user.target
Start the Postgres_exporter service and ensure the target service has been set to “UP” at the Prometheus web UI.
# sudo systemctl daemon-reload
# sudo systemctl start postgres_exporter
# sudo systemctl enable postgres_exporter
Importing Grafana Dashboard for PostgreSQL Monitoring
To import the Grafana dashboard, Prometheus acts to collect the statistical data matrix ( which is in binary format) from the Operating system and Postgres_exporter acts to collect the database data matrix from PostgreSQL to be monitored in Grafana.
At this point we have installed and configured Grafana, PostgreSQL & Prometheus have been added as data sources and the Postgres_exporter matrix collector has also been configured. We can now generate new graphs by creating manual queries under the new dashboards feature or else you can use any of the pre-built Grafana dashboard templates for monitoring PostgreSQL.
Let’s take a look at the steps below to achieve the aforementioned objective:
First, we need to set up our Grafana dashboard by visiting the Grafana PostgreSQL Dashboard and choosing the suitable dashboard for PostgreSQL monitoring (in this case for System Resource Monitoring and Database Monitoring).
The next step is to download the Grafana dashboard JSON or copy the ID number of the dashboard to import it into our Grafana dashboard.
In order to import either the JSON file or the Dashboard ID to our Grafana dashboard we need to follow the below-mentioned steps:
Go to the left menu bar
Click the dashboard icon to import the dashboard ID or upload the Dashboard JSON file that we have downloaded from the Grafana PostgreSQL Dashboard.
After executing step number 3 above you will be able to see the dashboard list ( reference image below). Now, you will be able to access the dashboard by clicking Grafana Dashboard and then clicking browse to see the dashboard management menu.
After choosing the dashboard to be monitored in the dashboard management, now we are able to see the dashboard to monitor our PostgreSQL database server that contains system resources and database monitoring.( what ate these types of??)
Comments