pg_cron（定時任務） - PolarDB

pg_cron是PolarDB PostgreSQL版支援的一款第三方外掛程式，提供了在PolarDB PostgreSQL版中通過cron文法定時執行SQL的能力。您可以通過SQL語句建立定時任務，並在指定的時間點或時間間隔內自動執行任務。本文介紹了pg_cron外掛程式的背景、原理以及樣本等內容。

背景資訊

定時任務是一種在預定的時間點或時間間隔內自動執行的任務。定時任務通常由一個調度器負責管理，根據預定的時間規則引發任務的執行。常見的時間規則包括每天的特定時間點、每周的特定日期和時間、每月的特定日期和時間等。調度器還可以根據任務的需要設定任務的執行次數和重複間隔。

定時任務的應用情境非常廣泛：

在伺服器端，定時任務常用於定期執行背景工作、產生報告、清理垃圾資料等。
在用戶端，定時任務可以用於更新資料、提醒使用者、執行自動化操作等。

無論是在伺服器端還是用戶端，定時任務都可以減輕人工操作的負擔，提升工作效率。

pg_cron外掛程式的優勢如下：

簡單易用：執行SQL語句即可建立、調度和管理工作，無需額外的編程或配置。
靈活的調度選項：支援按分鐘、小時、日期、星期、月份及其組合進行定時調度。
庫層級的任務管理：定時任務儲存在資料庫中，可以跨多個資料庫共用和管理工作。
並發任務執行：支援同時執行多個任務，提高任務執行效率。
可靠性和容錯性：提供錯誤處理和容錯機制，確保任務的正確執行並提供相應的日誌記錄。

pg_cron外掛程式為資料庫管理員和開發人員提供了一種簡單而強大的方式來自動化執行重複性任務。例如，備份資料、產生報告、定期清理等，從而提升資料庫的管理和開發效率。

前提條件

支援的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（核心小版本14.9.14.0及以上）
PostgreSQL 11（核心小版本1.1.1及以上）

說明

您可通過如下語句查看PolarDB PostgreSQL版的核心小版本的版本號碼：

PostgreSQL 14
```
select version();
```
PostgreSQL 11
```
show polar_version;
```

注意事項

PolarDB PostgreSQL版的核心小版本為14.10.16.0及更低版本時，該外掛程式在使用過程中可能會因叢集連接埠在重啟後發生變化而導致任務失敗。該問題在核心小版本為14.10.16.1及以上版本中得到修複。

原理介紹

定時任務資訊維護

所有的定時任務將會被儲存在cron.job表中，使用者可以通過SQL函數介面新增或刪除定時任務。

pg_cron通過維護JOB LIST和TASK LIST兩個列表進行後台調度和任務執行。資料庫啟動時，根據cron.job表中的工作清單構造JOB LIST和TASK LIST；後續工作清單有更新時，通過觸發器cron.job_cache_invalidate重新整理列表保持JOB LIST和TASK LIST與cron.job表即時一致，確保任務能夠得到正確調度和執行。

定時任務調度執行

WAITING（等待）：預設狀態。如果條件不滿足（非啟用狀態/計劃時間還未到），則跳過該任務的調度，如果條件滿足，則進入START狀態。
START（啟動）：構建任務的串連資訊，並進行串連測試。如果串連成功，則進入CONNECTING狀態，否則進入ERROR狀態。
CONNECTING（串連）：檢查任務是否啟用，串連是否正常。如果所有條件都滿足，則進入SENDING狀態，否則進入ERROR狀態。
SENDING（發送）：檢查任務是否啟用，串連是否正常。如果所有條件滿足，將定時任務文本發送至PolarDB PostgreSQL版伺服器，進入RUNNING狀態，否則進入ERROR狀態。
RUNNING（運行）：檢查任務是否啟用，串連是否正常。如果所有條件都滿足，接收傳回的任務結果並進入DONE狀態，否則跳出等待進入ERROR狀態。
ERROR（錯誤）：任務失敗，重設串連資訊並進入DONE狀態。
DONE（完成）：任務完成，重設任務資訊並重新進入WAITING狀態。

函數列表

說明

出於安全性考慮，只能以高許可權使用者通過下列函數操作cron.job表，普通使用者只有cron.job表的查看許可權。
所有定時任務都儲存於postgres資料庫中，對定時任務的操作需要串連到postgres資料庫進行操作。
定時任務執行的時間為GMT時間（格林威治標準時間），請注意換算時間。

