數(shù)據(jù)庫(kù)定時(shí)任務(wù)可以用于實(shí)現(xiàn)定期的備份、統(tǒng)計(jì)信息采集、數(shù)據(jù)匯總、數(shù)據(jù)清理與優(yōu)化等。

PostgreSQL 沒(méi)有提供類(lèi)似 Oracle、MySQL 以及 Microsoft SQL Sever 的內(nèi)置任務(wù)調(diào)度功能，因此本文給大家介紹一下 PostgreSQL 數(shù)據(jù)庫(kù)中實(shí)現(xiàn)定時(shí)任務(wù)的 4 種方法。

方法一：操作系統(tǒng)定時(shí)任務(wù)

Linux 定時(shí)任務(wù)（crontab）或者 Windows 任務(wù)計(jì)劃程序（Task Scheduler）為我們提供了一個(gè)實(shí)現(xiàn)定時(shí)任務(wù)傳統(tǒng)的方法。以 crontab 為例，我們可以使用以下命令編輯任務(wù)列表：

crontab -e

然后在打開(kāi)的文件中使用以下格式增加一行數(shù)據(jù)：

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

其中的前五個(gè)字段表示執(zhí)行命令的時(shí)間，可以使用星號(hào)（*）匹配所有的時(shí)間。例如，將 <month> 設(shè)置為星號(hào)表示每個(gè)月都執(zhí)行命令。

舉例來(lái)說(shuō)，輸入以下內(nèi)容表示每天零點(diǎn)執(zhí)行數(shù)據(jù)庫(kù)邏輯備份操作。

00 * * * pg_dump --no-password -U user db_name > backup.sql

為了安全起見(jiàn)不要直接輸入密碼，而是應(yīng)該將密碼加入 .pgpass 文件，并且將該文件的權(quán)限設(shè)置為僅當(dāng)前用戶可見(jiàn)：

chmod600 .pgpass

方法二：pgAgent

pgAgent 是一個(gè)用于 PostgreSQL 數(shù)據(jù)庫(kù)的任務(wù)調(diào)度代理，能夠基于復(fù)雜的調(diào)度計(jì)劃運(yùn)行多步驟的批處理、shell 腳本以及 SQL 命令。對(duì)于 Unix/Linux 系統(tǒng)，pgAgent 以后臺(tái)進(jìn)程的方式運(yùn)行；對(duì)于 Windows 系統(tǒng)，pgAgent 以服務(wù)的形式運(yùn)行。

安裝 pgAgent

PgAdmin 4 管理工具集成了 pgAgent 的功能，但是這兩者需要單獨(dú)安裝。我們可以通過(guò)官方網(wǎng)站下載 PgAdmin 4 以及 pgAgent。具體的安裝步驟和注意事項(xiàng)可以參考官網(wǎng)文檔。安裝完成之后，我們可以在 PgAdmin 4 左側(cè)導(dǎo)航樹(shù)中看到“pgAgent Jobs”節(jié)點(diǎn)。

創(chuàng)建定時(shí)任務(wù)

右鍵點(diǎn)擊“pgAgent Jobs”節(jié)點(diǎn)，選擇“Create” > “pgAgent Job”創(chuàng)建一個(gè)新的定時(shí)任務(wù)。

其中，“General”頁(yè)面可以輸入一些基本信息，包括任務(wù)的名稱(chēng)。“Steps”頁(yè)面可以設(shè)置多個(gè)操作步驟，包括執(zhí)行的腳本或者 SQL 語(yǔ)句等。“Schedules”頁(yè)面用于定義任務(wù)執(zhí)行的時(shí)間計(jì)劃。“SQL”頁(yè)面可以顯示創(chuàng)建或者修改任務(wù)的語(yǔ)句。

點(diǎn)擊“Save”按鈕保存設(shè)置并創(chuàng)建任務(wù)，然后我們就可以在“pgAgent Job”節(jié)點(diǎn)下看到創(chuàng)建的任務(wù)。

方法三：pg_cron

pg_cron 是由 citusdata 公司開(kāi)發(fā)的一個(gè) PostgreSQL 定時(shí)任務(wù)插件（類(lèi)似于 Oracle 中的 DBMS_SCHEDULER）。pg_cron 作為一個(gè)后臺(tái)工作進(jìn)程運(yùn)行，使用類(lèi)似 cron 的編輯語(yǔ)法，允許直接在數(shù)據(jù)庫(kù)中執(zhí)行定時(shí)任務(wù)。例如：

-- 每周六 3:30am (GMT) 刪除歷史記錄
SELECT cron.schedule('30 3 * * 6', $$DELETEFROM events WHERE event_time <now()-interval'1 week'$$);
 schedule
----------
42

-- 每天 10:00am (GMT) 執(zhí)行清理作業(yè)
SELECT cron.schedule('nightly-vacuum','0 10 * * *','VACUUM');
 schedule
----------
43

-- 將清理作業(yè)修改為 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum','0 3 * * *','VACUUM');
 schedule
----------
43

-- 停止計(jì)劃中的任務(wù)
SELECT cron.unschedule('nightly-vacuum');
 unschedule 
------------
 t
(1row)

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

安裝 pg_cron

