详解PostgreSQL 实现定时任务的 4 种方法

数据库定时任务可以用于实现定期的备份、统计信息采集、数据汇总、数据清理与优化等。postgresql 没有提供类似 oracle、mysql 以及 microsoft sql sever 的内置任务调度功能，因此本文给大家介绍一下 postgresql 数据库中实现定时任务的 4 种方法。

操作系统定时任务

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

crontab -e

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

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

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

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

0 0 * * * pg_dump --no-password -u user db_name > backup.sql

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

chmod 600 .pgpass

pgagent

pgagent 是一个用于 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

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

-- 每周六 3:30am (gmt) 删除历史记录
select cron.schedule('30 3 * * 6', $$delete from events where event_time < now() - interval '1 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
(1 row)

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

安装 pg_cron

pg_cron 目前只支持 linux 操作系统。对于 red hat、centos 以及 fedora 等操作系统可以使用以下命令进行安装（postgresql 12）：

sudo yum install -y pg_cron_12

对于 debian 以及 ubuntu 可以使用以下命令进行安装（postgresql 12）：

sudo apt-get -y install postgresql-12-cron

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

git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# ensure pg_config is in your path, e.g.
export path=/usr/pgsql-12/bin:$path
make && sudo path=$path make install

配置 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 = 'postgres'

重启 postgresql：

sudo service postgresql-12 restart

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

-- 使用 superuser 运行以下命令
create extension pg_cron;

-- 可选操作，为其他用户授予访问权限
grant usage on schema cron to username;

pg_timetable

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

安装 pg_timetable

首先，我们可以使用官方发布的二进制安装，目前支持 windows、linux 以及 macos 操作系统。

另外，官方的 docker 镜像可以。

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

docker run --rm \
 cybertecpostgresql/pg_timetable:latest \
 -h 10.0.0.3 -p 54321 -c worker001

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

docker run --rm \
 -e pgtt_pghost=10.0.0.3 \
 -e pgtt_pgport=54321 \
 cybertecpostgresql/pg_timetable:latest \
 -c worker001

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

$ env git_terminal_prompt=1 go get github.com/cybertec-postgresql/pg_timetable/
username for 'https://github.com': <github username>
password for 'https://cyberboy@github.com': <github password>

运行 pg_timetable：

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

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

$ go build
$ ./pg_timetable --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpwd

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

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

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

$ run_docker=true go test ./...

使用 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]

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

select timetable.job_add('myjob', 'select public.my_func()' , null, 'sql', '5 0 * 8 *', live := true);

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

select timetable.job_add('myjob', 'select public.my_func()' , null, 'sql', '23 0-20/2 * * *', live := true);

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

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

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

总结

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

到此这篇关于postgresql 实现定时任务的 4 种方法的文章就介绍到这了,更多相关postgresql 定时任务内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！