GreenPlum 6.27.0版本新特性及pg_cron模块配置定时任务说明

docker rm -f gpdb6270
docker run -itd --name gpdb6270 -h gpdb6270 \
  -p 5627:5432 -p 26270:28080  \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/greenplum:6.27.0 \
  /usr/sbin/init

docker exec -it gpdb6270 bash

su - gpadmin


gpstart -a
gpcc start



gpcc status
gpstate 

[gpadmin@gpdb6270 ~]$ ps -ef|grep cron | grep post
gpadmin    16678   16660  0 14:02 ?        00:00:00 postgres:  5432, bgworker: pg_cron launcher   
[gpadmin@gpdb6270 ~]$ 

 ┌───────────── min (0 - 59)
 │ ┌────────────── hour (0 - 23)
 │ │ ┌─────────────── day of month (1 - 31) or last day of the month ($)
 │ │ │ ┌──────────────── month (1 - 12)
 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
 │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

-- 1、默认在postgres数据库中，可以通过如下命令修改成其它数据库（可选）
gpconfig -c cron.database_name -v 'db_name' --skipvalidation


-- 2、修改shared_preload_libraries参数，注意该参数之前的值
gpconfig -s shared_preload_libraries

gpconfig -c shared_preload_libraries -v 'metrics_collector,pg_cron'
gpstop -M fast -ar

gpconfig -s shared_preload_libraries


-- 3、只能在第1步中cron.database_name配置的数据库中创建扩展，否则会报错
CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO user1;

ERROR:  can only create extension in database postgres
DETAIL:  Jobs must be scheduled from the database configured in cron.database_name, since the pg_cron background worker reads job descriptions from this database.
HINT:  Add cron.database_name = 'lhrgpdb' in postgresql.conf to use the current database.

ALTER EXTENSION pg_cron UPDATE TO '1.6.2';

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

-- 更改JOB
UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
SELECT cron.reload_job();


SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');



-- 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'$$);



-- 每2秒执行1次
SELECT  cron.schedule_in_database('run-select66', '2 seconds', $$SELECT 66$$,'db1');

-- 修改为10秒
UPDATE cron.job SET schedule = '10 seconds' WHERE jobid = 2;
-- 或：SELECT cron.alter_job(2,'20 seconds');
SELECT cron.reload_job();


-- 删除job
SELECT cron.unschedule(1);


-- 禁用或启用某个job
UPDATE cron.job SET active = 'f' WHERE jobid = 2;
SELECT cron.reload_job();



-- 查询
SELECT * from cron.job;
select * from cron.job_run_details order by runid desc limit 100;