如何在Postgres中进行作业调度

最新推荐文章于 2024-08-13 08:22:27 发布

whdxlxl

最新推荐文章于 2024-08-13 08:22:27 发布

阅读量853

点赞数 35

文章标签：数据库 postgresql

本文链接：https://blog.csdn.net/whdxlxl/article/details/137264206

版权

简介

想要实现在Postgres进行作业调度，可以使用pg_cron扩展。该pg_cron扩展是一个简单的基于 cron 的 PostgreSQL 作业调度程序，在数据库内运行。它使用与常规 cron 相同的语法，允许您直接从数据库定时调度并执行数据库命令。

本文为您介绍 PostgreSQLpg_cron插件的使用方法。

准备工作

登录MemFire Cloud平台，创建一个新应用，如下图所示：

应用创建成功后，即可获得一个云端的Postgres数据库。

创建一张数据表my_table，，包含id、name和timestamp三个字段，并插入几条数据。

CREATE TABLE my_table (  
    id SERIAL PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    timestamp TIMESTAMP DEFAULT NOW()  
);

INSERT INTO my_table (name) VALUES ('Alice');  
INSERT INTO my_table (name) VALUES ('Bob');  
INSERT INTO my_table (name) VALUES ('Charlie');

启用/禁用扩展

启用扩展

MemFire Cloud提供启用扩展两种方式，可在MemFire Cloud控制台页面进行开启，也可使用SQL命令开启扩展

1.在数据库->扩展页面搜索cron,找到pg_cron并启用扩展，如下图所示。

2.使用SQL命令来启用pg_cron扩展。


create extension pg_cron with schema extensions;
-- 默认 pg_cron 创建完成后，其配置数据以及任务执行只能由管理员用户进行设置。若需要其他用户进行 pg_cron 设置或者执行，则需要向其他用户授予 cron 元数据库的权限，请运行以下命令。
grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;

关闭扩展

同样的，用户可以选择在控制台关闭扩展，亦可使用如下SQL命令来关闭扩展。

drop extension if exists pg_cron;

使用介绍

pg_cron 提供三个主要操作：增加任务项、删除任务项、查看任务信息。

cron.schedule() 函数

使用cron.schedule函数来创建定时任务。任务最初是在默认 postgres 数据库中计划的。该函数返回一个表示任务标识符的 bigint 值。

语法：

cron.schedule (job_name,
    schedule,
    command
);

参数说明：

job_name ：cron 任务的名字，可为空不设置。
schedule：表示 cron 任务时间表的文本，格式是标准 cron 格式。其中 * 表示“每个时间段运行”，具体数字表示“仅在这个数字时运行”。

# 格式是：分 时 日 月 星期
# week (0 - 6) = sun,mon,tue,wed,thu,fri,sat
# Example of job definition:
 ┌───────────── min (0 - 59)
 │ ┌────────────── hour (0 - 23)
 │ │ ┌─────────────── day of month (1 - 31)
 │ │ │ ┌──────────────── month (1 - 12)
 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
 │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

command：要运行的命令的文本。

使用示例

现在，假设你想要设置一个定时任务，每5分钟向my_table中插入一条新的记录。

SELECT cron.schedule( 
    'task',
    '*/2 * * * *',  -- 每2分钟执行  
    
$$
INSERT INTO my_table (name) VALUES ('Daily Insert');
$$
  -- 要执行的SQL命令  
);

在表编辑器中，可以看到每两分钟插入一条数据：

cron.alter_job() 函数

允许你修改已经存在的定时任务（作业）的属性，而不需要删除并重新创建它。这个函数提供了一种灵活的方式来更新作业的配置，例如改变作业的执行时间、关联的 SQL 命令或其他相关设置。

SELECT cron.alter_job(jobid, new_schedule, new_command, new_database, new_nodename, new_username);

参数说明：

jobid: 要修改的作业的ID。你可以通过 cron.list_jobs() 函数获取作业ID。
new_schedule: 新的cron表达式，定义了作业的执行时间。如果不需要改变，可以传入NULL。
new_command: 新的SQL命令，作业执行时将运行这个命令。如果不需要改变，可以传入NULL。
new_database: 作业应该在哪个数据库中执行。如果不需要改变，可以传入NULL。
new_nodename: 作业应该在哪个PostgreSQL节点上执行。这主要用于多节点或分布式环境。如果不需要改变，可以传入NULL。
new_username: 以哪个用户的身份执行作业。如果不需要改变，可以传入NULL。

使用示例

现在，假设你想要修改一个定时任务，每5分钟向my_table中插入一条新的记录。

SELECT cron.alter_job( 
    job_id := (select jobid from cron.job where jobname = 'task'),
    schedule := '*/5 * * * *'
);

修改成功之后，使用SELECT * FROM cron.job;查看任务状态；

cron.unschedule() 函数

删除 cron 任务。使用时，可以通过传入 job_name 或 job_id来删除任务。请确保您是当前 job_id 所对应的策略的拥有者。该函数返回一个布尔值，指示成功或失败。

语法：

cron.unschedule (job_id);

cron.unschedule (job_name);

参数说明：

job_id：计划 cron 任务时从 cron.schedule 函数返回的任务标识符。
job_name：使用该 cron.schedule 函数计划的 cron 任务的名称。

查看 pg_cron 定时任务

在设置了定时任务后，可通过 cron.job 表查看已经配置了的定时任务，如执行以下语句。

SELECT * FROM cron.job;

注意事项

定时任务的执行不会阻塞正常的数据库操作，但它们会消耗系统资源。确保你的数据库服务器有足够的资源来处理额外的负载。