PG数据库pg_cron定时任务

PG几种定时任务实现方式对比：https://blog.csdn.net/weixin_73350116/article/details/134284090

pg_cron官网：https://github.com/citusdata/pg_cron

sudo apt-get -y install postgresql-16-cron
修改postgresql.conf：
/etc/postgresql/16/main$ :chmod 666 postgresql.conf
/etc/postgresql/16/main$ :vim postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
cron.timezone = 'PRC'
cron.use_background_workers = on

 sudo systemctl restart postgresql
-- run as superuser:
CREATE EXTENSION pg_cron;
-- optionally, grant usage to regular users:
GRANT USAGE ON SCHEMA cron TO log;

 SELECT cron.schedule('part log', '25 14 * * *', 'select auto_log_partition();');
select * from cron.job
update cron.job set database='XX'

DECLARE
    table_name     text ;            -- 表
    curMM         varchar(6);        -- 'YYYYMM'字串,用做分区子表的后缀
    isExist         boolean;        -- 分区子表,是否已存在
    startTime         text;
    endTime        text;
    strSQL          text;
    
BEGIN
    table_name := 'sys_log';
    -- 判断对应分区表 是否已经存在?
    curMM := to_char( now() , 'YYYYMM' );
    select count(*) INTO isExist from pg_class where relname = (table_name||'_'||curMM);
    --raise warning 'rkind%',rkind;
    IF (  isExist = false ) THEN  
        -- 创建子分区表
        startTime := curMM||'01 00:00:00.000';
        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
         strSQL := 'CREATE TABLE IF NOT EXISTS '||table_name||'_'||curMM||
                  ' PARTITION OF '||table_name||' FOR VALUES FROM ('''|| startTime ||''') TO ('''|| endTime ||''' ) ;';  
        EXECUTE strSQL;
 
       
    END IF;