本文介绍AnalyticDB PostgreSQL版实例如何通过pg_cron插件设置定时任务。

重要 V6.3.8.9及以后版本，安装或升级插件需要提交工单联系技术支持进行处理。

如何查看实例内核版本，请参见查看内核小版本。

功能介绍

pg_cron是基于cron的作业调度插件，语法与常规cron相同，但pg_cron可以直接从数据库执行PostgreSQL命令。

定时任务主要分为定时计划和定时任务两个部分，具体介绍如下：

  • 定时任务：用于指定该任务的具体内容，例如VACUUM。
  • 定时计划：用于规定任务的时间计划，例如每隔一分钟执行一次该任务。

    定时计划使用标准的cron语法，语法如下：

     ┌───────────── 分钟 (取值为0 - 59)
 │ ┌────────────── 小时 (取值为0 - 23)
 │ │ ┌─────────────── 日期 (取值为1 - 31)
 │ │ │ ┌──────────────── 月份 (取值为1 - 12)
 │ │ │ │ ┌───────────────── 一周中的一天 (取值为0 - 6，0表示周日)
 │ │ │ │ │
 │ │ │ │ │
 │ │ │ │ │
  *  *  *  *  *
    • *表示任意时间都可以运行。
    • 特定数字表示仅在这个时间运行。
    • ,表示分隔多个指定时间。
    • -表示时间范围。
    • /表示范围间隔。
    说明
    • 定时任务使用的是GMT/UTC时间，请注意将本地时间换算成GMT时间。
    • 创建或预览定时计划，请参见Crontab.guru。

    示例如下：

    • 每周六3:30 AM（GMT）：
      30 3 * * 6
    • 每月1号和30号1:45 AM（GMT）：
      45 1 1,30 * *
    • 每周一至周五的3:00 AM （GMT）：
      00 3 * * 1-5
    • 从8点（GMT）到20点（GMT），每两小时整点：
      0 8-20/2 * * *

注意事项

目前pg_cron插件最新版本为1.4，如果您需要使用最新版本的pg_cron需要将AnalyticDB PostgreSQL版实例升级至V6.3.6.0及以上版本。

说明

创建或删除插件

  • 查看插件是否安装

    目前V6.3.6.0及以上内核小版本的AnalyticDB PostgreSQL版实例默认安装1.4版本的pg_cron插件，您可以连接实例的postgres库查看pg_extension表确认是否已安装，查看语句如下：

    SELECT * FROM pg_extension;

    如果返回信息中extname列存在pg_cron则表示已安装pg_cron插件；如果extname列不存在pg_cron则表示未安装pg_cron插件。

  • 创建插件

    安装pg_cron操作步骤如下：

    1. 提交工单联系技术支持确认数据库配置中shared_preload_libraries是否包含pg_cron，并让技术支持配置cron.database_name参数。
    2. 修改配置参数后，需要您重启实例。
      说明 为了降低实例重启对您业务的影响，建议您在业务低谷期手动执行重启操作，如何重启实例，请参见重启实例。
    3. 使用管理员权限的账号连接数据库，在postgres库中执行以下语句安装并pg_cron插件：
      CREATE EXTENSION pg_cron WITH SCHEMA pg_catalog VERSION '1.0';
ALTER EXTENSION pg_cron UPDATE;
      说明 通过该方法创建的pg_cron插件为1.2版本，本文中介绍的部分功能可能无法使用，如需使用本文中的所有功能，建议您将实例的内核小版本升级至V6.3.6.0及以上版本。
  • 删除插件

    使用管理员权限的账号在postgres库中执行以下语句删除pg_cron插件：

    DROP EXTENSION pg_cron;

