Zabbix мониторинг postgresql на windows

Картинка с сайта: habr.com

PostgreSQL это современная, динамично развивающаяся СУБД с очень большим набором возможностей которые позволяют решать самый широкий спектр задач. Использование PostgreSQL как правило относится к очень критичному сегменту ИТ инфраструктуры который связан с обработкой и хранением данных. Учитывая особое место СУБД в инфраструктуре и степень критичности возложенных на нее задач, возникает вопрос мониторинга и надлежащего контроля за работой СУБД. В этом плане PostgreSQL обладает широкими внутренними средствами сбора и хранения статистики. Собираемая статистика позволяет получить довольно подробную картину о том что происходит под капотом в процессе функционирования СУБД. Эта статистика хранится в специальных системных таблицах-представлениях и постоянно обновляется. Выполняя обычные SQL запросы в эти таблицы можно получать разнообразные данные о базах, таблицах, индексах и других подсистемах СУБД.
Ниже я описываю способ и средства для мониторинга PostgreSQL в системе мониторинга Zabbix. Мне нравится эта система мониторинга поскольку предоставляет широкие возможности для реализации самого кастомного мониторинга самых разных систем и процессов.

Мониторинг будет строиться на основе SQL запросов к таблицам статистики. Сами запросы оформлены в виде дополнительного конфигурационного файла к zabbix агенту, в котором SQL запросы обернуты в т.н. UserParameters — пользовательские параметры мониторинга. Пользовательский параметр в Zabbix это отличный способ который позволяет настраивать мониторинг для нестандартных вещей, такими вещами в нашем случае будут параметры работы PostgreSQL. Каждый пользовательский параметр состоит из двух элементов: Название ключа и Команда. Название ключа представляет собой уникальное имя которое не пересекается с другими именами ключей. Команда — это собственно команда-действие которое должен выполнить агент zabbix. В расширенном варианте этой команде могут быть переданы различные параметры. В конфигурации заббикса, это выглядит так:

UserParameter=custom.simple.key,/usr/local/bin/simple-script
UserParameter=custom.ext.key[*],/usr/local/bin/ext-script $1 $2

Таким образом, все запросы к статистике PostgreSQL представляют собой запросы клиента psql обернутые в пользовательские параметры.

Сильные стороны:

• минимальные требования к конфигурированию наблюдаемого узла — в самом простом случае добавляем конфиг и перезапускаем zabbix агент (сложный случай предполагает донастройку прав доступа к PostgreSQL);
• настройка параметров подключения к PostgreSQL, как и пороговые значения для триггеров выполняются через переменные-макросы в веб-интерфейсе — таким образом не нужно залазить в триггеры и плодить шаблон на случай разных пороговых значений для разных хостов (макросы можно назначить на хост);
• широкий спектр собираемых данных (соединения, время транзакций, статистика по базам и таблицам, потоковая репликация и пр.);
• низкоуровневое обнаружение для баз данных, таблиц и серверов stand-by.

Слабые стороны:

• очень много наблюдаемых параметров, возможно кто-то захочет что-то отключить.
• в зависимости от версии PostgreSQL какие-то вещи не будут работать. В частности, это касается мониторинга репликации, т.к. некоторых функций попросту нет в старых версиях. Писалось с оглядкой на версии 9.2 и выше.
• также для некоторых вещей требуется наличие установленных расширений pg_stat_statements и pg_buffercache — если расширения не установлены часть параметров будет недоступна для наблюдения.

Возможности мониторинга:

• информация по объему выделяемых и записываемых буферах, чекпоинтах и времени записи в процессе чекпойинтов — pg_stat_bgwriter
• общая информация по shared buffers — здесь требуется расширение pg_buffercache. Также хочу отметить что запросы к этой статистике являются ресурсозатратными, что отражено в документации к расширению, поэтому в зависимости от потребностей можно либо увеличить интервал опроса, либо выключить параметры совсем.
• общая информация по сервису — аптайм, время ответа, cache hit ratio, среднее время запроса.
• информация по клиентским соединениям и времени выполнения запросов/транзакций — pg_stat_activity.
• размер баз данных и суммарная статистика по всем базам (коммиты/роллбэки, чтение/запись, временные файлы) — pg_stat_database
• потабличная статистика (чтение/запись, кол-во служебных задач такие как vacuum/analyze) — pg_stat_user_tables, pg_statio_user_tables.
• информация по потоковой репликации (статус сервера, количество реплик, лаг с ними) — pg_stat_replication
• прочие штуки (кол-во строк в таблице, существование триггера, параметры конфигурации, WAL журналы)

