一、简介

pg_timetable是 PostgreSQL 的高级作业调度程序，与cron等传统调度程序相比具有许多优势。它完全是数据库驱动的，并提供了一些高级概念。

1.1. 主要特点

任务可以链式排列
一条链可以由内置命令、SQL 和可执行文件组成
参数可以传递给链
错过的任务（可能由于停机）可以自动重试
支持可配置的重复
内置任务，例如发送电子邮件等。
完全数据库驱动的配置
完全支持数据库驱动的日志记录
PostgreSQL 服务器时区的 Cron 式调度
可选的并发保护
任务和链可以有执行超时设置

1.2. 快速开始

下载 pg_timetable 可执行文件

确保您的 PostgreSQL 服务器已启动并正在运行，并且具有CREATE具有目标数据库权限的角色，例如

my_database=> CREATE user scheduler WITH PASSWORD 'somestrong';
my_database=> GRANT CREATE ON DATABASE my_database TO scheduler;

创建一个新作业，例如VACUUM每晚在 Postgres 服务器时区 00:30运行

my_database=> SELECT timetable.add_job('frequent-vacuum', '30 * * * *', 'VACUUM');
add_job
---------
      3
(1 row)

运行pg_timetable

# pg_timetable postgresql://scheduler:somestrong@localhost/my_database --clientname=vacuumer

1.3. 命令行选项

# ./pg_timetable

Application Options:
  -c, --clientname=                                Unique name for application instance [$PGTT_CLIENTNAME]
      --config=                                    YAML configuration file
      --no-program-tasks                           Disable executing of PROGRAM tasks [$PGTT_NOPROGRAMTASKS]
  -v, --version                                    Output detailed version information [$PGTT_VERSION]

Connection:
  -h, --host=                                      PostgreSQL host (default: localhost) [$PGTT_PGHOST]
  -p, --port=                                      PostgreSQL port (default: 5432) [$PGTT_PGPORT]
  -d, --dbname=                                    PostgreSQL database name (default: timetable) [$PGTT_PGDATABASE]
  -u, --user=                                      PostgreSQL user (default: scheduler) [$PGTT_PGUSER]
      --password=                                  PostgreSQL user password [$PGTT_PGPASSWORD]
      --sslmode=[disable|require]                  Connection SSL mode (default: disable) [$PGTT_PGSSLMODE]
      --pgurl=                                     PostgreSQL connection URL [$PGTT_URL]
      --timeout=                                   PostgreSQL connection timeout (default: 90) [$PGTT_TIMEOUT]

Logging:
      --log-level=[debug|info|error]               Verbosity level for stdout and log file (default: info)
      --log-database-level=[debug|info|error|none] Verbosity level for database storing (default: info)
      --log-file=                                  File name to store logs
      --log-file-format=[json|text]                Format of file logs (default: json)
      --log-file-rotate                            Rotate log files
      --log-file-size=                             Maximum size in MB of the log file before it gets rotated (default: 100)
      --log-file-age=                              Number of days to retain old log files, 0 means forever (default: 0)
      --log-file-number=                           Maximum number of old log files to retain, 0 to retain all (default: 0)

Start:
  -f, --file=                                      SQL script file to execute during startup
      --init                                       Initialize database schema to the latest version and exit. Can be used
                                                   with --upgrade
      --upgrade                                    Upgrade database to the latest version
      --debug                                      Run in debug mode. Only asynchronous chains will be executed

Resource:
      --cron-workers=                              Number of parallel workers for scheduled chains (default: 16)
      --interval-workers=                          Number of parallel workers for interval chains (default: 16)
      --chain-timeout=                             Abort any chain that takes more than the specified number of
                                                   milliseconds
      --task-timeout=                              Abort any task within a chain that takes more than the specified number
                                                   of milliseconds

REST:
      --rest-port=                                 REST API port (default: 0) [$PGTT_RESTPORT]

二、项目背景

pg_timetable 项目于 2019 年启动，以满足 Cybertec 的内部调度需求。

有关项目动机和设计目标的更多背景信息，请参阅宣布该项目和以下功能更新的原始博客文章系列。