pg_cron 目前只支持 Linux 操作系統(tǒng)。對(duì)于 Red Hat、CentOS 以及 Fedora 等操作系統(tǒng)可以使用以下命令進(jìn)行安裝（PostgreSQL 12）：

sudo yum install -y pg_cron_12

對(duì)于 Debian 以及 Ubuntu 可以使用以下命令進(jìn)行安裝（PostgreSQL 12）：

sudoapt-get -y install postgresql-12-cron

另外，我們也可以使用源碼進(jìn)行編譯安裝：

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=$PATHmakeinstall

配置 pg_cron

為了在啟動(dòng) PostgreSQL 時(shí)運(yùn)行 pg_cron 后臺(tái)工作進(jìn)程，我們需要將 pg_cron 添加到 postgresql.conf 文件中的 shared_preload_libraries配置項(xiàng)。默認(rèn)情況下，pg_cron 后臺(tái)進(jìn)程使用 postgres 數(shù)據(jù)庫(kù)獲取所需的元數(shù)據(jù)。但是我們也可以使用 cron.database_name 配置參數(shù)進(jìn)行設(shè)置。

shared_preload_libraries ='pg_cron'
cron.database_name ='postgres'

重啟 PostgreSQL：

sudo service postgresql-12 restart

然后我們可以使用以下命令創(chuàng)建 pg_cron 函數(shù)以及元數(shù)據(jù)相關(guān)的表：

-- 使用 superuser 運(yùn)行以下命令
CREATE EXTENSION pg_cron;

-- 可選操作，為其他用戶授予訪問(wèn)權(quán)限
GRANTUSAGEONSCHEMA cron TO username;

方法四：pg_timetable

pg_timetable 是由 CYBERTEC 公司開(kāi)發(fā)的 PostgreSQL 作業(yè)調(diào)度程序，提供了靈活的配置方式和許多高級(jí)功能。包括由多個(gè)任務(wù)組成的任務(wù)鏈、支持 SQL 命令和可執(zhí)行程序、內(nèi)置任務(wù)（例如發(fā)送郵件）、完全基于數(shù)據(jù)庫(kù)的配置和日志功能、cron 風(fēng)格的計(jì)劃調(diào)度、并發(fā)執(zhí)行的保護(hù)等。

安裝 pg_timetable

首先，我們可以使用官方發(fā)布的二進(jìn)制安裝程序安裝 pg_timetable，目前支持 Windows、Linux 以及 macOS 操作系統(tǒng)。

另外，官方的 docker 鏡像可以點(diǎn)此下載。

master 分支的 latest 標(biāo)簽是最新版本，使用命令行的運(yùn)行方式如下：

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

指定環(huán)境變量的方式如下：

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

除此之外，我們也可以使用源碼進(jìn)行編譯安裝。首先下載并安裝 Go 語(yǔ)言環(huán)境，然后使用 go get 命令拷貝 pg_timetable 源碼：

$ env GIT_TERMINAL_PROMPT=1go get github.com/cybertec-postgresql/pg_timetable/
Username for'https://github.com':<Github Username>
Password for'https://cyberboy@github.com':<Github Password>

運(yùn)行 pg_timetable：

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

或者，也可以編譯成二進(jìn)制程序并運(yùn)行：

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

如果想要運(yùn)行項(xiàng)目中的所有測(cè)試，可以執(zhí)行以下命令：

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

也可以使用 postgres docker 鏡像運(yùn)行測(cè)試：

$ RUN_DOCKER=true go test ./...

使用 pg_timetable

pg_timetable 獨(dú)立于 PostgreSQL 服務(wù)器運(yùn)行，相當(dāng)于一個(gè)客戶端進(jìn)程。安裝完成之后，執(zhí)行以下命令運(yùn)行 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]

源代碼的 sample 目錄中提供了大量的示例可以參考。以下命令可以創(chuàng)建一個(gè) 8 月份 00:05 運(yùn)行“MyJob”的定時(shí)任務(wù)：

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

以下命令表示從 0 點(diǎn)到 20 點(diǎn)，每?jī)蓚€(gè)小時(shí)的 23 分時(shí)運(yùn)行“MyJob”任務(wù)：

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

pg_timetable 計(jì)劃任務(wù)的完整配置由 3 個(gè)階段組成：

• 第一個(gè)階段用于配置 base_task，定義需要執(zhí)行的操作。包括 SQL 語(yǔ)句、外部程序以及內(nèi)置的操作。
• 第二個(gè)階段用于配置 task_chain，定義一組順序執(zhí)行的基本任務(wù)。
• 第三階段用于配置 chain_execution_config，定義任務(wù)鏈的執(zhí)行計(jì)劃。

此外，為了給基本任務(wù)傳遞控制參數(shù)，任務(wù)鏈中的任務(wù)都可以附帶一個(gè)執(zhí)行參數(shù)。詳細(xì)的配置方法和案例可以參考官方網(wǎng)站。

總結(jié)

本文介紹了在 PostgreSQL 數(shù)據(jù)庫(kù)中實(shí)現(xiàn)定時(shí)任務(wù)的 4 種方法，包括操作系統(tǒng)定時(shí)任務(wù)、pgAgent 代理、pg_cron 插件以及 pg_timetable 工具。