pg_cron. Планировщик заданий по расписанию

Версия: 1.6.4.

В исходном дистрибутиве установлено по умолчанию: да.

Связанные компоненты:

ltree;

plpython2u.

Схема размещения: ext.

Расширение pg_cron используется в СУБД Pangolin для выполнения задач по расписанию (Job scheduler for PostgreSQL).

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

периодический вызов VACUUM в соответствии со спецификой загруженности сервера;
партиционирование таблиц. Например, для гибкого управления данными (удаление или перенос отдельных партиций вместо целой таблицы). Соответственно, добавление новой партиции — это часть процесса управления партиционированной таблицей, необходимая при расширении таблицы с течением времени и созданием новых партиций.

Обновление pg_cron до актуальной версии добавляет новые возможности в виде новых функций, параметров конфигурирования и режима работы.

При этом имя пользователя передается неявно, через чтение текущего пользователя, поэтому пользователь может завершить только свою задачу.

Работа внутри кластера

pg_cron можно безопасно использовать в схеме с реализацией. Данные cron можно модифицировать только с текущего лидер-сервера.

note

Так как pg_cron для выполнения задач сохраняет host:ip сервера (127.0.0.1:5433), необходимо на всех экземплярах выставить одинаковый порт. В противном случае при switchover/failover pg_cron попытается подключиться к старому лидеру-серверу.

Доработка

Ранее было произведено добавление расширения pg_cron в состав дистрибутива Pangolin для обеспечения возможности выполнения задач по расписанию.

Ограничения

Ограничения отсутствуют.

Установка

Сведения

Установка расширения может быть произведена в процессе развертывании СУБД Pangolin при использовании настроек по умолчанию (документ «Руководство по установке», раздел «Автоматизированная установка при помощи Ansible-скриптов»).

Для начала использования расширения выполните следующие действия:

Пропишите в конфигурационном файле Pangolin:
```
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
```
Где postgres - имя БД, в которой будет работать cron.
От пользователя с правами superuser включите расширение:
```
CREATE EXTENSION pg_cron;
```
Выдайте права на схему cron нужному пользователю:
```
GRANT USAGE ON SCHEMA cron TO <имя пользователя pg_cron>;
```
Перезагрузите Pangolin. Так как процессу pg_cron необходимо создавать подключение к БД, добавьте пароль пользователя, созданного на предыдущем шаге, в хранилище паролей:
```
SELECT add_auth_record_to_storage('localhost', <порт СУБД>, <имя БД>, <имя пользователя pg_cron>, 'пароль пользователя pg_cron');
```

note

Функция add_auth_record_to_storage добавляет учетные данные в хранилище паролей. Если хранилище паролей не используется, вызов этой функции не требуется.

Настройка

Выдача прав на схему cron

Выдача прав на схему cron нужному пользователю:

GRANT USAGE ON SCHEMA cron TO <имя пользователя pg_cron>;

Создание пользователя для расширения pg_cron

Проверьте, что расширение установлено.
Создайте пользователя (например, cronuser):
```
CREATE USER cronuser WITH PASSWORD 'passwd'
```
Разрешите использование схемы cron новому пользователю:
```
GRANT USAGE ON SCHEMA cron TO cronuser;
```

Добавьте пароль пользователя в хранилище паролей.

SELECT add_auth_record_to_storage('localhost', <порт СУБД>, <имя БД>, `cronuser`, 'passwd');

note

Управление задачами планировщика

Внимание!

Задачи запускаются только на лидер-сервере. Пока сервер в состоянии горячего резерва (Hot Standby), задачи не запускаются и ждут, пока сервер не станет лидером.

Задачи запускаются с правами пользователя, создавшего задачу.

Формат таблицы cron.job

Таблица содержит все запланированные задачи.

Только пользователь с правами суперпользователя может напрямую работать с данной таблицей (см. «Продвинутая работа с таблицей запланированных задач»).

