Плагин PostgreSQL

Мониторинг серверов баз данных PostgreSQL с подробными метриками, отслеживающими соединения, транзакции, блокировки, производительность кэша, производительность запросов и лаг репликации.

Обзор

Плагин PostgreSQL собирает детальные метрики с серверов PostgreSQL, включая:

• Статистика соединений - Всего соединений, активные/простаивающие состояния, лимиты соединений
• Статистика транзакций - Коммиты, откаты, здоровье транзакций
• Производительность кэша - Коэффициент попаданий в буферный кэш
• Статистика блокировок - Всего блокировок, deadlock'и
• Производительность запросов - Число медленных запросов (запросы > 5 секунд)
• Репликация - Лаг репликации для standby-серверов
• Детали по базам данных - Статистика по каждой базе данных, включая размер, соединения и операции с кортежами

Требования

Версия PostgreSQL

• Минимум: PostgreSQL 10
• Рекомендуется: PostgreSQL 12 или новее
• Протестировано с: PostgreSQL 10, 11, 12, 13, 14, 15, 16

Зависимости Python

pip install psycopg2-binary>=2.9.0

Устанавливается автоматически при использовании PLUGINS=postgresql во время установки агента.

Сетевой доступ

Агент должен иметь возможность подключиться к PostgreSQL:

# Test connectivity
psql -h localhost -U monitor -d postgres -c "SELECT 1"

Конфигурация

Базовая конфигурация

plugins:
  postgresql:
    enabled: true
    host: localhost
    port: 5432
    user: monitor
    password: 'monitoring-password'
    database: postgres

С SSL/TLS

plugins:
  postgresql:
    enabled: true
    host: postgres.example.com
    port: 5432
    user: monitor
    password: 'secure-password'
    database: postgres
    ssl_mode: require  # Options: disable, require, verify-ca, verify-full

Удалённый сервер PostgreSQL

plugins:
  postgresql:
    enabled: true
    host: postgres-server.example.com
    port: 5432
    user: monitor
    password: 'secure-password'
    database: postgres

Все параметры конфигурации

plugins:
  postgresql:
    enabled: true              # Enable/disable plugin
    host: localhost            # PostgreSQL hostname or IP
    port: 5432                 # PostgreSQL port (default: 5432)
    user: monitor              # PostgreSQL username
    password: ''               # PostgreSQL password
    database: postgres         # Database to connect to
    ssl_mode: disable          # SSL mode: disable, allow, prefer, require, verify-ca, verify-full
    connect_timeout: 10        # Connection timeout in seconds
    application_name: statusradar-agent  # Application name in pg_stat_activity

Переменные окружения

Конфигурацию можно переопределить переменными окружения:

export POSTGRESQL_HOST="localhost"
export POSTGRESQL_PORT="5432"
export POSTGRESQL_USER="monitor"
export POSTGRESQL_PASSWORD="secret"
export POSTGRESQL_DATABASE="postgres"
export POSTGRESQL_SSL_MODE="disable"

Настройка PostgreSQL

Создание пользователя мониторинга

Для PostgreSQL 10+:

CREATE USER monitor WITH PASSWORD 'your-secure-password';
GRANT pg_monitor TO monitor;

Выдача доступа к конкретной базе данных:

\c postgres
GRANT CONNECT ON DATABASE postgres TO monitor;

Необходимые привилегии

Рекомендуется: роль pg_monitor

Роль pg_monitor предоставляет доступ только для чтения ко всем представлениям мониторинга:

GRANT pg_monitor TO monitor;

Эта роль включает:

• pg_read_all_stats - Чтение всех представлений pgstat*
• pg_read_all_settings - Чтение всех параметров конфигурации
• pg_stat_scan_tables - Чтение статистики таблиц и индексов

Ручная выдача прав (если pg_monitor недоступна):

GRANT pg_read_all_stats TO monitor;
GRANT pg_read_all_settings TO monitor;

Настройка pg_hba.conf

Разрешите пользователю мониторинга подключаться:

Для локальных соединений:

