Планировщик событий

Для таких задач в PostgreSQL используют расширение pg_cron. Оно позволяет создавать запланированные задачи с помощью синтаксиса cron (как в Unix-системах).

С помощью pg_cron можно автоматизировать:

Сначала pg_cron нужно установить на сервер, добавить в shared_preload_libraries и перезапустить PostgreSQL. После этого расширение создают в базе данных:

MySQL 8.1
CREATE EXTENSION IF NOT EXISTS pg_cron;

Начнём с самого простого — задачи, которая выполнится один раз в определённое время:

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

Например, эта задача при ближайшем наступлении 3:00 удалит старые записи логов, а затем удалит саму себя:

MySQL 8.1
SELECT cron.schedule(
    'cleanup_old_logs_once',
    '0 3 * * *',
    $command$
    DO $$
    BEGIN
        DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
        PERFORM cron.unschedule('cleanup_old_logs_once');
    END;
    $$;
    $command$
);

Обычно расписание '0 3 * * *' запускает задачу каждый день в 3:00. Но после первого выполнения задача вызывает cron.unschedule() для собственного имени, поэтому повторного запуска не будет.

Чаще всего задачи нужно запускать периодически — каждый день, час или минуту:

MySQL 8.1
SELECT cron.schedule(
    'cleanup_old_logs',
    '0 3 * * *',
    'DELETE FROM logs WHERE created_at < NOW() - INTERVAL ''30 days'''
);

Эта задача будет выполняться каждый день в 3:00 и удалять записи логов старше 30 дней.

Разберём синтаксис:

• cron.schedule() — функция для создания запланированной задачи
• 'cleanup_old_logs' — имя задачи
• '0 3 * * *' — расписание в формате cron (минута, час, день месяца, месяц, день недели)
• последний параметр — SQL-команда для выполнения

Формат cron-расписания:

MySQL 8.1
SELECT cron.schedule(
    'update_statistics_hourly',
    '0 * * * *',
    $$
    UPDATE product_stats SET
        total_sales = (SELECT SUM(amount) FROM orders WHERE product_id = product_stats.product_id),
        last_updated = NOW()
    $$
);

Эта задача будет обновлять статистику продаж каждый час (в начале каждого часа).

Примеры расписаний:

• '*/5 * * * *' — каждые 5 минут
• '0 * * * *' — каждый час (в начале часа)
• '0 0 * * *' — каждый день в полночь
• '0 0 * * 0' — каждое воскресенье в полночь
• '0 9 1 * *' — первого числа каждого месяца в 9:00
• '30 seconds' — каждые 30 секунд в pg_cron 1.5 и новее

Интервал в секундах задаётся отдельной строкой, а не шестым полем cron-выражения. pg_cron поддерживает значения от 1 до 59 секунд.

Иногда нужно, чтобы задача работала только в течение определённого периода.

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

MySQL 8.1
SELECT cron.schedule(
    'temporary_log_cleanup',
    '0 0 * * *',
    $$DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days'$$
);

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

MySQL 8.1
DO $$
BEGIN
    PERFORM cron.unschedule('temporary_log_cleanup');
    PERFORM cron.unschedule('stop_temporary_log_cleanup');
END;
$$;

Первый вызов удаляет задачу очистки, а второй — служебную задачу. В расписании служебной задачи указывают минуту, час, день и месяц даты, которая наступит через 30 дней.

Чтобы увидеть доступные текущему пользователю запланированные задачи:

MySQL 8.1
SELECT * FROM cron.job;

Обычный пользователь видит только собственные задачи. Суперпользователь и роль с атрибутом BYPASSRLS могут видеть задачи других пользователей.

Для просмотра истории выполнения задач:

MySQL 8.1
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

Удаление запланированной задачи:

MySQL 8.1
SELECT cron.unschedule('cleanup_old_logs');

Или по ID задачи:

MySQL 8.1
SELECT cron.unschedule(42);  -- где 42 - это jobid из таблицы cron.job

Изменение задачи:

Расписание, команду и активность существующей задачи можно изменить с помощью cron.alter_job():

MySQL 8.1
SELECT cron.alter_job(
    42,
    schedule := '0 */2 * * *'
);

Здесь 42 — jobid задачи из таблицы cron.job.

Важные моменты при работе с задачами pg_cron

1. Права доступа: Суперпользователь обычно устанавливает расширение, после чего обычным пользователям можно выдать USAGE на схему cron. Задача выполняется с правами создавшего её пользователя.

2. Временная зона: Cron-выражения используют настройку cron.timezone, которая по умолчанию равна GMT. Текущее значение можно проверить командой SHOW cron.timezone;.

3. Интервалы: pg_cron 1.5 и новее поддерживает интервалы от 1 до 59 секунд. Частоту нужно выбирать с учётом стоимости задачи и нагрузки.

4. Параллельные запуски: pg_cron не запускает одновременно несколько экземпляров одной задачи. Если наступил следующий срок, пока задача ещё работает, новый запуск будет поставлен в очередь.

5. Логирование: При включённой настройке cron.log_run сведения о запусках сохраняются в таблице cron.job_run_details. По умолчанию логирование включено.

Какой минимальный интервал можно использовать для повторяющихся задач?

Запланированные задачи — это мощный инструмент для автоматизации рутинных задач в базе данных. Они помогают поддерживать чистоту данных, обновлять статистику и выполнять регламентные операции без участия разработчиков! 🚀