PostgreSQL的擴展 pg_cron
pg_cron 是 PostgreSQL 的一個開源擴展，它允許在數據庫內部使用 cron 語法調度定期任務，是最接近 Oracle DBMS_SCHEDULER 的解決方案。
一 安裝與配置
1 安裝方法
下載路徑：
https://github.com/citusdata/pg_cron/tags
編譯安裝：
cd pg_cron
make && make install
在 PostgreSQL 中配置：
-- 在 postgresql.conf 中添加：
shared_preload_libraries = 'pg_cron'-- 重啟后創建擴展
CREATE EXTENSION pg_cron;
2. 權限配置
-- 創建專用角色
CREATE ROLE cron_job_runner WITH LOGIN;
GRANT USAGE ON SCHEMA cron TO cron_job_runner;-- 配置任務執行權限
ALTER SYSTEM SET cron.database_name = 'your_database';
ALTER SYSTEM SET cron.host = 'localhost';
ALTER SYSTEM SET cron.log_run = 'on';
二、核心功能使用
1. 基本任務調度
-- 每分鐘執行
SELECT cron.schedule('test-job', '* * * * *', 'SELECT now()');-- 每天凌晨3點執行
SELECT cron.schedule('nightly-cleanup', '0 3 * * *', 'DELETE FROM logs WHERE created_at < now() - interval ''30 days''');-- 每周一早上執行
SELECT cron.schedule('weekly-report', '0 9 * * 1', 'CALL generate_weekly_report()');
2. 帶參數的任務
-- 使用美元引用
SELECT cron.schedule('param-job', '0 * * * *', $$UPDATE stats SET value = value + 1 WHERE metric_id = 'page_views'$$);-- 調用存儲過程
SELECT cron.schedule('call-proc', '0 0 * * *', 'CALL refresh_materialized_views(true)');
3. 任務管理
-- 查看所有任務
SELECT * FROM cron.job;-- 查看執行歷史
SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;-- 更新任務計劃
SELECT cron.alter_job(job_id, schedule := '0 4 * * *');-- 刪除任務
SELECT cron.unschedule(jobid);
三、高級功能
1. 任務重試機制
-- 帶錯誤處理的任務
SELECT cron.schedule('retry-job', '*/5 * * * *', $$
BEGIN-- 業務邏輯INSERT INTO important_data SELECT * FROM external_source;
EXCEPTION WHEN OTHERS THENINSERT INTO job_errors VALUES ('retry-job', SQLERRM, now());
END;
$$);
2. 任務依賴鏈
-- 使用表狀態控制流程
SELECT cron.schedule('step1', '0 1 * * *', $$TRUNCATE TABLE staging_data;COPY staging_data FROM '/data/import.csv';UPDATE job_status SET step1_complete = true WHERE job_name = 'daily_import';
$$);SELECT cron.schedule('step2', '0 2 * * *', $$BEGINPERFORM 1 FROM job_status WHERE job_name = 'daily_import' AND step1_complete;IF NOT FOUND THENRAISE EXCEPTION 'Step1 not completed';END IF;-- 處理數據...END;
$$);
3. 分布式任務
-- 在多個節點上調度（需Citus擴展）
SELECT cron.schedule('sharded-job', '0 * * * *', 'SELECT master_distribute_command($cmd$INSERT INTO sharded_events SELECT * FROM collect_events()$cmd$)');
四、監控與維護
1. 監控面板查詢
-- 正在運行的任務
SELECT * FROM cron.job_run_details
WHERE status = 'running';-- 失敗任務統計
SELECT jobid, jobname, COUNT(*) as failures
FROM cron.job_run_details
WHERE status = 'failed'
GROUP BY jobid, jobname
ORDER BY failures DESC;-- 任務執行時長分析
SELECT jobname,avg(end_time - start_time) as avg_duration,max(end_time - start_time) as max_duration
FROM cron.job_run_details
GROUP BY jobname;
2. 維護操作
-- 清理歷史記錄（保留30天）
DELETE FROM cron.job_run_details
WHERE start_time < now() - interval '30 days';-- 臨時禁用所有任務
UPDATE cron.job SET active = false;-- 導出任務配置
COPY (SELECT jobname, schedule, command FROM cron.job)
TO '/backup/pg_cron_jobs.csv' WITH CSV HEADER;
五、典型應用場景
1. 數據維護任務
-- 自動VACUUM
SELECT cron.schedule('auto-vacuum', '0 4 * * *', $$VACUUM (VERBOSE, ANALYZE) tables_with_heavy_updates;
$$);-- 分區表維護
SELECT cron.schedule('partition-rotation', '0 3 * * *', $$CALL rotate_partitions('events', 'day', 7);
$$);
2. 數據ETL流程
-- 每小時數據抽取
SELECT cron.schedule('extract-hourly', '0 * * * *', $$INSERT INTO data_warehouse.hourly_factsSELECT * FROM extract_hourly_metrics();
$$);-- 每天數據轉換
SELECT cron.schedule('transform-daily', '30 3 * * *', $$CALL transform_raw_to_dimensions();
$$);
3. 業務定時任務
-- 每月賬單生成
SELECT cron.schedule('monthly-billing', '0 2 1 * *', $$CALL generate_invoices(date_trunc('month', CURRENT_DATE));
$$);-- 定時提醒
SELECT cron.schedule('appointment-reminders', '0 9 * * *', $$INSERT INTO notificationsSELECT user_id, 'appointment_reminder', appointment_timeFROM appointmentsWHERE appointment_time BETWEEN now() AND now() + interval '24 hours';
$$);
六、注意事項
-
性能影響：
- 避免調度過于頻繁的CPU密集型任務
- 長時間運行的任務應設置超時
-
安全考慮：
-- 限制任務權限 REVOKE ALL ON SCHEMA cron FROM PUBLIC; GRANT USAGE ON SCHEMA cron TO scheduler_role; -
高可用性：
- 在主備架構中，pg_cron只需在主節點運行
- 使用 repmgr 或 Patroni 時確保故障轉移后任務繼續執行
-
版本兼容性：
- pg_cron 1.0+ 需要 PostgreSQL 12+
- 新版本支持任務標簽和更細粒度的控制
pg_cron 通過將 cron 功能直接集成到 PostgreSQL 中，提供了輕量級但強大的任務調度解決方案，特別適合需要與數據庫緊密交互的定時任務場景。