# TYPE  DATABASE    USER      ADDRESS         METHOD
host    postgres    monitor   127.0.0.1/32    md5
host    postgres    monitor   ::1/128         md5

Для удалённых соединений:

# TYPE  DATABASE    USER      ADDRESS         METHOD
host    postgres    monitor   10.0.0.0/8      md5

Перезагрузите PostgreSQL:

sudo systemctl reload postgresql

Проверка прав пользователя

-- Check user roles
\du monitor

-- Check database access
SELECT datname FROM pg_database WHERE has_database_privilege('monitor', datname, 'CONNECT');

-- Test stats access
SELECT * FROM pg_stat_database LIMIT 1;

Собираемые метрики

Статистика соединений

Утилизация соединений:

utilization = connections_total / max_connections

Тревожные признаки:

• connections_idle_in_transaction > 10 - Утечка соединений или длинные транзакции
• utilization > 0.8 - Нужно увеличить max_connections

Статистика транзакций

Здоровье транзакций:

commit_ratio = transactions_commits / (transactions_commits + transactions_rollbacks)

Здоровое значение: > 0.95 (95%)

Производительность кэша

Расчёт:

cache_hit_rate = 100.0 * buffer_hits / (disk_reads + buffer_hits)

Здоровое значение: > 99%

Статистика блокировок

Мониторинг:

• Внезапный скачок блокировок = конкуренция за блокировки
• deadlocks_total > 0 = исследуйте паттерны запросов

Производительность запросов

Тревожные признаки:

• slow_queries_count > 10 - Нужна оптимизация запросов
• Внезапный скачок - проверьте долго выполняющиеся запросы

Статистика репликации (только standby)

Примечание: Доступно только на standby-серверах, работающих в режиме восстановления.

Порог предупреждения:

• replication_lag_seconds > 60 - Репликация отстаёт

Информация о сервере

Детали по базам данных (массив)

Каждый объект базы данных содержит:

• datname - Имя базы данных
• connections - Активные соединения
• commits - Закоммиченные транзакции
• rollbacks - Откаченные транзакции
• disk_reads - Блоки, прочитанные с диска
• buffer_hits - Блоки, прочитанные из кэша
• tuples_returned - Строки, возвращённые запросами
• tuples_fetched - Строки, извлечённые запросами
• tuples_inserted - Вставленные строки
• tuples_updated - Обновлённые строки
• tuples_deleted - Удалённые строки
• conflicts - Конфликты запросов (только standby)
• deadlocks - Количество deadlock'ов
• database_size_bytes - Размер базы данных в байтах

Примечание: Ограничено топ-10 базами данных по числу соединений, исключая шаблонные базы данных.

Метрики дашборда

Дашборд StatusRadar отображает:

Карточка обзора

• Status - Сервер PostgreSQL работает/не работает
• Version - Версия PostgreSQL
• Connections - Всего активных соединений
• Cache Hit Rate - Процент попаданий в буферный кэш

График соединений

• Всего соединений (connections_total)
• Активные соединения (connections_active)
• Простаивающие соединения (connections_idle)
• Idle in transaction (connections_idle_in_transaction)
• Лимит максимальных соединений (max_connections)

График транзакций

• Всего коммитов (transactions_commits)
• Всего откатов (transactions_rollbacks)
• Процент commit ratio

Производительность кэша

• Процент попаданий в кэш (cache_hit_rate)

Статистика блокировок

• Всего блокировок (locks_total)
• Всего deadlock'ов (deadlocks_total)

Производительность запросов

• Число медленных запросов (slow_queries_count)

Статус репликации (только standby)

• Лаг репликации в секундах (replication_lag_seconds)

Детали по базам данных

• Статистика по каждой базе данных из массива databases
• Размеры баз данных
• Число транзакций по каждой базе данных
• Операции с кортежами по каждой базе данных

Установка

Быстрая установка

PLUGINS='postgresql' \
POSTGRESQL_USER='monitor' \
POSTGRESQL_PASSWORD='your-password' \
TOKEN='your-agent-token' \
bash -c "$(curl -sL https://statusradar.dev/install-agent.sh)"

Установка на существующий агент

