Установка PGWatch2 на Debian 11

В этой статье разбирается установка системы мониторинга PGWatch2 на операционную систему Debian 11 для мониторинга баз PostgreSQL.
Tags:

Введение

PGWatch2 — это специальная система мониторинга предназначенная исключительно для баз данных PostgreSQL. Она поддерживает мониторинг практически всех показателей для PostgreSQL, начиная с версии с 9.0 и выше. В основе решения лежит демон сборки метрик, написанный на Go.

Документацию по PGWatch2 можете найти  здесь  или на  github .

Существует 2 разных способа разворачивания PGWatch2:

  • push — сборщик метрик ставиться на сервер с PostgreSQL, базы которого мы хотим мониторить. Этот сборщик собирает метрики и передаёт их в базу хранения метрик, которая может находиться на этом же или на отдельном сервере.
  • pull — сборщик метрик устанавливается на сервере мониторинга. В этом случае сборщик метрик подключается по сети к наблюдаемым базам и забирает метрики к себе в локальную базу хранения метрик.

В этой статье мы будем настраивать PGWatch2 по методу pull на операционную систему Debian 11.

То есть на один сервер мы устанавливаем:

  • PGWatch2 (Metrics Collector) — приложение которое будет собирать метрики.
  • PGWatch Web-интерфейс (Config UI Python) — web-интерфейс для настройки PGWatch2. Производить настройки можно и напрямую, работая с базой данных конфигурации. Но web-интерфейс значительно упрощает работу.
  • PostgreSQL — для хранения баз данных:
    • база конфигурации PGWatch2;
    • база хранения метрик;
    • база конфигурации Grafana.
  • Grafana — приложение для отображения графиков.

Установка всех компонентов PGWatch2

Установка сервера PostgreSQL

Следует установить последнюю доступную версию PostgreSQL (обязательно не ниже 11 версии). Мы установим 14 версию из репозиториев Postgres:

# apt install curl ca-certificates gnupg -y
# curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
# echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
# apt update && apt install postgresql-14 -y

Проверим, запустилась ли у нас служба postgresql:

# systemctl status postgresql

Настроем PostgreSQL, чтобы он слушал все интерфейсы по протоколу ipv4 и проверим это с помощью утилиты ss:

# nano /etc/postgresql/14/main/postgresql.conf
listen_addresses = '0.0.0.0'

# systemctl restart postgresql
# ss -ntlp | grep postgres
LISTEN 0      244          0.0.0.0:5432      0.0.0.0:*    users:(("postgres",pid=6398,fd=5))

Установка PGWatch2

Скачаем и установим PGWatch2:

# wget https://github.com/cybertec-postgresql/pgwatch2/releases/download/v1.10.0/pgwatch2_1.10.0_linux_64-bit.deb
# dpkg -i pgwatch2_1.10.0_linux_64-bit.deb

Настроим службу pgwatch2:

# useradd -m -s /bin/bash pgwatch2
# cp /etc/pgwatch2/startup-scripts/pgwatch2.service /etc/systemd/system/
# systemctl daemon-reload

Но пока службу не запускаем, так как у нас не всё для этого подготовлено.

Загрузка базы данных конфигурации

Все настройки и строки подключения к базам данных, которые мы будем мониторить, будут храниться в определённой базе данных, подготовим её. Обратите внимание, что пароль лучше придумать свой, а не использовать из моего примера.

# su - postgres -c psql

> CREATE USER pgwatch2 PASSWORD 'xyz';
> CREATE DATABASE pgwatch2 OWNER pgwatch2;
> exit

$ su - postgres -c "psql -f /etc/pgwatch2/sql/config_store/config_store.sql pgwatch2"
$ su - postgres -c "psql -f /etc/pgwatch2/sql/config_store/metric_definitions.sql pgwatch2"

Загрузка базы данных для хранения метрик

Создадим ещё одну базу данных для хранения метрик:

# su - postgres -c psql

> CREATE DATABASE pgwatch2_metrics OWNER pgwatch2;
> exit

# su - postgres -c "cd /etc/pgwatch2/sql/metric_store/; psql -f roll_out_metric_time.psql pgwatch2_metrics"

Здесь я выбираю схему roll_out_metric_time.psql. Она подойдет для небольшого количества баз (до 100).

Обратите внимание! Хранение метрик по умолчанию составляет 2 недели.

