Настройка алертов и уведомлений при возникновении проблем

Все мы хотим узнавать о проблемах до того, как наш сервер упадет или усложнит жизнь пользователям. PostgreSQL предоставляет инструменты для создания уведомлений и запуска задач: pg_notify и pg_cron. Это, по сути, наш личный будильник и планировщик для базы данных.

Представьте ситуацию: у вас база данных курса обмена валют, и внезапно один из процессов заблокировал остальные. Вместо того чтобы постоянно вручную проверять состояние базы, вы можете настроить оповещения, чтобы быть в курсе. А для регулярной проверки состояния базы данных пригодится pg_cron. Всё это мы сейчас разберем.

Быстрые уведомления из базы данных: pg_notify

Начнем с pg_notify. Это встроенная функция PostgreSQL, которая позволяет отправлять уведомление из базы данных в определенный "канал". Вы можете использовать её для сигнализации о событиях, таких как завершение долгих запросов, обнаружение блокировок или других нестандартных ситуаций.

Синтаксис pg_notify достаточно простой:

NOTIFY <channel>, <message>;

• channel — это имя канала, на который отправляется уведомление.
• message — строка с текстом уведомления.

Приведём пример использования pg_notify. Давайте создадим уведомление для случая, когда обнаружена блокировка:

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM pg_locks l
        JOIN pg_stat_activity a
        ON l.pid = a.pid
        WHERE NOT l.granted
    ) THEN
        PERFORM pg_notify('alerts', 'Блокировка в базе данных!');
    END IF;
END $$;

Этот код проверяет, есть ли необработанная блокировка, и отправляет уведомление в канал alerts.

Чтобы прослушивать уведомления, используйте команду LISTEN в другом подключении:

LISTEN alerts;

Теперь, если pg_notify отправит сообщение в канал alerts, вы увидите уведомление в консоли.

Пример:

NOTIFY alerts, 'Эй, тут блокировка!';

На другом подключении, где выполнялось LISTEN alerts, вы сразу получите:
NOTIFY: Эй, тут блокировка!

Применение pg_notify не ограничивается простыми уведомлениями. Например, вы можете связать его с триггерами для автоматических оповещений о добавленных, измененных или удаленных данных:

Уведомление о новых записях

CREATE OR REPLACE FUNCTION notify_new_record()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('table_changes', 'Новая запись добавлена в таблицу!');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER record_added
AFTER INSERT ON your_table
FOR EACH ROW EXECUTE FUNCTION notify_new_record();

Теперь, если в таблицу your_table добавляется новая запись, вы сразу получите уведомление.

Вы узнаете подробнее о триггерах и встроенных функциях всего через пару уровней :P

Типичные ошибки и как их избежать

Если вы используете LISTEN, но не видите уведомлений, проверьте:

1. Работаете ли вы в том же соединении, откуда отправляются уведомления.
2. Проверьте, правильно ли указан канал.
3. Убедитесь, что вы вызываете pg_notify внутри транзакции, которая была зафиксирована (COMMIT).

Планировщик задач в PostgreSQL: pg_cron

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

Создание задач с pg_cron

Теперь, когда pg_cron установлен и готов к работе, создадим задачу, которая будет ежедневно очищать старые записи в таблице logs.

SELECT cron.schedule('Удаление старых логов',
'0 0 * * *',
$$ DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days' $$);

Расскажем, что тут происходит:

• '0 0 * * *' — это расписание выполнения команды (каждый день в полночь).
• DELETE FROM logs ... — это SQL-запрос, который cron будет выполнять.

Просмотр задач

Чтобы увидеть все задачи, выполняемые через pg_cron, используйте:

SELECT * FROM cron.job;

Отключение задач

Выключить задачу можно с помощью:

SELECT cron.unschedule(jobid);

Где jobid — идентификатор задачи. Вы можете узнать его из таблицы cron.job.

Полезные примеры с pg_cron

Регулярная проверка активности запросов

Создадим задачу, которая будет каждые 5 минут проверять долго выполняющиеся запросы:

SELECT cron.schedule('Проверка долгих запросов',
'*/5 * * * *',
$$ SELECT pid, query, state
    FROM pg_stat_activity
    WHERE state = 'active'
        AND now() - query_start > INTERVAL '5 minutes' $$);

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

Интеграция с внешними системами

И pg_notify, и pg_cron могут интегрироваться с внешними системами, такими как Slack, Telegram или системы мониторинга (например, Prometheus).

Telegram

Вы можете объединить pg_notify с ботом Telegram для отправки уведомлений. Основная идея — написать скрипт на Python или другом языке, который слушает уведомления и отправляет их в Telegram.

Пример простого Python-бота:

import psycopg2
import telegram

# Подключение к PostgreSQL
conn = psycopg2.connect("dbname=your_database user=your_user")

# Создание Telegram-бота
bot = telegram.Bot(token='your_telegram_bot_token')

# Открываем курсор для прослушивания
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN alerts;")

# Слушаем уведомления
print("Слушаем уведомления...")
while True:
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop()
        print("Получено уведомление:", notify.payload)
        bot.send_message(chat_id='your_chat_id', text=notify.payload)

Теперь ваш бот будет получать уведомления, отправленные через pg_notify.

Когда использовать pg_notify и pg_cron?

Используйте pg_notify для мгновенных реакций (например, уведомить администратора о блокировках).

Используйте pg_cron для регулярного выполнения задач (проверка активности запросов, чистка старых данных).

Замечания и подводные камни

pg_notify генерирует уведомления моментально, но не хранит их историю. Лучше интегрировать его с файловыми логами или внешними системами.

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

Теперь вы готовы к тому, чтобы оптимизировать свой мониторинг и автоматизировать управление базой данных. Вперед — настраивать алерты, чтобы быть не просто SQL-программистом, а DBA!