Sysadminium

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

Введение

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

1. Установка сервера 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.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Tue 2023-06-27 12:56:47 MSK; 2min 7s ago
   Main PID: 3979 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4660)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service

Настроем 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))

2. Установка 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

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

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

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

# 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"

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

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

# 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 недели.

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

Теперь переходим к серверу 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

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

Веб-интерфейс не является строго обязательным, но значительно упрощает работу с настройками 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

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

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

В 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

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

8. Запуск 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
● pgwatch2.service - Pgwatch2 Gathering Daemon
     Loaded: loaded (/etc/systemd/system/pgwatch2.service; disabled; vendor preset: enabled)
     Active: active (running) since Tue 2023-06-27 15:14:12 MSK; 11s ago
   Main PID: 14751 (pgwatch2-daemon)
      Tasks: 6 (limit: 4660)
     Memory: 7.7M
        CPU: 43ms
     CGroup: /system.slice/pgwatch2.service
             └─14751 /usr/bin/pgwatch2-daemon -c /etc/pgwatch2/config/instances.yaml -m /etc/pgwatch2/metrics --datastore=postgres

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

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-server.service - Grafana instance
     Loaded: loaded (/lib/systemd/system/grafana-server.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2023-06-27 17:02:53 MSK; 21h ago
       Docs: http://docs.grafana.org
   Main PID: 19670 (grafana)
      Tasks: 10 (limit: 4660)
     Memory: 85.5M
        CPU: 1min 17.633s
     CGroup: /system.slice/grafana-server.service
             └─19670 /usr/share/grafana/bin/grafana server --config=/etc/grafana/grafana.ini --pidfile=/run/grafana/grafana-server.pid

Проверим работу 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.

В следующей статье постараюсь описать самые важные метрики, за которыми нужно следить.