pg_cron

定时任务调度器

扩展总览

PGDG 第一方扩展: pg_cron : 定时任务调度器

基本信息

元数据

  • 默认版本: 1.6.5
  • PG大版本: 17,16,15,14,13
  • 动态加载: 需要显式加载
  • 需要DDL: 需要执行 CREATE EXTENSION DDL
  • 可重定位: 可以重定位安装至其他模式下
  • 信任程度: 未受信任,创建扩展需要超级用户权限
  • 所需模式: pg_catalog
  • 所需扩展: 无

软件包

  • RPM仓库:PGDG
  • RPM包名:pg_cron_$v*
  • RPM版本:1.6
  • RPM依赖:无
  • DEB仓库:PGDG
  • DEB包名:postgresql-$v-cron
  • DEB版本:1.6
  • DEB依赖:无

最新版本

系统 架构 PG17 PG16 PG15 PG14 PG13
el8 x86_64 pg_cron_17
PGDG 1.6.5
pg_cron_16
PGDG 1.6.5
pg_cron_15
PGDG 1.6.5
pg_cron_14
PGDG 1.6.5
pg_cron_13
PGDG 1.6.5
el8 aarch64 pg_cron_17
PGDG 1.6.5
pg_cron_16
PGDG 1.6.5
pg_cron_15
PGDG 1.6.5
pg_cron_14
PGDG 1.6.5
pg_cron_13
PGDG 1.6.5
el9 x86_64 pg_cron_17
PGDG 1.6.5
pg_cron_16
PGDG 1.6.5
pg_cron_15
PGDG 1.6.5
pg_cron_14
PGDG 1.6.5
pg_cron_13
PGDG 1.6.5
el9 aarch64 pg_cron_17
PGDG 1.6.5
pg_cron_16
PGDG 1.6.5
pg_cron_15
PGDG 1.6.5
pg_cron_14
PGDG 1.6.5
pg_cron_13
PGDG 1.6.5
d12 x86_64 postgresql-17-cron
PGDG 1.6.5
postgresql-16-cron
PGDG 1.6.5
postgresql-15-cron
PGDG 1.6.5
postgresql-14-cron
PGDG 1.6.5
postgresql-13-cron
PGDG 1.6.5
d12 aarch64 postgresql-17-cron
PGDG 1.6.5
postgresql-16-cron
PGDG 1.6.5
postgresql-15-cron
PGDG 1.6.5
postgresql-14-cron
PGDG 1.6.5
postgresql-13-cron
PGDG 1.6.5
u22 x86_64 postgresql-17-cron
PGDG 1.6.5
postgresql-16-cron
PGDG 1.6.5
postgresql-15-cron
PGDG 1.6.5
postgresql-14-cron
PGDG 1.6.5
postgresql-13-cron
PGDG 1.6.5
u22 aarch64 postgresql-17-cron
PGDG 1.6.5
postgresql-16-cron
PGDG 1.6.5
postgresql-15-cron
PGDG 1.6.5
postgresql-14-cron
PGDG 1.6.5
postgresql-13-cron
PGDG 1.6.5
u24 x86_64 postgresql-17-cron
PGDG 1.6.5
postgresql-16-cron
PGDG 1.6.5
postgresql-15-cron
PGDG 1.6.5
postgresql-14-cron
PGDG 1.6.5
postgresql-13-cron
PGDG 1.6.5
u24 aarch64 postgresql-17-cron
PGDG 1.6.5
postgresql-16-cron
PGDG 1.6.5
postgresql-15-cron
PGDG 1.6.5
postgresql-14-cron
PGDG 1.6.5
postgresql-13-cron
PGDG 1.6.5

扩展安装

使用 pig 命令行工具安装 pg_cron 扩展:

pig ext install pg_cron

使用 Pigsty剧本 安装 pg_cron 扩展:

./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_cron"]}' # -l <集群名>

YUM仓库 手工安装 pg_cron RPM 包:

dnf install pg_cron_17*;
dnf install pg_cron_16*;
dnf install pg_cron_15*;
dnf install pg_cron_14*;
dnf install pg_cron_13*;

APT仓库 手工安装 pg_cron DEB 包:

apt install postgresql-17-cron;
apt install postgresql-16-cron;
apt install postgresql-15-cron;
apt install postgresql-14-cron;
apt install postgresql-13-cron;

扩展 pg_cron 需要通过 shared_preload_libraries 进行 动态加载

shared_preload_libraries = 'pg_cron'; # 修改 PG 集群配置

使用以下 SQL 命令在已经安装此扩展插件的 PG 集群上 启用 pg_cron 扩展:

CREATE EXTENSION pg_cron;

使用方法

beware that cron.database has to be set before adding to shared_preload_libraries

-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
 schedule
----------
       42

-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
 schedule
----------
       43

-- Change to vacuum at 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
 schedule
----------
       43

-- Stop scheduling jobs
SELECT cron.unschedule('nightly-vacuum' );
 unschedule 
------------
 t

SELECT cron.unschedule(42);
 unschedule
------------
          t

-- Vacuum every Sunday at 4:00am (GMT) in a database other than the one pg_cron is installed in
SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');
 schedule
----------
       44

-- Call a stored procedure every 5 seconds
SELECT cron.schedule('process-updates', '5 seconds', 'CALL process_updates()');

-- Process payroll at 12:00 of the last day of each month
SELECT cron.schedule('process-payroll', '0 12 $ * *', 'CALL process_payroll()');

Crontab format:

 ┌───────────── 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)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *




最后修改 2025-03-20: update extension version (52133a3)