添加定时任务

  • 添加定时任务

    语法如下：

    SELECT cron.schedule('<定时计划>', '<定时任务>');

    示例如下：

    • 周六3:30 AM (GMT) 删除过期数据：
      SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
    • 每天的10:00 AM (GMT) 执行函数test：
      SELECT cron.schedule('0 10 * * *', 'select test()');
    • 每分钟执行指定SQL：
      SELECT cron.schedule('* * * * *', 'select 1');
    • 每月1号和30号以及每周六和周日的2:30 AM (GMT) 执行磁盘清理：
      SELECT cron.schedule('30 2 1,30 * 6,0', 'VACUUM FULL');
  • 指定定时任务名称

    pg_cron现已支持添加定时任务名称字段，语法如下：

    SELECT cron.schedule('<定时任务名称>', '<定时计划>', '<定时任务>');

    示例如下：

    • 每周六的10:00 AM (GMT) 删除过期数据：
      SELECT cron.schedule('Delete Expired Data','30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
    • 每分钟执行指定SQL：
      SELECT cron.schedule('Select Per Minute', '* * * * *', 'select 1');
    • 每天的11:00 PM (GMT) 对postgres库执行VACUUM：
      SELECT cron.schedule('Do Vacuum', '0 23 * * *' , 'VACUUM FULL');
  • 指定定时任务执行数据库

    pg_cron插件早期只能在安装插件的库执行定时任务，如果需要在其他库执行任务，需要直接操作cron.job表，操作不方便且不安全，当前版本支持指定库和指定用户执行定时任务。语法如下：

    SELECT cron.schedule('<定时任务名称>', '<定时计划>', '<定时任务>', '<执行数据库>', '<执行用户>', '<任务是否启用>');
    • '<执行数据库>'：默认值为空，表示在postgres库执行。
    • '<执行用户>：默认值为空，表示使用当前账号执行。
    • '<任务是否启用>'：默认值为true，表示启用该定时任务。

    示例如下：

    • 每天的11:00 PM (GMT) 在dw库执行VACUUM：
      SELECT cron.schedule('Do Vacuum', '0 23 * * *' , 'VACUUM FULL', 'dw');
    • 每分钟让gp1234用户在dw库中执行指定SQL：
      SELECT cron.schedule('Select Per Minute', '* * * * *', 'select 1', 'dw', 'gp1234');
    • 每天的10:00 AM (GMT) 让user1用户在dw库执行函数test：
      SELECT cron.schedule('DO Function', '0 10 * * *', 'select test()', 'dw', 'user1', true);

更改定时任务

pg_cron提供了cron.alter_job函数用于更改定时任务，语法如下：

SELECT cron.alter_job(<定时任务ID>, '<定时计划>', '<定时任务>', '<执行数据库>', '<执行用户>', '<任务是否启用>');
  • <定时任务ID>：必填。
  • 其它参数：非必填，默认值为空，表示不更改。

示例如下：

  • 任务ID为3的定时任务更改为每天11:00 AM（GMT）执行：
    SELECT cron.alter_job(3, '0 11 * * *');
  • 任务ID为1的定时任务更改为VACUUM：
    SELECT cron.alter_job(1, null , 'VACUUM');
  • 任务ID为2的定时任务的执行用户更改为gp1234：
    SELECT cron.alter_job(2, null , null, null, 'gp1234');

查看定时任务执行信息

pg_cron新增了cron.job_run_details表记录定时任务执行信息，您可以在该表中获取执行任务的详细情况。示例如下：

  • 查看失败的任务详情：
    SELECT * FROM cron.job_run_details WHERE status = 'failed';
  • 查看任务ID为1的定时任务执行详情：
    SELECT * FROM cron.job_run_details WHERE jobid = '1';
说明 定时任务较多时，可能会导致cron.job_run_details表变得非常大，建议设置一个定时任务来删除该表。如果您不需要记录定时任务的执行情况，也可以提交工单联系技术支持修改cron.log_run关闭记录信息。

查看定时任务列表

命令如下：

SELECT * FROM cron.job;

删除定时任务

  • 通过任务名称删除定时任务，语法如下：
    SELECT cron.unschedule('<定时任务名称>');
    说明 pg_cron允许创建重复的定时任务名称，当删除名称重复的定时任务时，只会删除任务ID较小的一个。

    示例如下：

    删除名为Do Vacuum的定时任务：

    SELECT cron.unschedule('Do Vacuum');
  • 通过定时任务ID删除定时任务，语法如下：
    SELECT cron.unschedule(<定时任务ID>);
    说明 定时任务ID为创建任务时自动生成的，可以通过查看cron.job表的jobid字段查看。

    示例如下：

    删除定时任务ID为21的定时任务：

    SELECT cron.unschedule(21);