Поле	Описание
`jobid`	Идентификатор задачи
`schedule`	Дата и время выполнения задачи или временной период для выполнения задачи
`command`	Выполняемая команда
`nodename`	Имя узла, где будет выполнена команда. В качестве имени узла можно использовать одно из возможных значений: `hostname`, `localhost`, IP-адрес, Unix-сокет (значение конфигурационного параметра - `unix_socket_directories`). Какое именно указать значение - зависит от правила, прописанного в `pg_hba.conf` для пользователя создавшего команду
`nodeport`	Порт для подключения к узлу PostgreSQL (по умолчанию `5432`)
`database`	Имя базы данных, в который будет выполнена команда
`username`	Пользователь, создавший команду

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

Зайдите под пользователем, созданным в при настройке расширения.
Создайте задачу в cron:
```
SELECT cron.schedule(period, command);
```
где:
- period – строка, означающая дату или временной период для выполнения задачи. Формат строки:
  
  * * * * *
  минуты (0-59) часы (0-23) день (1-31) месяц (1-12) день недели (0-6, 7 равноценно 0)
  
  Значение * означает повторение события.
  
  Примеры:
  - * * * * * - каждую минуту;
  - 0 5 * * * - каждый день в 5:00;
  - */10 * * * * - каждые 10 минут;
  - 0 0 1 1 * - 1 января каждого года;
  - 0 9 * * 1,3,5 - в понедельник, среду и пятницу в 9 утра;
  - 0 0 1 * * - каждое 1-е число месяца.
- command – исполняемая команда, форматы:
  - $$ SQL-req $$ - для SQL-запроса;
  - 'PostreSQL SQL command' - одиночная PostreSQL SQL команда.
  Примеры:
  - Вывести все задачи в планировщике:
    SELECT cron.schedule('59 23 * * *', $$SELECT * FROM cron.job$$);
  - Выполнить команду VACUUM:
    SELECT cron.schedule('59 23 * * *', 'VACUUM');
Выведите содержимое таблицы cron.job, чтобы проверить, что задача создана:
```
SELECT * from cron.job;
```

*	*	*	*	*
минуты (0-59)	часы (0-23)	день (1-31)	месяц (1-12)	день недели (0-6, 7 равноценно 0)

Задачи могут выполняться параллельно, но они будут запущены с небольшим временным смещением.

Если задача A требует завершения задачи B, то задача A будет ждать завершения команды B.

Удаление задачи из pg_cron

Выведите данные таблицы cron.job:
```
SELECT * from cron.job;
```
По таблице найдите jobid задачи, которую необходимо удалить.
Выполните команду удаления задачи:
```
SELECT cron.unschedule(jobid);
```
Проверьте, что задача удалена (вывести таблицу cron.job).

Внимание!

Удаление чужой задачи при наличии соответствующих прав возможно только через ID задачи.

Просмотр задач в планировщике

Для просмотра задач выведите содержимое таблицы cron.job:

SELECT * FROM cron.job;

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

Продвинутая работа с таблицей запланированных задач

Пользователь с правами суперпользователя может редактировать данные таблицы cron.job, например, для:

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

Пример:

INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username)
VALUES ('0 4 * * *', 'VACUUM', 'node-1', 5432, 'postgres', 'marco');

note

Для работы с удаленными серверами добавьте соответствующие записи в хранилище паролей для всех удаленных узлов.

Использование модуля

Примеры задач планировщика:

Удалить старые данные в субботу, 3:30 ночи:

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

Выполнять VACUUM каждый день в 10:00 утра:
```
SELECT cron.schedule('0 10 * * *', 'VACUUM');
```
Посмотреть текущие задачи:
```
SELECT * FROM cron.job;
```
Отменить задачу:
```
SELECT cron.unschedule(43);
```

Пример работы с именованными задачами:

/* Создаем задачу */
select cron.schedule('My select', '0 8 * * *', 'select 1');
schedule
----------
       15