Подготовка баз данных для мониторинга

Теперь переходим к серверу PostgreSQL, базы которого мы собираемся мониторить.

Здесь нужно создать и настроить пользователя для подключения к базе данных, которую мы собираемся мониторить. А также нужно подключить расширение pg_stat_statements и включить track_io_timing.

В конфиге postgresql.conf включим pg_stat_statements и track_io_timing. Обратите внимание, путь к конфигурационному файлу в вашем случае может отличаться, возможно вы используете другую версию сервера PostgreSQL или другой дистрибутив:

# nano /etc/postgresql/12/main/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on

В конфиге pg_hba.conf дадим право пользователю pgwatch2 подключаться по сети к наблюдаемой базе данных. В моём случае база данных называется 1c_base, у вас скорее всего будет называться по другому:

# nano /etc/postgresql/12/main/pg_hba.conf
host   1c_base         pgwatch2    192.168.0.0/24  md5

Теперь подключимся к базе, создадим и настроим пользователя, а также создадим расширение pg_stat_statements. Дополнительно установим лимит подключений для пользователя pgwatch2, на всякий случай.

# su - postgres -c "psql -d 1c_base"

> CREATE EXTENSION pg_stat_statements;
> CREATE ROLE pgwatch2 WITH LOGIN PASSWORD 'xyz';
> ALTER ROLE pgwatch2 CONNECTION LIMIT 3;
> GRANT pg_monitor TO pgwatch2; 
> GRANT CONNECT ON DATABASE "1c_base" TO pgwatch2;
> GRANT USAGE ON SCHEMA public TO pgwatch2;
> GRANT EXECUTE ON FUNCTION pg_stat_file(text) to pgwatch2;
> exit

И перезагрузим службу postgresql:

# systemctl restart postgresql

Проверим подключение:

# psql -h 192.168.0.55 -U pgwatch2 -d 1c_base -W
Password: (xyz)
> exit

Установка и запуск веб-интерфейса

Веб-интерфейс не является строго обязательным, но значительно упрощает работу с настройками PGWatch2.

# apt install python3 python3-pip -y
# cd /etc/pgwatch2/webpy/
# pip3 install -U -r requirements_pg_metrics.txt

Запустим веб-интерфейс с помощью команды:

# su - pgwatch2

$ cd /etc/pgwatch2/webpy/
$ python3 web.py --datastore=postgres --password=xyz --pg-metric-store-conn-str="dbname=pgwatch2_metrics user=pgwatch2"
$ exit

Теперь можем проверить работу web-интерфейса, который работает на 8080 порту:

Убедившись что всё работает, остановим web-интерфейс нажатием Ctr+с. И настроим службу веб-интерфейса:

# cp /etc/pgwatch2/webpy/startup-scripts/pgwatch2-webui.service /etc/systemd/system/
# systemctl daemon-reload

Нужно настроить pg_hba на метод подключения trust, так как этим методом подключается веб интерфейс по localhost:

# nano /etc/postgresql/14/main/pg_hba.conf
host    all             all             127.0.0.1/32            trust

# systemctl restart postgresql

Запустим службу и добавим её в автозагрузку:

# systemctl start pgwatch2-webui
# systemctl enable pgwatch2-webui

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

Подключение баз данных к мониторингу

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

  • Uniq-name — уникальное имя базы которую мы собираемся мониторить (поддерживаются только латинские символы, цифры и знак подчеркивания).
  • DP type — тиб базы, в нашем случае postgres.
  • DB host — ip-адрес сервера PostgreSQL, который мы собираемся мониторить.
  • DB port — порт сервера PostgreSQL (по умолчанию 5432).
  • DB dbname -имя базы данных, которую мы собираемся мониторить.
  • DB user — имя пользователя базы данных (в нашем случае pgwatch2).
  • DB password — пароль пользователя базы данных (в моём примере пароль был xyz).
  • Preset metrics config — набор метрик, которые нужно собирать (можем поставить full).

Отслеживаемые БД, добавляются в таблицу pgwatch2.monitored_db, посмотреть это можно следующим образом:

# su - pgwatch2 -c psql
$ SELECT * from monitored_db;
$ exit

Также можем добавлять базы данных для мониторинга программно, заполняя эту таблицу.

Запуск PGWatch2

