Налаштування моніторингу PostgreSQL (pg_stat_statements, pgBadger)
Уявіть: ви запускаєте нову функціональність, і через годину база починає гальмувати. SELECT'и висять по 30 секунд, а в логах тільки 'checkpoint time: 200ms'. Що сталося? Без моніторингу це ворожіння на кавовій гущі. Давайте розберемо, як налаштувати спостереження за PostgreSQL, щоб бачити проблему до того, як вона стане інцидентом.
Наш досвід показує: PostgreSQL мовчить до останнього. Запити працюють повільно тижнями. База виглядає живою, але SELECT на 10 млн рядків без індексу використовує всю пам'ять. Autovacuum не встигає за bloat. Без правильного моніторингу ці проблеми знаходять себе самі — під час інциденту. Ми налаштовували моніторинг для десятків проектів — від стартапів до enterprise-кластерів з терабайтами даних. За понад 5 років роботи ми реалізували понад 30 проектів, включаючи кластери з реплікацією та шардуванням.
Чому pg_stat_statements недостатньо для аналізу продуктивності?
pg_stat_statements — перше, що вмикають після встановлення PostgreSQL. Розширення агрегує статистику виконання запитів: сумарний час, кількість викликів, rows, shared_blks_hit/read. Вмикається одним рядком у postgresql.conf:
shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all pg_stat_statements.max = 10000 Після CREATE EXTENSION pg_stat_statements у цільовій базі в'юха pg_stat_statements стає основним інструментом пошуку важких запитів. Запит для знаходження топ-10 за сумарним часом:
SELECT round(total_exec_time::numeric, 2) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;Проблема в тому, що pg_stat_statements не аналізує логи — тільки живу статистику в пам'яті. Історичні дані губляться при рестарті. Для аналізу минулих інцидентів потрібен pgBadger. Крім того, pg_stat_statements не показує bloat таблиць, lock waits у розрізі сесій, checkpoint activity. Ці метрики критичні для продуктивності — наприклад, bloat може сягати 40% об'єму таблиці, сповільнюючи послідовне сканування. Документація з pg_stat_statements підтверджує ці обмеження.
Як pgBadger відновлює історію інцидентів?
pgBadger парсить postgresql.log і генерує HTML-звіт з топом повільних запитів, lock waits, checkpoint warnings, тимчасовими файлами. Попередньо потрібно налаштувати правильний формат логів у postgresql.conf:
log_min_duration_statement = 1000 # логувати запити довше 1 секунди
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
lc_messages = 'C' # pgBadger вимагає англійський формат
Генерація звіту:
pgbadger /var/log/postgresql/postgresql-*.log \
-o /var/www/reports/pgbadger_$(date +%Y%m%d).html \
--format stderr \
--jobs 4Звіт показує не просто повільні запити, а паттерни: в який час доби навантаження пікове, які додатки генерують lock waits, скільки тимчасових файлів створюється (ознака нестачі work_mem). На основі звіту можна робити postmortem — розбирати інцидент крок за кроком. pg_stat_statements зберігає дані тільки в пам'яті, а pgBadger — у файлах, дозволяючи зберігати звіти за місяці — у 100 разів більше даних. Репозиторій pgBadger на GitHub містить детальну документацію.
Як postgres_exporter і Grafana забезпечують оперативний моніторинг?
Для онлайн-метрик використовуємо postgres_exporter від Prometheus Community. Він збирає десятки метрик: розміри баз, активні з'єднання, стан реплікації, bloat, час останнього vacuum/analyze.
Конфігурація через змінну оточення:
DATA_SOURCE_NAME="postgresql://monitoring_user:password@localhost:5432/postgres?sslmode=disable" Кастомні запити для queries.yaml дозволяють додати метрики, яких немає в стандартному наборі — наприклад, bloat по таблицях з розширення pgstattuple або lag реплікації по кожному слоту.
У Grafana дашборд ID 9628 (PostgreSQL Database) покриває більшість базових кейсів. Алерти на критичні події:
| Метрика | Поріг алерту | Severity |
|---|---|---|
| pg_up = 0 | негайно | critical |
| Активні з'єднання > 80% від max_connections | 5 хв | warning |
| Replication lag > 30 сек | 2 хв | warning |
| Вік транзакції (wraparound) > 1.5 млрд | одразу | critical |
| Dead tuples > 10% від live tuples | 15 хв | warning |
Wraparound — окрема історія. Якщо age(datfrozenxid) наближається до autovacuum_freeze_max_age (за замовчуванням 200 млн транзакцій), PostgreSQL спочатку сповільнюється, а потім зупиняється повністю і потребує ручного VACUUM FREEZE. Алерт на це потрібен завжди. Ми рекомендуємо ставити попередження за 2 дні до межі — цього часу достатньо для позапланового обслуговування.
Порівняння інструментів моніторингу PostgreSQL
| Інструмент | Дані | Зберігання | Типовий Use Case |
|---|---|---|---|
| pg_stat_statements | Поточні запити | В пам'яті | Пошук важких запитів у реальному часі |
| pgBadger | Логи PostgreSQL | Файлові звіти | Історичний аналіз, тренди |
| postgres_exporter | Метрики Prometheus | Часові ряди | Оперативний дашборд і алерти |
pgBadger у поєднанні з postgres_exporter дає повну картину: перший — для глибинного аналізу минулих подій, другий — для поточного стану. Це краще, ніж використовувати лише один інструмент.
Що входить у налаштування моніторингу PostgreSQL?
У рамках робіт з налаштування моніторингу ми надаємо:
- Аудит поточної конфігурації: перевірка параметрів логування, розширень, політик autovacuum.
- Включення та налаштування pg_stat_statements (без рестарту, якщо можливо).
- Конфігурація pgBadger: налаштування формату логів, cron-завдання для щоденної генерації звітів.
- Деплой postgres_exporter як systemd-сервісу, налаштування Prometheus target.
- Імпорт та кастомізація Grafana дашборду під вашу архітектуру (репліки, шарди).
- Налаштування алертів: wraparound, bloat, lag, активні з'єднання.
- Документація: опис метрик, runbook за типовими інцидентами.
- Навчання команди: інтерпретація звітів pgBadger, робота з алертами.
Як інтерпретувати звіт pgBadger?
- Top slowest queries: шукаємо запити з високим середнім часом і великою кількістю викликів.
- Lock waits: при якому додатку виникають блокування.
- Temporary files: якщо багато — не вистачає work_mem, потрібно збільшити.
- Checkpoint warnings: занадто часті checkpoint — скорочується max_wal_size.
- Часовий паттерн: пікове навантаження на одні й ті ж години — можливо, потрібно реплікувати читання.
Строки та вартість
Строк налаштування — від половини дня для базової конфігурації до 2–3 днів, якщо потрібні кастомні метрики, інтеграція з існуючим стеком моніторингу та історичний аналіз накопичених логів. Вартість розраховується індивідуально залежно від складності кластера та необхідного набору інструментів.
Налаштування моніторингу PostgreSQL — не разова дія, а інвестиція в стабільність. Ми гарантуємо якість налаштування. Замовте аудит PostgreSQL-моніторингу — отримайте повну картину здоров'я бази. Зв'яжіться з нами для консультації. Отримайте консультацію прямо зараз.