Cybertec 还为 pg_timetable 提供商业 9 到 5 和 24/7 支持。

2.1.安装

pg_timetable与最新支持的PostgreSQL 版本兼容：11、12、13、14（稳定版）；15（开发）

3.1. 官方发布包

您可以在官方发布页面上找到适合您平台的二进制包。目前，Windows、Linux和macOS软件包均可用

3.2. docker部署

在 Docker 中运行pg_timetable：

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

使用环境变量在 Docker 中运行pg_timetable ：

docker run --rm \
-e PGTT_PGHOST=10.0.0.3 \
-e PGTT_PGPORT=5432 \
cybertecpostgresql/pg_timetable:latest \
-c worker001

例子：

我们来看看这个命令行：
pg_timetable -c timetable -u demo --password=test123 -d demo -p 5432

-c 指定客户端程序名
-u 用户名
--password 密码
-d 数据库名
-p 连接的端口号

启动这个进程以后，数据库demo里头就有timetable这个schema。我们就用一个简单的例子来验证它的功能。
SELECT timetable.add_job(
    job_name            => 'notify every minute',
    job_schedule        => '* * * * *',
    job_command         => 'SELECT pg_notify($1, $2)',
    job_parameters      => '[ "TT_CHANNEL", "Ahoj from SQL base task" ]' :: jsonb,
    job_kind            => 'SQL'::timetable.command_kind,
    job_client_name     => NULL,
    job_max_instances   => 1,
    job_live            => TRUE,
    job_self_destruct   => FALSE,
    job_ignore_errors   => TRUE
) as chain_id;

执行完这个之后，就有一个job: notify every minute, 会每隔一分钟执行一次。主要就是往"tt_channel"上发送一则通知。

删除这个job:

demo=# select timetable.delete_job('notify every minute');
 delete_job
------------
 t
(1 row)

弄一个简单点儿的：

第一步：准备一张表

第二步：创建job

第三步：验证

demo=# create table t(id int); 
CREATE TABLE

demo=# SELECT timetable.add_job('insert_every_minute', '* * * * *', ' INSERT INTO  t values(100*random()::int)'); # 定时任务 一分钟往t表里面插入一次数据
 add_job
---------
       3
(1 row)

demo=# select count(*) from t;  # 查看t表的行数，后续的行数会越来越多
 count
-------
     1
(1 row)

3.3 docker-compose部署

cat docker-compose.yml
version: '3.4'
services:
  pg_timetable:
    image: cybertecpostgresql/pg_timetable:latest
    environment:
      - PGTT_PGHOST=192.168.10.10
      - PGTT_PGPORT=5432
      - PGTT_USER=scheduler
      - PGTT_PASSWORD=somestrong
      - PGTT_PGDATABASE=my_database
    command: -c work001
docker-compose up -d  # 直接运行即可，在运行之前给予用户权限 确定相关的库

3.4 源码包安装

在您的系统上下载并安装Go。

克隆pg_timetable存储库：

$ git clone https://github.com/cybertec-postgresql/pg_timetable.git
$ cd pg_timetable

运行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

（可选）在项目的所有子文件夹中运行测试：

$ psql --command="CREATE USER scheduler PASSWORD 'somestrong'"
$ createdb --owner=scheduler timetable
$ go test -failfast -timeout=300s -count=1 -p 1 ./...

三、组件

pg_timetable中的调度包含三个不同的抽象级别，以方便与其他参数或附加调度的重用。

命令:

基础级别command定义要做什么。
任务:

第二级，任务，代表运行其中一个命令的链元素（步骤）。对于**任务，**我们定义命令的顺序、传递的参数（如果有）以及如何处理错误。
链:

第三层代表形成任务链的连接任务。Chain定义了作业是否、何时以及多久执行一次。

4.1. 命令

目前，有三种不同类型的命令：

SQL

SQL 片段。开始清理、刷新物化视图或处理数据。
PROGRAM

外部命令。任何可以作为外部二进制文件调用的东西，包括 shell，例如bash、等。外部命令将使用 golang 的exec.CommandContextpwsh调用。
BUILTIN