Дополнительно стоит отметить, что для сбора статистических данных необходимо включить следующие параметры в postgresql.conf:
track_activities — включает трекинг команд (queries/statements) всеми клиентскими процессами;
track_counts — включает сбор статистики по таблицам и индексам;

Установка и настройка.
Все необходимое для настройки находится в Github репозитории.

# git clone https://github.com/lesovsky/zabbix-extensions/
# cp zabbix-extensions/files/postgresql/postgresql.conf /etc/zabbix/zabbix_agentd.d/

Дальше следует отметить что для выполнения запросов со стороны агента, необходимо чтобы в pg_hba конфигурации был определен соответствующий доступ — агент должен иметь возможность устаналивать соединения с postgres сервисом в целевую базу и исполнять запросы. В самом простом случае нужно добавить следующую строку в pg_hba.conf (для разных дистрибутив расположение файла может отличаться) — разрешаем подключения от имени postgres к базе mydb с localhost.

host 	mydb	postgres 	127.0.0.1/32  	trust

Незабываем, что после изменения pg_hba.conf сервису postgresql нужно сделать reload (pg_ctl reload). Однако это самый простой вариант и не совсем безопасный, поэтому если вы хотите использовать парольную или более сложную схему доступа, то еще раз внимательно ознакомьтесь с pg_hba и .pgpass.

Итак файл конфигурации скопирован, осталось подгрузить его в основную конфигурацию, убедитесь что в основном файле конфигурации агента, есть строка Include c указанным там путем где размещены дополнительные файлы конфигурации. Теперь перезапускаем агента, после чего можем проверить работу выполнением самой простой проверки — используем pgsql.ping и в квадратных скобках указываем опции подключения к postgres, которые будет переданы psql клиенту.

# systemctl restart zabbix-agent.service
# zabbix-get -s 127.0.0.1 -k pgsql.ping['-h 127.0.0.1 -p 5432 -U postgres -d mydb']

Если вы правильно прописали доступ, то вам вернется время ответа сервиса в милисекундах. Если вернулась пустая строка, то тут проблема с доступом в pg_hba. Если вернулась строка ZBX_NOTSUPPORTED — конфигурация не подгрузилась, проверяйте конфиг агента, пути в Include и выставленные на конфиг права.

Когда команда проверки возвращает правильный ответ, остается скачать шаблон и загрузить его в веб интерфейс и назначить на целевой хост. Скачать шаблон можно также из репозитория (postgresql-extended-template.xml). После импорта нужно перейти на вкладку настройки макросов шаблона и настроить их.

Картинка с сайта: habr.com

Ниже список и краткое описание:

• PG_CONNINFO — это параметры подключения который будут переданы клиенту psql при выполнении запроса. Это самый важный макрос т.к. определяет параметры подключения к postgres сервису. Строка указанная по умолчанию является более-менее универсальной для любых случаев, однако если у вас несколько серверов и у каждого сервера отличные от других настройки, то у хостов в заббиксе можно определить макрос с таким же именем и задать ему индивидуальное значение. При выполнении проверки макрос хоста имеет приоритет перед макросом шаблона.
• PG_CONNINFO_STANDBY — это параметры подключения который будут переданы утилите psql при выполнении запроса на stanby сервера (определение лага репликации).
• PG_CACHE_HIT_RATIO — это пороговое значение для триггера на процент успешного попадания в кэш; сработает триггер если процент попадания будет ниже этой отметки;
• PG_CHECKPOINTS_REQ_THRESHOLD — пороговое значение для чекпойнтов по требованию
• PG_CONFLICTS_THRESHOLD — пороговое значение для конфликтов возникших при выполнении запросов на серверах standby;
• PG_CONN_IDLE_IN_TRANSACTION — пороговое значение для соединений которые открыли транзакцию и ничего при этом не делают (плохие транзакции);
• PG_CONN_TOTAL_PCT — пороговое значение для процента открытых соединений к максимально возможному числу соединений (если 100%, то всё, коннекты кончились);
• PG_CONN_WAITING — пороговое значение для заблокированных запросов которые ждут завершения других запросов;
• PG_DATABASE_SIZE_THRESHOLD — пороговое значение для размера БД;
• PG_DEADLOCKS_THRESHOLD — пороговое значение для дедлоков (к счастью они разрешаются автоматически, но об их наличии желательно быть в курсе, т.к. это прямое свидетельство некачественно написанного кода);
• PG_LONG_QUERY_THRESHOLD — пороговое значение для времени выполнения запросов; сработает триггер если будут запросы чье время выполнения больше этой отметки;
• PG_PING_THRESHOLD_MS — пороговое значение для времени ответа сервиса;
• PG_SR_LAG_BYTE — пороговое значение для лага репликации в байтах;
• PG_SR_LAG_SEC — пороговое значение для лага репликации в секундах;
• PG_UPTIME_THRESHOLD — пороговое значение аптайма, если аптайм ниже отметки значит сервис перезапустили;