Из консоли запустить сборщик метрик можно с помощью такой команды:

# pgwatch2-daemon \
  --host=localhost --user=pgwatch2 --dbname=pgwatch2 \
  --datastore=postgres --pg-metric-store-conn-str=postgresql://pgwatch2@localhost:5432/pgwatch2_metrics \
  --verbose=info

Дальше идём в web-интерфейс и проверяем что на вкладке Stats summary появились значения для нашей базы данных:

Если всё работает, останавливаем сборку метрик с помощью Ctr+c. И настроим службу:

# nano /etc/systemd/system/pgwatch2.service
ExecStart=/usr/bin/pgwatch2-daemon --host=localhost --user=pgwatch2 --dbname=pgwatch2 --datastore=postgres --pg-metric-store-conn-str=postgresql://pgwatch2@localhost:5432/pgwatch2_metrics

Запустим службу и добавляем её в автозапуск:

# systemctl daemon-reload
# systemctl start pgwatch2.service
# systemctl enable pgwatch2.service
# systemctl status pgwatch2.service

Следим, что метрики продолжают поступать!

9. Установка Grafana

Для визуализации полученных данных используется система Grafana.

Создадим базу данных для хранения конфигурации Grafana (пароль можете придумать свой):

# su - postgres -c psql

> CREATE USER pgwatch2_grafana PASSWORD 'xyz';
> CREATE DATABASE pgwatch2_grafana OWNER pgwatch2_grafana;
> exit

Установим Grafana:

# apt install adduser libfontconfig1 -y
# cd
# wget https://dl.grafana.com/oss/release/grafana_10.0.1_amd64.deb
# dpkg -i grafana_10.0.1_amd64.deb
# systemctl daemon-reload
# systemctl enable grafana-server

Перед запуском службы настроим Grafana, редактируя конфиг /etc/grafana/grafana.ini:

# nano /etc/grafana/grafana.ini
[database]
type = postgres
host = localhost:5432
name = pgwatch2_grafana
user = pgwatch2_grafana
password = xyz

[security]
admin_user = admin
admin_password = admin

Запустим Grafana:

# systemctl start grafana-server
# systemctl status grafana-server

Проверим работу Grafana, перейдя в web-интерфейс (порт по умолчанию 3000). Логин и пароль мы задали выше в конфиге. Кстати, после входа вам предложат изменить пароль, что желательно сделать.

Установим базу данных метрик (pgwatch2_metrics) в качестве источника данных по умолчанию. Это делается через web-интерфейс (Administration / Data Sources):

Добавляем панели мониторинга (Dashboards) pgwatch2 в Grafana:

# cd /etc/pgwatch2/grafana-dashboards/
# ./import_all.sh

И наконец откроем Dashboards в Grafana:

Вот пример панели мониторинга DB Overview:

Здесь мы видим:

  • Instance state (статус сервера) — PRIMARY или SLAVE;
  • Instance uptime (uptime сервера);
  • TPS (транзакций в секунду);
  • QPS (запросов в секунду);
  • Query runtime (среднее время выполнения запроса в миллисекундах);
  • DB size ch. 1h (на сколько база выросла или уменьшилась за 1 час);
  • Approx Table Bloat (примерное раздувание талицы);
  • Tuples fetched vs returned (процентное отношение извлеченных данных к передаваемым, если менее 10%, вероятно у вас проблемы с индексами);
  • Tuple ins. / upd. / del. statistics 5m avg (кортежей вставлено / обновлено / удалено в среднем за 5 минут);
  • Shared Buffers hit ratio + Rollback ratio 5m avg (процент попадания в Shared Buffers + соотношение роллбэков);
  • WAL rate + DB size (скорость записи WAL и размер базы данных);
  • Session by state (количество сессий в разных состояниях);
  • CPU load + avg. query runtime (загрузка ЦПУ + среднее время выполнение запроса);
  • Seq. / Idx. scan (количество последовательных чтений и количество сканирований по индексу);
  • Temp bytes (появляется, когда большие группы/сортировки не помещаются в Work Mem).

Итог

Установка системы мониторинга PGWatch2 — это не просто, так как приходится проделывать много ручных операций. Но я надеюсь эта статья поможет вам установить PGWatch2 и настроить мониторинг ваших баз данных PostgreSQL.

Если понравилась статья, подпишись на мой канал в  VK  или  Telegram .