pg_cron 使用

下载

https://github.com/citusdata/pg_cron
https://github.com/citusdata/pg_cron/releases/tag/v1.6.2
wget https://github.com/citusdata/pg_cron/archive/refs/tags/v1.6.2.zip

编译安装

...
cd pg_cron
# Ensure pg_config is in your path, e.g.
export PATH=/usr/local/pgsql/bin:$PATH
make
make install

修改配置

vim $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_cron'

# 假设你想默认在 test 库中使用 定时任务
cron.database_name = 'test'
# 默认是 GMT，当前版本是支持 修改 timezone 配置的
cron.timezone = 'PRC'
# 通过 unix socket domain 来连接 PG ， 此时需要确保 $PGDATA/pg_hba.conf 中 local 连接是 trust 的
cron.host = '/tmp'

# 并发度相关
# Schedule jobs via background workers instead of localhost connections
cron.use_background_workers = on
# Increase the number of available background workers from the default of 8
max_worker_processes = 20

重启 PG 服务, 新建扩展

pg_ctl restart
psql -d test -c "create extension pg_repack;

查看当前有哪些 job

-- View active jobs
select * from cron.job;

查看历史 job 运行情况

select * from cron.job_run_details order by start_time desc limit 5;
┌───────┬───────┬─────────┬──────────┬──────────┬───────────────────┬───────────┬──────────────────┬───────────────────────────────┬───────────────────────────────┐
│ jobid │ runid │ job_pid │ database │ username │      command      │  status   │  return_message  │          start_time           │           end_time            │
├───────┼───────┼─────────┼──────────┼──────────┼───────────────────┼───────────┼──────────────────┼───────────────────────────────┼───────────────────────────────┤
│    10 │  4328 │    2610 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:30:00.098164+01 │ 2023-02-07 09:30:00.130729+01 │
│    10 │  4327 │    2609 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:29:00.015168+01 │ 2023-02-07 09:29:00.832308+01 │
│    10 │  4321 │    2603 │ postgres │ marco    │ select process()  │ succeeded │ SELECT 1         │ 2023-02-07 09:28:00.011965+01 │ 2023-02-07 09:28:01.420901+01 │
│    10 │  4320 │    2602 │ postgres │ marco    │ select process()  │ failed    │ server restarted │ 2023-02-07 09:27:00.011833+01 │ 2023-02-07 09:27:00.72121+01  │
│     9 │  4320 │    2602 │ postgres │ marco    │ select do_stuff() │ failed    │ job canceled     │ 2023-02-07 09:26:00.011833+01 │ 2023-02-07 09:26:00.22121+01  │
└───────┴───────┴─────────┴──────────┴──────────┴───────────────────┴───────────┴──────────────────┴───────────────────────────────┴───────────────────────────────┘
(10 rows)

cron.job_run_details

• 该表不会自动清理， 需要手工清理， 可通过如下方式实现

-- Delete old cron.job_run_details records of the current user every day at noon
SELECT  cron.schedule('delete-job-run-details', '0 12 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$);

• 如果你不想使用 cron.job_run_details 可以禁用他

vim $PGDATA/postgresql.conf
cron.log_run = off

异库操作

如果你想在当前 test 库中去定时处理 test2 库中的对象需要使用 cron.schedule_in_database 函数

常见使用实例

-- 每周6凌晨3:30 删除 events 表的就数据 (GMT)
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
 schedule
----------
       42

-- 每天10点执行 vacuum 操作
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
 schedule
----------
       43

-- 将vacuum 操作对应job 改为每天3点执行
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
 schedule
----------
       43

-- 取消名为 nightly-vacuum 的job
SELECT cron.unschedule('nightly-vacuum' );
 unschedule 
------------
 t

-- 取消 id 为 42 的job
SELECT cron.unschedule(42);
 unschedule
------------
          t
-- 每间隔 5 秒执行一次存储过程 process_updates
SELECT cron.schedule('process-updates', '5 seconds', 'CALL process_updates()');

-- 每个月最后一天的 12:00 执行存储过程 process_payroll
SELECT cron.schedule('process-payroll', '0 12 $ * *', 'CALL process_payroll()');

-- 比如我当前是在 test 库， 现在我想对 test2 库中的对象进行处理， 需要使用 cron.schedule_in_database 函数
SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'test2');
 schedule
----------
       44

参考： https://github.com/citusdata/pg_cron