内部命令。pg_timetable中包含的预构建功能。这些包括：无操作，睡觉，日志,发送邮件,下载，从文件复制，复制到文件,关机。

4.2. 任务

下一个构建块是一个任务，它简单地表示链命令列表中的一个步骤。组合在链中的任务的一个示例是：

从服务器下载文件
导入文件
运行聚合
构建报告
从磁盘中删除文件

笔记

pg_timetable中链上的所有任务都在一个事务内执行。但是，请注意没有机会回滚PROGRAM和BUILTIN任务。

4.2.1. 表时间表.任务

chain_id bigint

NULL如果任务被视为禁用，则链接到链

task_order DOUBLE PRECISION

指示链中任务的顺序。

kind timetable.command_kind

命令的类型。可以是SQL（默认）、PROGRAM或BUILTIN。

command text

包含 SQL 命令、应用程序路径或将执行的BUILTIN命令的名称。

run_as text

执行任务时应扮演的角色。

database_connection text

应使用的外部数据库的连接字符串。

ignore_error boolean

指定遇到错误后是否应继续执行下一个任务（默认值：false）。

autonomous boolean

指定任务是否应在链事务外执行。适用于VACUUM、等。CREATE DATABASE``CALL

timeout integer

中止链中任何耗时超过指定毫秒数的任务。

警告

如果任务已配置ignore_error为true（默认值为），则即使链中的任务失败，false工作进程也会报告执行成功。

如上所述，命令是简单的骨架（例如发送电子邮件、真空等）。在大多数情况下，必须通过将输入参数传递给执行来使它们生效。

4.2.2. 表时间表.参数

task_id bigint

任务的 ID。
order_id integer

参数的顺序。几个参数按照顺序一一处理。
value jsonb

包含参数的 JSON 值。

4.2.3. 参数值格式

根据命令种类参数可以由不同的JSON值表示。

种类

模式例子
SQL

array``'[ "one", 2, 3.14, false ]'::jsonb
PROGRAM

array of strings``'["-x", "Latin-ASCII", "-o", "orte_ansi.txt", "orte.txt"]'::jsonb
BUILTIN: Sleep

integer``'5' :: jsonb
BUILTIN: Log

any``'"WARNING"'::jsonb '{"Status": "WARNING"}'::jsonb
BUILTIN: SendMail

object``'{ "username": "user@example.com", "password": "password", "serverhost": "smtp.example.com", "serverport": 587, "senderaddr": "user@example.com", "ccaddr": ["recipient_cc@example.com"], "bccaddr": ["recipient_bcc@example.com"], "toaddr": ["recipient@example.com"], "subject": "pg_timetable - No Reply", "attachment": ["/temp/attachments/Report.pdf","config.yaml"], "attachmentdata": [{"name": "File.txt", "base64data": "RmlsZSBDb250ZW50"}], "msgbody": "<h2>Hello User,</h2> <p>check some attachments!</p>", "contenttype": "text/html; charset=UTF-8" }'::jsonb
BUILTIN: Download

object``'{ "workersnum": 2, "fileurls": ["http://example.com/foo.gz", "https://example.com/bar.csv"], "destpath": "." }'::jsonb
BUILTIN: CopyFromFile

object``'{ "sql": "COPY location FROM STDIN", "filename": "download/orte_ansi.txt" }'::jsonb
BUILTIN: CopyToFile

object``'{ "sql": "COPY location TO STDOUT", "filename": "download/location.txt" }'::jsonb
BUILTIN: Shutdown

值被忽略
BUILTIN: NoOp

值被忽略

4.3. 链

一旦安排了任务，就必须将它们安排为一个链。为此，pg_timetable基于增强的****cron字符串构建，同时添加多个配置选项。

4.3.1. 表时间表.链

chain_name text

链的唯一名称。

run_at timetable.cron

Postgres 服务器时区或, ,子句的标准cron样式值。@after``@every``@reboot

max_instances integer

该链可能同时运行的实例数量。

timeout integer

中止任何花费超过指定毫秒数的链。

live boolean

