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。
試試看吧!