1. Создайте пользователя PostgreSQL:

   CREATE USER monitor WITH PASSWORD 'secure-password';
   GRANT pg_monitor TO monitor;

2. Настройте pg_hba.conf:

   sudo nano /etc/postgresql/15/main/pg_hba.conf

   Добавьте:

   host    postgres    monitor    127.0.0.1/32    md5

   Перезагрузите:

   sudo systemctl reload postgresql

3. Установите зависимость Python:

   cd /opt/statusradar
   source venv/bin/activate  # If using venv
   pip install psycopg2-binary

4. Включите плагин в конфигурации:

   sudo nano /opt/statusradar/config/agent.yaml

   Добавьте:

   plugins:
     postgresql:
       enabled: true
       host: localhost
       port: 5432
       user: monitor
       password: 'secure-password'
       database: postgres

5. Перезапустите агент:

   sudo systemctl restart statusradar-agent

6. Проверьте:

   sudo journalctl -u statusradar-agent -n 50 --no-pager | grep postgresql

   Ожидаемо:

   INFO: Plugin postgresql: Metrics collected successfully

Тестирование

Ручной тест плагина

cd /opt/statusradar
python3 plugins/postgresql_plugin.py

Ожидаемый вывод:

Plugin: postgresql
Enabled: True
Available: True

Collecting metrics...
{
  "connections_total": 15,
  "transactions_commits": 45000,
  "transactions_rollbacks": 100,
  "deadlocks_total": 5,
  "cache_hit_rate": 99.5,
  "connections_active": 12,
  "connections_idle": 3,
  "connections_idle_in_transaction": 0,
  "max_connections": 100,
  "locks_total": 25,
  "slow_queries_count": 2,
  "version": "PostgreSQL 15.4 on x86_64-pc-linux-gnu...",
  "databases": [
    {
      "datname": "postgres",
      "connections": 10,
      "commits": 30000,
      "rollbacks": 50,
      "disk_reads": 1000,
      "buffer_hits": 300000,
      "tuples_returned": 500000,
      "tuples_fetched": 400000,
      "tuples_inserted": 5000,
      "tuples_updated": 3000,
      "tuples_deleted": 100,
      "conflicts": 0,
      "deadlocks": 2,
      "database_size_bytes": 104857600
    }
  ]
}

Тест подключения к PostgreSQL

# Basic connection test
psql -h localhost -U monitor -d postgres -c "SELECT 1"

# Test with password from environment
PGPASSWORD='your-password' psql -h localhost -U monitor -d postgres -c "SELECT 1"

# Remote server
psql -h postgres.example.com -U monitor -d postgres -c "SELECT 1"

# Check permissions
psql -h localhost -U monitor -d postgres -c "SELECT * FROM pg_stat_database LIMIT 1"

Тест запросов статистики

# Test database stats
psql -U monitor -d postgres -c "SELECT * FROM pg_stat_database WHERE datname = 'postgres'"

# Test connection stats
psql -U monitor -d postgres -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state"

# Test lock stats
psql -U monitor -d postgres -c "SELECT mode, count(*) FROM pg_locks GROUP BY mode"

# Test replication stats (primary)
psql -U monitor -d postgres -c "SELECT * FROM pg_stat_replication"

Устранение неполадок

Плагин не собирает метрики

Проверка 1: Запущен ли PostgreSQL?

sudo systemctl status postgresql

Проверка 2: Может ли агент подключиться?

psql -h localhost -U monitor -d postgres -c "SELECT 1"

Проверка 3: Верны ли учётные данные?

# Check config
cat /opt/statusradar/config/agent.yaml | grep -A10 postgresql

Проверка 4: Установлен ли Python-пакет?

python3 -c "import psycopg2; print(psycopg2.__version__)"

Проверка 5: Проверьте логи агента

sudo journalctl -u statusradar-agent -n 100 --no-pager | grep postgresql

Распространённые ошибки

"Connection refused"

Ошибка:

ERROR: Plugin postgresql: could not connect to server: Connection refused

Причины:

1. PostgreSQL не запущен
2. Неверный host/port
3. PostgreSQL не слушает на ожидаемом интерфейсе

Решение:

# Check if PostgreSQL is running
sudo systemctl status postgresql

# Check PostgreSQL port
sudo netstat -tlnp | grep 5432

# Check listen_addresses
sudo -u postgres psql -c "SHOW listen_addresses"

# If listen_addresses = 'localhost', can only connect from localhost
# To allow remote connections, edit postgresql.conf:
# listen_addresses = '*'

"Password authentication failed"

Ошибка:

ERROR: Plugin postgresql: password authentication failed for user "monitor"

Причины:

1. Неверный пароль в конфигурации
2. Пользователь не существует
3. pg_hba.conf не разрешает соединение

Решение:

-- Check if user exists
\du monitor

-- Reset password
ALTER USER monitor WITH PASSWORD 'new-secure-password';

Обновите pg_hba.conf:

host    postgres    monitor    127.0.0.1/32    md5

Перезагрузите:

sudo systemctl reload postgresql

"Permission denied for table"

Ошибка:

ERROR: Plugin postgresql: permission denied for table pg_stat_database

Причина: У пользователя нет роли pg_monitor или pg_read_all_stats

Решение:

GRANT pg_monitor TO monitor;
-- or
GRANT pg_read_all_stats TO monitor;

"No module named 'psycopg2'"

Ошибка:

ERROR: No module named 'psycopg2'

Решение:

pip install psycopg2-binary
# Or if using venv:
cd /opt/statusradar && source venv/bin/activate && pip install psycopg2-binary

"SSL connection has been closed unexpectedly"

Ошибка:

ERROR: SSL connection has been closed unexpectedly

Причина: Несоответствие режима SSL

Решение:

Установите подходящий режим SSL:

plugins:
  postgresql:
    ssl_mode: disable  # or require, verify-ca, verify-full

Влияние на производительность

На PostgreSQL

Незначительное влияние:

• Плагин опрашивает представления pgstat* (статистика в памяти)
• Без сканирования таблиц, без блокировок пользовательских таблиц
• Запросы выполняются за < 10 мс
• Операции только для чтения

Бенчмарк:

• TPS PostgreSQL с мониторингом: 5,000+ TPS
• TPS PostgreSQL без мониторинга: 5,000+ TPS
• Накладные расходы: < 0.01%

На агент

Использование ресурсов:

• Память: +15 MB
• CPU: +3% во время сбора (0.2-0.5 секунды)
• Сеть: +2 KB за сбор

Сценарии использования

1. Мониторинг транзакций

Отслеживайте:

• Всего коммитов (transactions_commits)
• Всего откатов (transactions_rollbacks)
• Commit ratio

Настройте оповещения на:

• Commit ratio < 95%
• Внезапный скачок откатов

Формула:

commit_ratio = transactions_commits / (transactions_commits + transactions_rollbacks)

2. Управление пулом соединений

Отслеживайте:

• Всего соединений (connections_total)
• Активные соединения (connections_active)
• Простаивающие соединения (connections_idle)
• Idle in transaction (connections_idle_in_transaction)
• Лимит максимальных соединений (max_connections)

Настройте оповещения на:

• connections_total > max_connections * 0.8 - Приближение к лимиту
• connections_idle_in_transaction > 10 - Утечки соединений
• connections_total >= max_connections - Нет свободных слотов

3. Оптимизация коэффициента попаданий в кэш

Отслеживайте:

• Hit rate кэша (cache_hit_rate)

Настройте оповещения на:

• cache_hit_rate < 99% - Нужно увеличить shared_buffers

Здоровое значение: > 99%

4. Анализ конкуренции за блокировки

Отслеживайте:

• Всего блокировок (locks_total)
• Всего deadlock'ов (deadlocks_total)

Настройте оповещения на:

• Рост deadlocks_total - Проблемы с паттернами запросов
• Внезапный скачок locks_total - Конкуренция за блокировки

5. Мониторинг производительности запросов

Отслеживайте:

• Число медленных запросов (slow_queries_count)

Настройте оповещения на:

