Prometheus based PostgreSQL database monitoring and Alert generating System

In this article I’m going to talk about how Prometheus can be used to generate notifications for user defined database events. These user defined events are domain specific. As an example in aviation industry a particular user may want to get notified when there is a new price record or new tax record is added to the system. Or some other user wants to get notified when the actual departure time of a flight is delayed by 5 minutes from the estimated departure time. These type of user specific event notifications can be facilitated by this system.

This diagram illustrates the architecture of Prometheus and some of its ecosystem components:

The overview architecture diagram of the system is as follows.

Prometheus

Prometheus is an open-source tool built with the objectivity of monitoring and alerting systems. Prometheus collects and stores its metrics as time series data, i.e. metrics information is stored with the timestamp at which it was recorded, alongside optional key-value pairs called labels. Normally metric means a numeric measurement. Time series means changes are recorded over time. What users want to measure differs from application to application. Normally these metrics use to measure the requests received by a web server, number of connections for a database etc. But in our case we use those metrics to find out whether a new database row is added to a table or some other user specified event is violated. These collected metrics can be used for various use cases such as alert generation using alertmanager, visualize using visualize tools like Grafana etc.

Alertmanager

The Alertmanager handles alerts sent by client applications such as the Prometheus server. Alertmanager can be separated from the Prometheus Eco system and used as a standalone entity for the alert generation. With the exposed APIs we can write custom client applications using programming languages like java. It takes care of deduplicating, grouping, and routing them to the correct receiver integration such as email, PagerDuty, or OpsGenie.

Postgres Exporter

When extracting metrics from third-party system, there are several number of libraries which we can use to export the prometheus metrics. According to the third-party system we have to choose the appropriate library. In our case we can use postgres_exporter as we want to extract metrics related to Postgres database.

Other available exporters can be found here.

Prerequisites

You need to have PostgreSQL database and Docker installed in your system.

How To Setup

We have to setup Prometheus, alertmanager and postgres_exporter. There are several methods you can follow to setup above three tools. But you have to have several dependencies such as Go programming language to be installed in your system run above tools. Instead of that you can easily follow Docker based approach to set up the project.

• Setup postgres_exporter

As mentioned above postgres_exporter is responsible for extracting time series data from the database. Normally postgres_exporter is configured to extract default metrics related to a database. According to our requirement we need to customize the metrics. In order to do that we can use queries.yaml file. Below shows a example queires.yaml file

Here we query the fuel price table and extract the newly created items in the last 15 seconds. This interval can be varied according to the scrape interval provided in the prometheus configuration.

Below given the Dockerfile configuration for the postgres_exporter.

• Setup Alertmanager

All the configurations related to alert generation is stored in the alertmanager.yml file. You can provide different receiver configurations according to your requirement. More information about alermanager configuration can be found here.

Below given the Dockerfile configuration for the alertmanager.

• Setup Prometheus

Prometheus configurations are stored in prometheus.yml file. Here we can configure the scrape interval to extract data from the exporters. More information about Prometheus configuration can be found here.

rules.yml file contains the rules related to alert generation. When a rule is violated given the expression an alert is generated.

Below given the Dockerfile configuration for the Prometheus.

How To Run

• postgres_exporter

$docker build . -t postgres_exporter
$docker run --net=host --name=postgres_exporter -p 9187:9187 postgres_exporter

• Alertmanager

$docker build . -t alertmanager
$docker run --net=host --name=alertmanager -p 9093:9093 alertmanager

• Prometheus

$docker build . -t prometheus
$docker run --net=host --name=prometheus -p 9090:9090 prometheus

Use the above commands to create the images and run the containers for postgres_exprter, alertmanager and Prometheus.

Add a new item to your database table and you’ll get a alert from prometheus.

Sample project can be found here.

GitHub - KalanaGeesara/notification_manager: Prometheus based alert managing system for user…