(1 row)
/* Проверяем, что задача создалась */
select * from cron.job where jobname='My select';
jobid | schedule  | command  | nodename  | nodeport | database | username | active |  jobname
-------+-----------+----------+-----------+----------+----------+----------+--------+-----------
15 | 0 8 * * * | select 1 | localhost |     5433 | postgres | postgres | t      | My select
(1 row)
/* Удаляем задачу */
select cron.unschedule('My select');
unschedule
------------
t
(1 row)
/* Проверяем, что задача удалена */
select * from cron.job where jobname='My select';
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+----------+---------+----------+----------+----------+----------+--------+---------
(0 rows)
/* Создаем задачу */
select cron.schedule('My select', '0 8 * * *', 'select 1');
schedule
----------
       16
(1 row)
/* Проверяем, что задача создалась */
select * from cron.job where jobname='My select';
jobid | schedule  | command  | nodename  | nodeport | database | username | active |  jobname
-------+-----------+----------+-----------+----------+----------+----------+--------+-----------
16 | 0 8 * * * | select 1 | localhost |     5433 | postgres | postgres | t      | My select
(1 row)
/* Меняем имя пользователя задачи */
select cron.alter_job(job_id:=16, username:='test_tuz');
alter_job
-----------

(1 row)
/* Проверяем, что задача поменялась */
select * from cron.job where jobname='My select';
jobid | schedule  | command  | nodename  | nodeport | database | username | active |  jobname
-------+-----------+----------+-----------+----------+----------+----------+--------+-----------
16 | 0 8 * * * | select 1 | localhost |     5433 | postgres | test_tuz | t      | My select
(1 row)
/* Пытаемся удалить задачу по имени, получаем сообщение об ошибке, хотя имя задачи указано верно */
select cron.unschedule('My select');
ERROR:  could not find valid entry for job 'My select'
/* Удаляем задачу по ID */
select cron.unschedule(16);
unschedule
------------
t
(1 row)
/* Проверяем, что задача удалена */
select * from cron.job where jobname='My select';
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+----------+---------+----------+----------+----------+----------+--------+---------
(0 rows)

Функции и параметры

В новой версии добавлены внутренние функции и новые функции, которые работают с именованными задачами. При поиске задачи по имени (например, в функции cron.unschedule) используется пара имя задачи/имя пользователя.

Функция cron.schedule теперь имеет перегруженный вариант со следующими параметрами:

Имя параметра	Тип данных	Комментарий
job_name	name	Имя задачи
schedule	text	Расписание в формате cron
command	text	Команда, выполняемая задачей

Пример использования:

/* Создаем задачу */
select cron.schedule('My vacuum', '0 3 * * *', 'VACUUM');
/* Меняем ранее созданную задачу в части расписания и команды */
select cron.schedule('My vacuum', '0 5 * * *', 'select 1');

Функция возвращает ID созданной задачи.

Функция cron.unschedule теперь имеет перегруженный вариант со следующим параметром:

Имя параметра Тип данных Комментарий
job_name name Имя задачи

Пример использования:
```
/* Удаляем задачу */
select cron.unschedule('My vacuum');
```

Добавлена функция cron.alter_job для изменения ранее созданных задач.

Имя параметра	Тип данных	Комментарий
job_id	bigint	ID задачи
schedule	text	Расписание в формате cron
command	text	Команда, выполняемая задачей
database	text	База данных
username	text	Имя пользователя
active	boolean	Запускается ли задача

Примеры использования:

/*
 * Задаем все параметры
 * Задача должна была быть ранее создана
 * Меняем задачу с ID 4:
 * расписание: '0 4 * * *'
 * база данных: 'First_db'
 * имя пользователя: 'test_tuz'
 * команда, выполняемая задачей: 'select 2'
 * запускается ли задача: true
 */
select cron.alter_job(job_id:=4,schedule:='0 4 * * *',database:='First_db', username:='test_tuz', command:='select 2', active:=true);

/* Отключаем задачу с ID 4 не удаляя ее */
select cron.alter_job(job_id:=4, active:=false);
/* Включаем задачу с ID 4 обратно */
select cron.alter_job(job_id:=4, active:=true);

Добавлена функция cron.schedule_in_database, позволяющая запускать задачи сразу с указанием необходимой базы данных и имени пользователя.

