详解PostgreSQL实现定时任务的4种⽅法
⽬录
操作系统定时任务
pgAgent
安装 pgAgent
创建定时任务
pg_cron
安装 pg_cron
配置 pg_cron
pg_timetable
安装 pg_timetable
使⽤ pg_timetable
总结
数据库定时任务可以⽤于实现定期的备份、统计信息采集、数据汇总、数据清理与优化等。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 github/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 添加到 f ⽂件中的
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 \
git使用详解
-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/cybertec-postgresql/pg_timetable/
Username for 'github': <Github Username>
Password for 'cyberboy@github': <Github Password>
运⾏ pg_timetable：
$ cd ~/go/src/github/cybertec-postgresql/pg_timetable/
$ 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/cybertec-postgresql/pg_timetable/
$ go get github/stretchr/testify/
$ go test ./...

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。