PostgreSQL实现定时任务的四种方法

数据库定时任务可以用于实现定期的实现备份、统计信息采集、定时的种数据汇总、任务数据清理与优化等。实现

PostgreSQL 没有提供类似 Oracle、定时的种MySQL 以及 Microsoft SQL Sever 的任务内置任务调度功能，因此本文给大家介绍一下 PostgreSQL 数据库中实现定时任务的实现 4 种方法。

方法一：操作系统定时任务

Linux 定时任务（crontab）或者 Windows 任务计划程序（Task Scheduler）为我们提供了一个实现定时任务传统的定时的种方法。以 crontab 为例，任务我们可以使用以下命令编辑任务列表：

复制crontab -e1.

然后在打开的实现文件中使用以下格式增加一行数据：

复制#分钟 小时 月份中的某一天 月份 星期 命令 #(0-59) (0-23) (1-31) (1-12) (0-7 [7 or 0 == Sunday]) <minute><hour><day of month><month><day of week><command>1.2.3.

其中的前五个字段表示执行命令的时间，可以使用星号（*）匹配所有的定时的种时间。例如，任务将 <month> 设置为星号表示每个月都执行命令。实现

举例来说，定时的种输入以下内容表示每天零点执行数据库逻辑备份操作。任务

复制00 * * * pg_dump --no-password -U user db_name > backup.sql1.

为了安全起见不要直接输入密码，而是应该将密码加入 .pgpass 文件，并且将该文件的权限设置为仅当前用户可见：

复制chmod600 .pgpass1.

方法二：pgAgent

pgAgent 是WordPress模板一个用于 PostgreSQL 数据库的任务调度代理，能够基于复杂的调度计划运行多步骤的批处理、shell 脚本以及 SQL 命令。对于 Unix/Linux 系统，pgAgent 以后台进程的方式运行；对于 Windows 系统，pgAgent 以服务的形式运行。

安装 pgAgent

PgAdmin 4 管理工具集成了 pgAgent 的功能，但是这两者需要单独安装。我们可以通过官方网站下载 PgAdmin 4 以及 pgAgent。具体的安装步骤和注意事项可以参考官网文档。安装完成之后，我们可以在 PgAdmin 4 左侧导航树中看到“pgAgent Jobs”节点。

创建定时任务

右键点击“pgAgent Jobs”节点，选择“Create” > “pgAgent Job”创建一个新的定时任务。

其中，“General”页面可以输入一些基本信息，包括任务的名称。“Steps”页面可以设置多个操作步骤，包括执行的脚本或者 SQL 语句等。“Schedules”页面用于定义任务执行的时间计划。“SQL”页面可以显示创建或者修改任务的网站模板语句。

点击“Save”按钮保存设置并创建任务，然后我们就可以在“pgAgent Job”节点下看到创建的任务。

方法三：pg_cron

pg_cron 是由 citusdata 公司开发的一个 PostgreSQL 定时任务插件（类似于 Oracle 中的 DBMS_SCHEDULER）。pg_cron 作为一个后台工作进程运行，使用类似 cron 的编辑语法，允许直接在数据库中执行定时任务。例如：

复制-- 每周六 3:30am (GMT) 删除历史记录 SELECT cron.schedule(30 3 * * 6, $$DELETEFROM events WHERE event_time <now()-interval1 week$$); schedule ---------- 42 -- 每天 10:00am (GMT) 执行清理作业 SELECT cron.schedule(nightly-vacuum,0 10 * * *,VACUUM); schedule ---------- 43 -- 将清理作业修改为 3:00am (GMT) SELECT cron.schedule(nightly-vacuum,0 3 * * *,VACUUM); schedule ---------- 43 -- 停止计划中的任务 SELECT cron.unschedule(nightly-vacuum); unschedule ------------ t (1row) SELECT cron.unschedule(42); unschedule ------------ t1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29. 安装 pg_cron

pg_cron 目前只支持 Linux 操作系统。对于 Red Hat、CentOS 以及 Fedora 等操作系统可以使用以下命令进行安装（PostgreSQL 12）：

复制sudo yum install -y pg_cron_121.

对于 Debian 以及 Ubuntu 可以使用以下命令进行安装（PostgreSQL 12）：

复制sudoapt-get -y install postgresql-12-cron1.

另外，我们也可以使用源码进行编译安装：

复制git clone https://github.com/citusdata/pg_cron.git cd pg_cron # Ensure pg_config is in your path, e.g. exportPATH=/usr/pgsql-12/bin:$PATH make&&sudoPATH=$PATHmakeinstall1.2.3.4.5. 配置 pg_cron

为了在启动 PostgreSQL 时运行 pg_cron 后台工作进程，我们需要将 pg_cron 添加到 postgresql.conf 文件中的 shared_preload_libraries配置项。默认情况下，pg_cron 后台进程使用 postgres 数据库获取所需的元数据。但是我们也可以使用 cron.database_name 配置参数进行设置。