Имя параметра	Тип данных	Комментарий
job_name	text	ID задачи
schedule	text	Расписание в формате cron
command	text	Команда, выполняемая задачей
database	text	База данных
username	text	Имя пользователя
active	boolean	Запускается ли задача

/*
 * Создаем задачу с именем: 'Mysel'
 * расписание: '0 4 * * *'
 * база данных: 'postgres'
 * имя пользователя: 'postgres'
 * команда, выполняемая задачей: 'select 2'
 * запускается ли задача: false
 */
select cron.schedule_in_database(job_name:='Mysel',schedule:='0 4 * * *',database:='postgres', username:='postgres', command:='select 2', active:=false);

Функция возвращает ID созданной задачи.

Добавлена таблица аудита задач. Пример чтения из таблицы аудита задач:

select * from cron.job_run_details;
 jobid | runid | job_pid | database | username | command  |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------
    22 |   114 |   30463 | postgres | postgres | select 2 | succeeded | SELECT 1       | 2022-11-22 16:31:00.008602+03 | 2022-11-22 16:31:00.014413+03
/*
 * ID задачи: 22
 * номер задачи в очереди выполнения: 114
 * PID процесса задачи: 30463
 * база данных: postgres
 * имя пользователя: postgres
 * команда, выполняемая задачей: 'select 2'
 * статус выполнения задачи: 'succeeded'
 * сообщение статуса выполнения: 'SELECT 1'
 * время начала выполнения задачи: 2022-11-22 16:31:00.008602+03
 * время окончания выполнения задачи: 2022-11-22 16:31:00.014413+03
 */

Добавлен параметр cron.enable_superuser_jobs, значение по умолчанию on. Разрешает/запрещает задачи от имени пользователя с правами superuser. Изменение значения требует перезапуска базы данных.
Добавлен параметр cron.log_min_messages, значение по умолчанию warning. Управляет уровнем логирования расширения pg_cron. Изменение значения требует перезапуска базы данных.
Добавлен параметр cron.use_background_workers, значение по умолчанию off. Управляет режимом выполнения задач расширения pg_cron.

note

Режим работы через фоновые рабочие процессы отключен в обновлении.

При значении off задачи создаются через открытие соединения к базе, что требует настройки файла pg_hba.conf и хранилища паролей.

При значении on задачи создаются с использованием фоновых рабочих процессов.

Изменение значения требует перезапуска базы данных.

Отключение функциональности

Внимание!

Отключение функциональности без потери данных невозможно.

Выполните следующие SQL-запросы:

/* Так как откат до предыдущей версии не поддерживается расширением, сначала его необходимо удалить */
DROP EXTENSION IF EXISTS pg_cron;
/* Добавляем расширение версии 1.2 */
CREATE EXTENSION pg_cron VERSION '1.2';
/* Если понадобится провести обновление версии pg_cron, то выполните */
/* ALTER EXTENSION pg_cron UPDATE TO '1.6.4'; */

Ссылки на документацию разработчика

Дополнительно поставляемый модуль pg_cron: https://access.crunchydata.com/documentation/pg_cron/1.2.0/

Работа внутри кластера​

Доработка​

Ограничения​

Установка​

Настройка​

Выдача прав на схему cron​

Создание пользователя для расширения pg_cron​

Управление задачами планировщика​

Формат таблицы cron.job​

Создание задачи в pg_cron​

Удаление задачи из pg_cron​

Просмотр задач в планировщике​

Продвинутая работа с таблицей запланированных задач​

Использование модуля​

Функции и параметры​

Отключение функциональности​

Ссылки на документацию разработчика​

Работа внутри кластера

Доработка

Ограничения

Установка

Настройка

Выдача прав на схему cron

Создание пользователя для расширения pg_cron

Управление задачами планировщика

Формат таблицы cron.job

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

Удаление задачи из pg_cron

Просмотр задач в планировщике

Продвинутая работа с таблицей запланированных задач

Использование модуля

Функции и параметры

Отключение функциональности

Ссылки на документацию разработчика