Из текста триггеров должно стать понятно для чего нужны эти пороговые значения:

• PostgreSQL active transaction to long — зафиксирована длинная транзакция или запрос;
• PostgreSQL cache hit ratio too low — слишком низкий процент попадания в кэш;
• PostgreSQL deadlock occured — зафиксирован дедлок;
• PostgreSQL idle in transaction connections to high — много коннектов в состоянии idle in transaction;
• PostgreSQL idle transaction to long — зафиксирована длинная транзакция в состоянии idel in transaction;
• PostgreSQL number of waiting connections to high — зафиксированн запрос или транзакция в состоянии ожидания;
• PostgreSQL recovery conflict occured — обнаружен конфликт при восстановлении на реплике;
• PostgreSQL required checkpoints occurs to frequently — чекпоинты случаются слишком часто;
• PostgreSQL response to long — долгое время ответа;
• PostgreSQL service not running — сервис не запущен;
• PostgreSQL service was restarted — сервис перезапустился;
• PostgreSQL total number of connections to high — общее число коннектов слишком большое и приближается к max_connections;
• PostgreSQL waiting transaction to long — зафиксирована слишком длинный запрос или транзакция в состоянии ожидания;
• PostgreSQL database {#DBNAME} to large — размер БД слишком большой;
• PostgreSQL streaming lag between {HOSTNAME} and {#HOTSTANDBY} to high — лаг репликации между серверами слишком большой.

Правила низкоуровневого обнаружения

• PostgreSQL databases discovery — обнаружение имеющихся БД с возможностью фильтрации по регулярным выражениям. При обнаружении добавляется график о размерах;
• PostgreSQL database tables discovery — обнаружение имеющихся таблиц в наблюдаемой базе с возможностью фильтрации по регулярным выражениям. Будьте внимательны с фильтром и добавляйте только те таблицы что вам реально интересны, т.к. это правило порождает 21 параметр на каждую найденную таблицу. При обнаружении добавляются графики о размерах, сканированиях, изменении строк и статистике по чтению.
• PostgreSQL streaming stand-by discovery — обнаружение подключенных реплик. При обнаружении добавляется график с лагом репликации.

Доступные графики, если говорить про графики, то я постарался сгруппировать наблюдаемые параметры, при этом не перегружая графики чрезмерно большим количеством парамтеров. Так информация из pg_stat_user_tables разнесена на 4 графика.

• PostgreSQL bgwriter — общая информация о том что происходит с буфферами (сколько выделено, сколько и каким образом записано).
• PostgreSQL buffers — общая информация по состоянию shared buffers (сколько буфферов, сколько используется, сколько «грязных» буфферов).
• PostgreSQL checkpoints — информация по происходящим чекпоинтам.
• PostgreSQL connections — информация по клиентским соединениям.
• PostgreSQL service responce — время ответа сервиса и среднее время выполнения запросов.
• PostgreSQL summary db stats: block hit/read — чтение из кэша и с диска.
• PostgreSQL summary db stats: events — события в базе (дедлоки, конфликты, коммиты, роллбэки).
• PostgreSQL summary db stats: temp files — информация по временным файлам.
• PostgreSQL summary db stats: tuples — общая информация по изменениям строк.
• PostgreSQL transactions — время выполнения запросов.
• PostgreSQL uptime — аптайм и процент попадания в кэш.
• PostgreSQL write-ahead log — информация по WAL журналу (объем записи и кол-во файлов).
• PostgreSQL: database {#DBNAME} size — информация по изменению размера БД.
• PostgreSQL table {#TABLENAME} maintenance — операции обслуживания таблицы (autovacuum, autoanalyze, vacuum, analyze).
• PostgreSQL table {#TABLENAME} read stats — статистика по чтению из кэша, диска.
• PostgreSQL table {#TABLENAME} rows — изменение в строках.
• PostgreSQL table {#TABLENAME} scans — информация по сканирования (sequential/index scans).
• PostgreSQL table {#TABLENAME} size — информация по размерам таблиц и их индексов.
• PostgreSQL streaming replication lag with {#HOTSTANDBY} — размер лага репликации с серверами-репликами.

В заключении, немного графиков-примеров:
Здесь как мы видим, в базе регулярно создаются временные файлы, следует поискать виновника в логе и пересмотреть work_mem.

Картинка с сайта: habr.com

Здесь события происходящие в базе — коммиты/роллбэки и конфликты/дедлоки — вцелом все хорошо здесь.

Картинка с сайта: habr.com

Здесь состояние потоковой репликации с одним из серверов — время отставания в секундах и байтах.

Картинка с сайта: habr.com

И заключительный график — время отклика службы и среднее время запроса.

Картинка с сайта: habr.com

Вот и все, всем спасибо за внимание!

Last year, the popular monitoring service Zabbix introduced Agent 2, designed to reduce the number of TCP connections and provide convenient extendability through plugins in Golang. In this article, I will discuss the main features of the Zabbix Agent 2 plugin for monitoring PostgreSQL,  give tips on how to configure it, and explain by example how to customize the plugin.

What is the story behind the PostgreSQL monitoring plugin for Zabbix Agent 2?

In 2019 Zabbix announced the release of the new Agent 2. The Zabbix team created it from scratch in Golang. Each application requires a separate monitoring plugin. We at Postgres Professional have been using Zabbix for monitoring PostgreSQL for long years, that is why we decided to invest our experience in developing a PostgreSQL monitoring module for Agent 2.

How does DBMS monitoring work in Zabbix?

Let us start with a short introduction to the Zabbix monitoring strategy for beginners.

Картинка с сайта: postgrespro.com

The structure we are interested in consists of two components:

• Zabbix Server, which stores, visualizes, and processes the collected data.
• Zabbix Agent, which is deployed on a monitoring target to monitor local resources and applications (hard drives, memory, processor statistics etc.).

Zabbix Server requires a template — an XML file — to monitor each application. It contains the metrics’ keys (unique IDs) and their processing parameters.

Zabbix Agent 2 provides the user with a monitoring tool out of the box. It is quick and easy to customize and possesses a good extendability.

So how does PostgreSQL plugin for Zabbix Agent 2 work?

There is the main function from which the handlers with a unique key for each metric are called. The handler collects data. It is a file in which a SQL query is specified and executed to get one or more metrics. A variable of int, float, or string type stores the results of the query execution. If it contains the values of several metrics, it will be converted to JSON. The metrics received by Zabbix Agent 2 are regularly sent to Zabbix Server.

The plugin and the handlers are located in this folder: /plugins/postgres.

Which features does the PostgreSQL monitoring module for Zabbix Agent 2 have?

• A constant connection to PostgreSQL.
• Monitoring for multiple PostgreSQL instances simultaneously.
• Options to monitor and check metrics in real-time via command line.
• One agent configuration file for configuring all plugin parameters.
• Keeping the state between checks.
• Very simple customization of how the existing metrics are collected.
• Ability to write new plugins according to your requirements.

The plugin has an official template, which is accessible here:

https://www.zabbix.com/integrations/postgresql#tab:official2

It has basic triggers and a complex screen that displays a combination of several charts. The plugin collects more than 95 metrics. The full list of all metrics can also be found on the Zabbix website.

In Zabbix Server web-interface you can easily customize the template and its components. What exactly can you set up?

• Change the time interval for how often the desired metrics are collected.
• Add a trigger for the metric.
• Add a macro or edit an existing one.

How to install and use the PostgreSQL plugin for Zabbix Agent 2?

1. Create a PostgreSQL user for monitoring (<password> at your discretion):

   CREATE USER 'zbx_monitor' IDENTIFIED BY '<password>';
   GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text) TO zbx_monitor;
   GRANT EXECUTE ON FUNCTION pg_catalog.pg_stat_file(text) TO zbx_monitor;

1. Edit pg_hba.conf to allow connections from Zabbix agent:

   # TYPE DATABASE USER ADDRESS METHOD
     host all zbx_monitor 127.0.0.1 md5
   

For more information, please read the PostgreSQL documentation here: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html.

Now you need to specify the PostgreSQL connection parameters for Zabbix Agent 2. There are two ways to do this:

• to use macros for connection parameters,
• to create a session.

The first way is a little easier. It can suffice if you need to configure monitoring for one PostgreSQL instance:

1. Set in the {$PG.URI} macro the system data source name of the PostgreSQL instance such as <protocol(host:port)> in the template.
2. Set the macro with your username and password ({$PG.USER} and {$PG.PASSWORD}) in the template. You can also specify the macro {$PG.DATABASE}. This parameter is optional for most metrics. If it is not specified in the key, then the base name specified in the agent configuration file will be used. An example can be seen in Picture 1.

The official template already has all required macros in the parameters of all keys. Please note that the order of the parameters in the key is fixed.

Картинка с сайта: postgrespro.com

The second way enables you to set the connection parameters to several PostgreSQL instances:

1. Set the connection parameters for the session in the configuration file zabbix_agent2.conf in the section of the Postgres plugin: Postgres.Sessions.<Session_name>.URI, Postgres.Sessions.<Session_name>.User, Postgres.Sessions.<Session_name>.Password
2. Instead of the <Session_name>, you should specify a unique name for the new session (as shown in Picture 2).
   

   Картинка с сайта: postgrespro.com

3.  Create a macro with the session name {$PG.<Session_name>} in the template  (as shown in Picture 3).
   

   Картинка с сайта: postgrespro.com

4. Specify the macro as the only parameter for metrics in the template  (as shown in Picture 4).

Картинка с сайта: postgrespro.com

How to add new metrics to existing ones?

Let us explore how to use the plugin to collect more metrics. In our example, we’ll be adding an uptime metric.

To do this, you will need to create a new handler with a query, and then add its key to the main function.

1. Let’s create a file to get a new metric:

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

Import the postgres package and specify the key(s) of the metrics:

package postgres

const (
  keyPostgresUptime = "pgsql.uptime"
)

1. Declare a handler with a query, do the same for the variable to store results:

func (p *Plugin) uptimeHandler(conn *postgresConn, params []string)(interface{}, error) 
{
  var uptime float64
query := `SELECT date_part('epoch', now() - pg_postmaster_start_time());`

1. Execute a query, check if there is an error. If everything is OK,  the uptime variable will be returned with some result.

err := conn.postgresPool.QueryRow(context.Background(), query).Scan(&uptime)
  if err != nil {
 ...
}
return uptime, nil

1. Finally, register a key for the new metric:

func init() {
  plugin.RegisterMetrics(&impl, pluginName, 
  … 
 keyPostgresUptime, "Returns uptime.",
  )
 }

Recompile the agent!

Other Related Resources:

Article by Vadim Ipatov — co-developer of Zabbix Agent 2:

https://blog.zabbix.com/developing-plugins-for-zabbix-agent-2/9682/

Template for our PostgreSQL monitoring plugin:

https://www.zabbix.com/integrations/postgresql#tab:official2

Zabbix Git. It is for those who want to see more real-world examples and have a look at all SQL queries for metrics:

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/postgres

What’s next?

The new version of the above-mentioned plugin for PostgreSQL monitoring will become available soon! It will feature new metric enabling users to collect results of custom queries from separate SQL files. This version will also include minor bug fixes and introduce the module’s architecture update.

Join Postgres Pro & Zabbix for a Meetup!

If you have any questions related to the monitoring plugin for Zabbix Agent 2, you can ask them directly at the upcoming PostgreSQL Monitoring Day with Zabbix & Postgres Pro. I will speak there discussing the enhancements of version 2 of this monitoring plugin. The community event featuring 5 sessions on Postgres monitoring will take place online on December 10, 2020, at 6 PM CET. Hope to see many of you there!

Ok