控制链在达到其计划后是否可以执行。

self_destruct boolean

成功执行后自毁链条。失败的链将按照时间表再次执行。

exclusive_execution boolean

指定在所有其他链暂停时是否应独占执行该链。

client_name text

指定哪个客户端应该执行该链。将其设置为NULL以允许任何客户端。

timeout integer

中止花费超过指定毫秒数的链。

on_error

保存发生错误时要执行的 SQL。如果任务产生错误，则标记为，ignore_error则不执行任何操作。

笔记

pg_timetable中的所有链都按照 PostgreSQL 服务器时区进行调度。添加新链时，您可以更改当前会话 的时区，例如

SET TIME ZONE 'UTC';

-- Run VACUUM at 00:05 every day in August UTC
SELECT timetable.add_job('execute-func', '5 0 * 8 *', 'VACUUM');

5.开始使用

可以在示例中找到各种示例。如果您想从不同的调度程序迁移，可以使用从其他调度程序迁移一章中的脚本。

5.1. 添加简单的工作

在现实世界中，通常使用简单的作业就足够了。在这个术语下我们理解：

工作是一条链，其中只有一个任务（步骤）；
它不使用复杂的逻辑，而是使用简单的命令；
它不需要复杂的事务处理，因为一项任务作为单个事务隐式执行。

对于这样一组链，我们引入了一个特殊的函数timetable.add_job()。

timetable.add_job(job_name, job_schedule, job_command, ...) 返回 BIGINT

创建一个简单的单任务链参数:job_name ( text ) –链和命令的唯一名称。job_schedule ( timetable.cron ) – Postgres 服务器时区的 сron 语法时间表job_command ( text ) – 将执行的 SQL。job_parameters ( jsonb ) – chain命令的参数。默认值：NULL.job_kind ( timetable.command_kind ) – 命令类型：SQL、PROGRAM或BUILTIN。默认值：SQL.job_client_name ( text ) – 指定哪个客户端应执行该链。将其设置为NULL以允许任何客户端。默认值：NULL.job_max_instances ( integer ) – 该链可以同时运行的实例数量。默认值：NULL.job_live ( boolean ) – 控制链在达到其计划后是否可以执行。默认值：TRUE.job_self_destruct ( boolean ) – 执行后自毁链。默认值：FALSE.job_ignore_errors ( boolean ) – 忽略执行期间的错误。默认值：TRUE.job_exclusive ( boolean ) – 以独占模式执行链。默认值：FALSE.退货:创建的链的ID返回类型:整数

5.2. 例子

public.my_func()8 月 Postgres 服务器时区每天 00:05运行：

SELECT timetable.add_job('execute-func', '5 0 * 8 *', 'SELECT public.my_func()');

每天 0 点到 20 点之间每隔 2 小时运行VACUUM Postgres 服务器时区：
```
SELECT timetable.add_job('run-vacuum', '23 0-20/2 * * *', 'VACUUM');
```

每 2 小时刷新一次物化视图：

SELECT timetable.add_job('refresh-matview', '@every 2 hours', 'REFRESH MATERIALIZED VIEW public.mat_view');

pg_timetable重启后清除日志表：

SELECT timetable.add_job('clear-log', '@reboot', 'TRUNCATE timetable.log');

使用reindexdb实用程序在周日午夜重新索引 Postgres 服务器时区：

在默认用户下使用默认数据库（无命令行参数）

SELECT timetable.add_job('reindex', '0 0 * * 7', 'reindexdb', job_kind := 'PROGRAM');

指定目标数据库和表，并且要详细

SELECT timetable.add_job('reindex', '0 0 * * 7', 'reindexdb',
    '["--table=foo", "--dbname=postgres", "--verbose"]'::jsonb, 'PROGRAM');

通过bashshell使用环境变量传递密码

SELECT timetable.add_job('reindex', '0 0 * * 7', 'bash',
    '["-c", "PGPASSWORD=5m3R7K4754p4m reindexdb -U postgres -h 192.168.0.221 -v"]'::jsonb,
    'PROGRAM');

postgresql高级作业调度程序 pg-timetable