添加定時任務

文法

cron.schedule (
    job_name TEXT,
    schedule TEXT,
    command TEXT
);

參數說明

參數	說明
job_name	任務名稱，可以預設為空白。
schedule	任務計劃周期，格式為標準`cron`格式字串。
command	任務內容，格式為`SQL`字串。

在指定資料庫中添加定時任務

文法

cron.schedule_in_database (
    job_name TEXT,
    schedule TEXT,
    command TEXT,
    db_name TEXT
);

參數說明

參數	說明
job_name	任務名稱。
schedule	任務計劃周期，格式為標準`cron`格式字串。
command	任務內容，格式為`SQL`字串。
db_name	任務執行的目標資料庫。

修改定時任務

文法

cron.alter_job (
    job_id BIGINT,
    schedule TEXT DEFAULT NULL,
    command TEXT DEFAULT NULL,
    db_name TEXT DEFAULT NULL,
    active BOOLEAN DEFAULT NULL
);

參數說明

參數	說明
job_id	待修改的任務ID。
schedule	修改後的任務計劃。
command	修改後的任務內容。
db_name	修改後的任務執行資料庫。
active	修改後任務是否處於啟用狀態（正常運行）。

刪除定時任務

文法

cron.unschedule(job_id BIGINT);

cron.unschedule(job_name TEXT);

參數說明

參數	說明
job_id	待刪除任務ID。
name	待刪除任務名稱。

使用指南

說明

出於安全性考慮，只能以高許可權使用者執行以下操作。

建立外掛程式

串連資料庫並執行以下命令，建立pg_cron外掛程式。

CREATE EXTENSION pg_cron;

新增定時任務

執行以下命令，為db01資料庫建立一個名為task1的任務，函數將返回任務ID。

SELECT cron.schedule_in_database('task1', '* * * * *', 'SELECT 1', 'db01');

結果顯示如下：

 schedule_in_database
----------------------
                    1
(1 row)

查看定時工作清單

執行以下命令，查看定時工作清單。

SELECT * FROM cron.job;

結果顯示如下：

jobid | schedule  | command  | nodename | nodeport | database | username | active | jobname
-------+-----------+----------+----------+----------+----------+----------+--------+---------
     1 | * * * * * | SELECT 1 | /tmp     |    39361 | db01     | u1       | t      | task1
(1 row)

查看定時任務執行歷史

執行以下命令，查看定時任務執行歷史。

SELECT * FROM cron.job_run_details;

結果顯示如下：

 jobid | runid | job_pid | database | username | command  |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------
     1 |     1 | 4152316 | db01     | u1       | SELECT 1 | succeeded | 1 row          | 2023-10-19 03:55:00.020442+00 | 2023-10-19 03:55:00.021512+00
     1 |     2 | 4152438 | db01     | u1       | SELECT 1 | succeeded | 1 row          | 2023-10-19 03:56:00.006468+00 | 2023-10-19 03:56:00.006822+00
(2 rows)

刪除定時任務

執行以下命令，刪除定時任務。

SELECT cron.unschedule('task1');

結果顯示如下：

 unschedule
------------
 t
(1 row)

刪除外掛程式

執行以下命令，刪除pg_cron外掛程式。

DROP EXTENSION pg_cron;

時間規則樣本

pg_cron使用標準的cron文法，*表示每間隔指定單位時間運行，指定數字表示間隔該數字單位時間運行。

 ┌───────────── 分鐘 (0 - 59)
 │ ┌────────────── 小時 (0 - 23)
 │ │ ┌─────────────── 日期 (1 - 31)
 │ │ │ ┌──────────────── 月份 (1 - 12)
 │ │ │ │ ┌───────────────── 一周中的某一天 (0 - 6) (0到6表示周日到下周六，7 也表示周日）
 │ │ │ │ │              
 * * * * *

在每周六3:30 AM（GMT 時間）刪除到期資料。

SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

在每天10:00 AM（GMT 時間）執行VACUUM。
```
SELECT cron.schedule('0 10 * * *', 'VACUUM;');
```

每分鐘執行SQL。

SELECT cron.schedule('* * * * *', 'SELECT 1;');

每小時的23分鐘執行SQL。

SELECT cron.schedule('23 * * * *', 'SELECT 1;');

每個月的4號執行SQL。

SELECT cron.schedule('* * 4 * *', 'SELECT 1;');