在Posgresql中使用pg_cron進行定時任務

https://github.com/citusdata/pg_cron
一直都使用作業系統的 crontab 來執行 PostgreSQL 例行維護作業或資料整理工作。直到前陣子又被問到這個問題，才發現一直都不知道 pg_cron 這個好用的套件。
簡單來說，就是可以在資料庫內自行維護排程的工作，不只是管理者（postgres），也可以授權給一般使用者自行排程。
個人覺得使用上算是很直覺了，專案 README 的說明就很足夠了，上面也有一些例子可以參考。這邊就再提供一個簡單的例子。

安裝
作業系統的部份就不詳述，參考專案 README 即可，記得要搭配好你的資料庫版本即可。

設定 postgresql.conf：

# add to postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
要注意的是 cron.database_name 指的是排程的工作列表要放在哪個資料庫裡，只能填一個資料庫。如果你只需要管理者的維護作業，可能使用 postgres 是最方便的；如果是資料庫多個使用者都需要使用，為了安全性，另開一個資料庫來處理也是可以考慮的。

設定好要重新啟動資料庫服務。

進入你設定的資料庫中：

CREATE EXTENSION pg_cron;
範例
為了可以馬上驗證功能，這個範例的行為是：建立一個資料表 cronlog，每分鐘 INSERT 一筆資料。

建立 cronlog 資料表：

CREATE TABLE cronlog (logtime TIMESTAMP);
建立排程：

SELECT cron.schedule('*/1 * * * *', $$INSERT INTO cronlog VALUES (now())$$);
 schedule
----------
        1
參數最基本就兩個資訊，第一個是排程設定，和 crontab 的方式一樣，不確定的話可使用 crontab.guru 來幫助你設定；第二個則是排程要執行的 SQL 查詢語句，注意只能是單個查詢語句唷。

結果欄位 schedule 出現的數字是排程工作的 jobid。你可以在 cron.job 這個資料表中看到目前所有你可以看得到的設定。

SELECT * FROM cron.job;
到這裡排程本身就設定完了，隔一分鐘看看有沒有資料出現：

SELECT * FROM cronlog;
如果沒有資料的話，可能是有認證的問題。因為 pg_cron 的行為是另建一個連線去執行指定的排程工作，你可以排本機 localhost，或遠端主機（所以你可以有一台控制中心安裝 pg_cron 就好）。

我們在設定排程時並沒有輸入密碼，cron.job 資料表則會列出執行的資料庫標的與使用者，所以你需要輸入密碼或變更連線權限。

輸入密碼的方式是修改 pgpass （記得是要改 postgres 使用者的 ~/.pgpass），連線權限則要看你的 pg_hba.conf 中對於連線主機的認證方式如何，兩者都只能靠你自己處理囉。

測試完成
如果你確認每分鐘都有資料在 cronlog 出現的話，記得要把排程取消唷：

SELECT cron.unschedule(1);
參數是該排程的 jobid。

其他
這裡面唯一不直覺的是時區，它並沒有考慮時區，都視為 GMT。以我們台北時區來說，是 GMT+8，設定排程時：

0 5 * * *
將會是每天的 13 時 0 分啟動排程。

另外由於目前不支援單一排程多個 SQL 指令，所以如果有一系列的指令要執行，請寫成 Function，再由排程執行該 Function。

試試看吧！