• slow_queries_count > 10 - Много медленных запросов
• Устойчиво высокое число медленных запросов - Нужна оптимизация

Примечание: Медленными считаются запросы, выполняющиеся > 5 секунд.

6. Мониторинг лага репликации (standby-серверы)

Отслеживайте:

• Лаг репликации (replication_lag_seconds)

Настройте оповещения на:

• replication_lag_seconds > 60 - Отставание от primary
• replication_lag_seconds > 300 - Критический лаг

Примечание: Доступно только на standby-серверах в режиме восстановления.

Лучшие практики

1. Используйте роль pg_monitor

Рекомендуется:

CREATE USER monitor WITH PASSWORD 'secure-password';
GRANT pg_monitor TO monitor;

Это предоставляет все необходимые права только для чтения для мониторинга.

2. Следите за ключевыми показателями производительности

Важнейшие метрики:

• Hit rate кэша (cache_hit_rate) - Должен быть > 99%
• Число соединений (connections_total) - Должно быть < 80% от max_connections
• Idle in transaction (connections_idle_in_transaction) - Должно быть близко к 0
• Медленные запросы (slow_queries_count) - Должно быть низким
• Лаг репликации (replication_lag_seconds) - Должен быть < 60 секунд для standby

3. Настройте подходящие оповещения

Оповещения по соединениям:

connections_total > max_connections * 0.8
connections_idle_in_transaction > 10
connections_total >= max_connections

Оповещения по производительности:

cache_hit_rate < 99
deadlocks_total increasing
slow_queries_count > 10
replication_lag_seconds > 60 (standbys only)

4. Оптимизируйте Shared Buffers

Практическое правило:

• shared_buffers = 25% ОЗУ (для выделенного сервера БД)
• Следите за cache_hit_rate для проверки эффективности
• Корректируйте в зависимости от нагрузки
• Цель: cache_hit_rate > 99%

5. Защитите конфигурацию

# Protect agent config
chmod 600 /opt/statusradar/config/agent.yaml

# Use strong password
openssl rand -base64 32

Расширенная конфигурация

Подключение через Unix-сокет

plugins:
  postgresql:
    enabled: true
    unix_socket: '/var/run/postgresql'
    user: monitor
    password: 'password'
    database: postgres

Несколько баз данных

Сейчас мониторит одну базу данных на экземпляр агента. Чтобы мониторить несколько баз данных:

Вариант 1: Опрашивайте все базы данных через pg_stat_database

• Плагин автоматически собирает статистику по всем базам данных
• Дашборд показывает агрегированные метрики

Вариант 2: Несколько агентов

• Установите отдельный агент для каждой критичной базы данных
• Обеспечивает изолированный мониторинг

SSL с клиентскими сертификатами

plugins:
  postgresql:
    enabled: true
    host: postgres.example.com
    port: 5432
    user: monitor
    database: postgres
    ssl_mode: verify-full
    ssl_cert: '/path/to/client-cert.pem'
    ssl_key: '/path/to/client-key.pem'
    ssl_ca: '/path/to/ca-cert.pem'

Примеры конфигураций

Сервер разработки

plugins:
  postgresql:
    enabled: true
    host: localhost
    port: 5432
    user: monitor
    password: 'dev-password'
    database: postgres

Продакшен-primary

plugins:
  postgresql:
    enabled: true
    host: localhost
    port: 5432
    user: monitor
    password: 'strong-production-password'
    database: postgres
    connect_timeout: 10

Продакшен-standby

plugins:
  postgresql:
    enabled: true
    host: localhost
    port: 5432
    user: monitor
    password: 'strong-production-password'
    database: postgres
    # Plugin automatically detects standby status

Удалённый PostgreSQL (SSL)

plugins:
  postgresql:
    enabled: true
    host: postgres-prod.example.com
    port: 5432
    user: monitor
    password: 'secure-password'
    database: postgres
    ssl_mode: require
    connect_timeout: 15

Дальнейшие шаги

• Просмотреть все плагины
• Плагин MySQL
• Плагин MongoDB
• Плагин Redis
• Руководство по настройке агента