詳解PostgreSQL 實現定時任務的 4 種方法
數據庫定時任務可以用於實現定期的備份、統計信息采集、數據匯總、數據清理與優化等。PostgreSQL 沒有提供類似 Oracle、MySQL 以及 Microsoft SQL Sever 的內置任務調度功能,因此本文給大傢介紹一下 PostgreSQL 數據庫中實現定時任務的 4 種方法。
操作系統定時任務
Linux 定時任務(crontab)或者 Windows 任務計劃程序(Task Scheduler)為我們提供瞭一個實現定時任務傳統的方法。以 crontab 為例,我們可以使用以下命令編輯任務列表:
crontab -e
然後在打開的文件中使用以下格式增加一行數據:
#分鐘 小時 月份中的某一天 月份 星期 命令 #(0-59) (0-23) (1-31) (1-12) (0-7 [7 or 0 == Sunday]) <minute> <hour> <day of month> <month> <day of week> <command>
其中的前五個字段表示執行命令的時間,可以使用星號(*)匹配所有的時間。例如,將 <month> 設置為星號表示每個月都執行命令。
舉例來說,輸入以下內容表示每天零點執行數據庫邏輯備份操作。
0 0 * * * pg_dump --no-password -U user db_name > backup.sql
為瞭安全起見不要直接輸入密碼,而是應該將密碼加入 .pgpass 文件,並且將該文件的權限設置為僅當前用戶可見:
chmod 600 .pgpass
pgAgent
pgAgent 是一個用於 PostgreSQL 數據庫的任務調度代理,能夠基於復雜的調度計劃運行多步驟的批處理、shell 腳本以及 SQL 命令。對於 Unix/Linux 系統,pgAgent 以後臺進程的方式運行;對於 Windows 系統,pgAgent 以服務的形式運行。
安裝 pgAgent
PgAdmin 4 管理工具集成瞭 pgAgent 的功能,但是這兩者需要單獨安裝。我們可以通過官方網站下載 PgAdmin 4 以及 pgAgent。具體的安裝步驟和註意事項可以參考官網文檔。安裝完成之後,我們可以在 PgAdmin 4 左側導航樹中看到“pgAgent Jobs”節點。
創建定時任務
右鍵點擊“pgAgent Jobs”節點,選擇“Create” > “pgAgent Job”創建一個新的定時任務。
其中,“General”頁面可以輸入一些基本信息,包括任務的名稱。“Steps”頁面可以設置多個操作步驟,包括執行的腳本或者 SQL 語句等。“Schedules”頁面用於定義任務執行的時間計劃。“SQL”頁面可以顯示創建或者修改任務的語句。
點擊“Save”按鈕保存設置並創建任務,然後我們就可以在“pgAgent Job”節點下看到創建的任務。
pg_cron
pg_cron 是由 citusdata 公司開發的一個 PostgreSQL 定時任務插件(類似於 Oracle 中的 DBMS_SCHEDULER)。pg_cron 作為一個後臺工作進程運行,使用類似 cron 的編輯語法,允許直接在數據庫中執行定時任務。例如:
-- 每周六 3:30am (GMT) 刪除歷史記錄 SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$); schedule ---------- 42 -- 每天 10:00am (GMT) 執行清理作業 SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM'); schedule ---------- 43 -- 將清理作業修改為 3:00am (GMT) SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM'); schedule ---------- 43 -- 停止計劃中的任務 SELECT cron.unschedule('nightly-vacuum' ); unschedule ------------ t (1 row) SELECT cron.unschedule(42); unschedule ------------ t
安裝 pg_cron
pg_cron 目前隻支持 Linux 操作系統。對於 Red Hat、CentOS 以及 Fedora 等操作系統可以使用以下命令進行安裝(PostgreSQL 12):
sudo yum install -y pg_cron_12
對於 Debian 以及 Ubuntu 可以使用以下命令進行安裝(PostgreSQL 12):
sudo apt-get -y install postgresql-12-cron
另外,我們也可以使用源碼進行編譯安裝:
git clone https://github.com/citusdata/pg_cron.git cd pg_cron # Ensure pg_config is in your path, e.g. export PATH=/usr/pgsql-12/bin:$PATH make && sudo PATH=$PATH make install
配置 pg_cron
為瞭在啟動 PostgreSQL 時運行 pg_cron 後臺工作進程,我們需要將 pg_cron 添加到 postgresql.conf 文件中的 shared_preload_libraries配置項。 默認情況下,pg_cron 後臺進程使用 postgres 數據庫獲取所需的元數據。但是我們也可以使用 cron.database_name 配置參數進行設置。
shared_preload_libraries = 'pg_cron' cron.database_name = 'postgres'
重啟 PostgreSQL:
sudo service postgresql-12 restart
然後我們可以使用以下命令創建 pg_cron 函數以及元數據相關的表:
-- 使用 superuser 運行以下命令 CREATE EXTENSION pg_cron; -- 可選操作,為其他用戶授予訪問權限 GRANT USAGE ON SCHEMA cron TO username;
pg_timetable
pg_timetable 是由 CYBERTEC 公司開發的 PostgreSQL 作業調度程序,提供瞭靈活的配置方式和許多高級功能。包括由多個任務組成的任務鏈、支持 SQL 命令和可執行程序、內置任務(例如發送郵件)、完全基於數據庫的配置和日志功能、cron 風格的計劃調度、並發執行的保護等。
安裝 pg_timetable
首先,我們可以使用官方發佈的二進制安裝程序安裝 pg_timetable,目前支持 Windows、Linux 以及 macOS 操作系統。
另外,官方的 docker 鏡像可以點此下載。
master 分支的 latest 標簽是最新版本,使用命令行的運行方式如下:
docker run --rm \ cybertecpostgresql/pg_timetable:latest \ -h 10.0.0.3 -p 54321 -c worker001
指定環境變量的方式如下:
docker run --rm \ -e PGTT_PGHOST=10.0.0.3 \ -e PGTT_PGPORT=54321 \ cybertecpostgresql/pg_timetable:latest \ -c worker001
除此之外,我們也可以使用源碼進行編譯安裝。首先下載並安裝 Go 語言環境,然後使用 go get 命令拷貝 pg_timetable 源碼:
$ env GIT_TERMINAL_PROMPT=1 go get github.com/cybertec-postgresql/pg_timetable/ Username for 'https://github.com': <Github Username> Password for 'https://[email protected]': <Github Password>
運行 pg_timetable:
$ cd ~/go/src/github.com/cybertec-postgresql/pg_timetable/ $ go run main.go --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpwd
或者,也可以編譯成二進制程序並運行:
$ go build $ ./pg_timetable --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpwd
如果想要運行項目中的所有測試,可以執行以下命令:
$ cd ~/go/src/github.com/cybertec-postgresql/pg_timetable/ $ go get github.com/stretchr/testify/ $ go test ./...
也可以使用 postgres docker 鏡像運行測試:
$ RUN_DOCKER=true go test ./...
使用 pg_timetable
pg_timetable 獨立於 PostgreSQL 服務器運行,相當於一個客戶端進程。安裝完成之後,執行以下命令運行 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 目錄中提供瞭大量的示例可以參考。以下命令可以創建一個 8 月份 00:05 運行“MyJob”的定時任務:
SELECT timetable.job_add('MyJob', 'SELECT public.my_func()' , NULL, 'SQL', '5 0 * 8 *', live := TRUE);
以下命令表示從 0 點到 20 點,每兩個小時的 23 分時運行“MyJob”任務:
SELECT timetable.job_add('MyJob', 'SELECT public.my_func()' , NULL, 'SQL', '23 0-20/2 * * *', live := TRUE);
pg_timetable 計劃任務的完整配置由 3 個階段組成:
- 第一個階段用於配置 base_task,定義需要執行的操作。包括 SQL 語句、外部程序以及內置的操作。
- 第二個階段用於配置 task_chain,定義一組順序執行的基本任務。
- 第三階段用於配置 chain_execution_config,定義任務鏈的執行計劃。
此外,為瞭給基本任務傳遞控制參數,任務鏈中的任務都可以附帶一個執行參數。詳細的配置方法和案例可以參考官方網站。
總結
本文介紹瞭在 PostgreSQL 數據庫中實現定時任務的 4 種方法,包括操作系統定時任務、pgAgent 代理、pg_cron 插件以及 pg_timetable 工具。
到此這篇關於PostgreSQL 實現定時任務的 4 種方法的文章就介紹到這瞭,更多相關PostgreSQL 定時任務內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Linux安裝Docker詳細教程
- .NET 6 從0到1使用Docker部署至Linux環境超詳細教程
- Docker安裝阿裡雲服務器和在虛擬機安裝遇到的坑(問題小結)
- Linux系統Docker 部署 ASP.NET Core應用的流程分析
- 內網環境下registry搭建步驟詳解