复制shared_preload_libraries =pg_cron cron.database_name =postgres1.2.

重启 PostgreSQL：

复制sudo service postgresql-12 restart1.

然后我们可以使用以下命令创建 pg_cron 函数以及元数据相关的表：

复制-- 使用 superuser 运行以下命令 CREATE EXTENSION pg_cron; -- 可选操作，为其他用户授予访问权限 GRANTUSAGEONSCHEMA cron TO username;1.2.3.4.5.

方法四：pg_timetable

pg_timetable 是由 CYBERTEC 公司开发的 PostgreSQL 作业调度程序，提供了灵活的配置方式和许多高级功能。包括由多个任务组成的任务链、云南idc服务商支持 SQL 命令和可执行程序、内置任务（例如发送邮件）、完全基于数据库的配置和日志功能、cron 风格的计划调度、并发执行的保护等。

安装 pg_timetable

首先，我们可以使用官方发布的二进制安装程序安装 pg_timetable，目前支持 Windows、Linux 以及 macOS 操作系统。

另外，官方的 docker 镜像可以点此下载。

master 分支的 latest 标签是最新版本，使用命令行的运行方式如下：

复制docker run --rm \ cybertecpostgresql/pg_timetable:latest \ -h 10.0.0.3 -p 54321 -c worker0011.2.3.

指定环境变量的方式如下：

复制docker run --rm \ -e PGTT_PGHOST=10.0.0.3 \ -e PGTT_PGPORT=54321\ cybertecpostgresql/pg_timetable:latest \ -c worker0011.2.3.4.5.

除此之外，我们也可以使用源码进行编译安装。首先下载并安装 Go 语言环境，然后使用 go get 命令拷贝 pg_timetable 源码：

复制$ env GIT_TERMINAL_PROMPT=1go get github.com/cybertec-postgresql/pg_timetable/ Username forhttps://github.com:<Github Username> Password forhttps://cyberboy@github.com:<Github Password>1.2.3.

运行 pg_timetable：

复制$ cd ~/go/src/github.com/cybertec-postgresql/pg_timetable/ $ go run main.go --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpwd1.2.

或者，也可以编译成二进制程序并运行：

复制$ go build $ ./pg_timetable --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpwd1.2.

如果想要运行项目中的所有测试，可以执行以下命令：

复制$ cd ~/go/src/github.com/cybertec-postgresql/pg_timetable/ $ go get github.com/stretchr/testify/ $ go test ./...1.2.3.

也可以使用 postgres docker 镜像运行测试：

复制$ RUN_DOCKER=true go test ./...1. 使用 pg_timetable

pg_timetable 独立于 PostgreSQL 服务器运行，相当于一个客户端进程。安装完成之后，执行以下命令运行 pg_timetable 程序：

复制# ./pg_timetable Application Options: -c, --clientname= Unique name for application instance -v, --verbose Show verbose debug information [$PGTT_VERBOSE] -h, --host= PG config DB host(default: localhost)[$PGTT_PGHOST] -p, --port= PG config DB port (default: 5432)[$PGTT_PGPORT] -d, --dbname= PG config DB dbname (default: timetable)[$PGTT_PGDATABASE] -u, --user= PG config DB user (default: scheduler)[$PGTT_PGUSER] -f, --file= SQL script file to execute during startup --password= PG config DB password (default: somestrong)[$PGTT_PGPASSWORD] --sslmode=[disable|require] What SSL priority use for connection (default: disable) --pgurl= PG config DB url [$PGTT_URL] --init Initialize database schema and exit. Can be used with --upgrade --upgrade Upgrade database to the latest version --no-program-tasks Disable executing of PROGRAM tasks [$PGTT_NOPROGRAMTASKS]1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.

源代码的 sample 目录中提供了大量的示例可以参考。以下命令可以创建一个 8 月份 00:05 运行“MyJob”的定时任务：

复制SELECT timetable.job_add(MyJob,SELECT public.my_func(),NULL,SQL,5 0 * 8 *, live :=TRUE);1.

以下命令表示从 0 点到 20 点，每两个小时的 23 分时运行“MyJob”任务：

复制SELECT timetable.job_add(MyJob,SELECT public.my_func(),NULL,SQL,23 0-20/2 * * *, live :=TRUE);1.

pg_timetable 计划任务的完整配置由 3 个阶段组成：

第一个阶段用于配置 base_task，定义需要执行的操作。包括 SQL 语句、外部程序以及内置的操作。第二个阶段用于配置 task_chain，定义一组顺序执行的基本任务。第三阶段用于配置 chain_execution_config，定义任务链的执行计划。

此外，为了给基本任务传递控制参数，任务链中的任务都可以附带一个执行参数。详细的配置方法和案例可以参考官方网站。

总结

本文介绍了在 PostgreSQL 数据库中实现定时任务的 4 种方法，包括操作系统定时任务、pgAgent 代理、pg_cron 插件以及 pg_timetable 工具。