Monitoring PostgreSQL using Zabbix

image
Daria Vilkova’s presentation for Zabbix Meetup Online

I want to introduce you to the PostgreSQL and operating system monitoring tool, which is being developed by our company using Zabbix.

We have chosen Zabbix as our monitoring tool for a long time because it is an open source platform supported by an active community and is very popular in Russia.

We created an active agent – Mamonsu, which provided more flexible monitoring than standard tools allowed at that time, and ensured the collection of metrics and their sending to Zabbix Server. We use Mamonsu in our audit.

Mamonsu

Mamonsu is an active agent (Zabbix Trapper) for monitoring PostgreSQL and the operating system. Mamonsu (written in Python) allows you to configure PostgreSQL and operating system monitoring parameters in five minutes.

Mamonsu has additional tools:

  • mamonsu tune is a command that edits settings in the PostgreSQL configuration file for the machine on which the Mamonsu agent is installed.
  • mamonsu report is a command that generates responses about the operating system and PostgreSQL.

Mamonsu is installed on the DBMS server, collects information, assembles it into JSON, which is sent for visualization to Zabbix Server, where there should be a template for its metrics.

Monitoring_PostgreSQL_Darja_Vilkova-05

How Mamonsu works

Mamonsu features

  • Working effectively with PostgreSQL… A persistent connection to PostgreSQL is the main advantage of Mamonsu. In this case, the maximum number of connections is equal to the maximum number of databases to which it connects.
  • Extensibility… Mamonsu is a completely plugin agent, and thanks to the fixed structure of each plugin and the relative simplicity of Python, it is easy to learn how to write new or edit standard plugins, ie metric collection parameters.
  • Wide coverage of metrics for monitoring for PotgreSQL including metrics for specific extensions.
  • Quick start, out of the box availability
  • Uploading templates and configuration filesas well as uploading to Zabbix Server.
  • Cross-platform, which is important for our customers who use various Linux distributions, including domestic ones.
  • BSD-clause license

At the moment we offer a lot of plugins and in each new version we try to add something new.

  • 14 plugins for PostgreSQL,
  • 8 plugins for OS Linux,
  • 4 plugins for OS Windows.

Mamonsu collects over 110 PostgreSQL metrics and operating systems:

  • 70 PostgreSQL metrics,
  • 40 OS Linux metrics,
  • 8 Windows OS metrics.

Key metrics include DBMS availability, number of connections, database size, checkpoints, read / write speed, locks, number of autovacuum processes, and WAL generation rate. A complete list of available metrics, as well as a detailed description of all instruments are available in repositories on GitHub.

Monitoring_PostgreSQL_Darja_Vilkova-10

List of available metrics on GitHub

Launch Mamonsu in 5 minutes

To set up PostgreSQL and operating system monitoring with Mamonsu, you can take 5 minutes by following 5 simple steps.

  1. Installing Mamonsu… Mamonsu can be built from source or by using available packages.

$ git clone ... && cd mamonsu && python setup.py

build && python setup.py install

  1. Setting up connections… It is necessary to register the connection parameters for PostgreSQL and Zabbix Server in the agent.conf file.

/etc/mamonsu/agent.conf

  1. Exporting a template to Zabbix Server

$ mamonsu zabbix template export

/usr/share/mamonsu/example.xml

  1. Adding host to Zabbix Server… The exported template will be automatically connected to the new host on Zabbix Server.

$ mamonsu zabbix host create mamonsu-demo

  1. Running

$ service mamonsu start

Mamonsu development directions

As part of the development of Mamonsu, we plan to refine the metrics and create new plugins, for example, a plugin for monitoring the size of individual tables. We also plan to improve and create additional tools, as well as expand the autotuning capabilities through the command mamonsu tune

PostgreSQL monitoring module as part of Zabbix Agent 2

A fast and popular driver is used to connect to PostgreSQL pgx (PG driver and toolkit for Go).

So far we are using two interfaces: Exporter, which calls the handler by key, and Configurator Zabbix Agent 2, which reads and checks the parameters for connecting to the server specified in the configuration file.

We tried to optimize the DBMS performance by grouping metrics and using a handler for metrics and metric groups, as well as using metric groups in JSON as dependency items, and low-level discovery rules.

Key features

  • maintaining a persistent connection to PostgreSQL between checks;
  • support for flexible polling intervals;
  • compatibility with PostgreSQL versions starting from 10 and Zabbix Server starting from version 4.4;
  • the ability to connect and monitor multiple PostgreSQL instances at the same time due to the fact that Zabbix Agent 2 allows you to create multiple sessions.

PostgreSQL connection parameter levels

There are three levels of PostgreSQL connection parameters, i.e. tasks and settings:

  • Global,
  • Sessions,
  • Macros.

  1. Global parameters are set at the agent level, Session and Macros parameters determine the connection parameters to the base.

  2. PostgreSQL connection parameters – Sessions are set in the file zabbix_agent2.conf

Monitoring_PostgreSQL_Darja_Vilkova-18

PostgreSQL connection parameters – Sessions

  • After the keyword Sessions a unique session name is specified, which must be specified in the key (template).
  • Parameters URI and UserName are required for every session.
  • If no database name is specified, the default common database name for all PostgreSQL sessions is used, which is also specified in the configuration file.

  1. Connection parameters to PostgreSQL – Macros are set in the metric key in the template (similar to the method used in Zabbix Agent 1), i.e. they are created in the template and then specified as parameters in the key. In this case, the sequence of macros is fixed, i.e., for example, URI always comes first.

Monitoring_PostgreSQL_Darja_Vilkova-19

PostgreSQL connection options – Macros

The PostgreSQL monitoring module already includes more than 95 metrics that allow you to cover a fairly wide range of PostgreSQL parameters, including:

  • number of connections,
  • volume of databases,
  • archiving wal files,
  • control points,
  • the number of “bloated” tables,
  • replication status,
  • replica lag.

PostgreSQL metrics are not informative without operating system parameters. But Zabbix Agent 2 is already able to collect operating system parameters, so to get a complete picture, we just connect the necessary templates to the host.

Handler

Handler – the main unit of the module, in which the request itself is executed and which allows you to receive metrics.

To get a simple metric:

  1. Create a file to get a new metric:

zabbix / src / go / plugins / postgres / handler_uptime.go

  1. We connect the package and specify the unique key (s) of the metrics:

Monitoring_PostgreSQL_Darja_Vilkova-23

  1. We create a handler with a request, that is, we initiate a variable containing the result:

Monitoring_PostgreSQL_Darja_Vilkova-24

  1. We execute the request:

Monitoring_PostgreSQL_Darja_Vilkova-25

It is necessary to check the request for errors, after which the result will be picked up by the Zabbix Agent 2 process.

  1. We register the key of the new metric:

Monitoring_PostgreSQL_Darja_Vilkova-26

After registering the metric, you can rebuild the agent with the new metric.

The module is available since Zabbix 5.0 on the site https://www.zabbix.com/download… In this version of Zabbix, parameters are set separately via host and port. In Zabbix 5.0.2, which will be released soon, connection parameters will be bundled into one URI.

Thanks for your attention!

useful links

GitHub Mamonsu

Mamonsu Documentation

Zabbix Git

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *