详解PostgreSQL 实现定时使命的 4 种方法

• 操作系统定时使命
  
• pgAgent
  
• 安装 pgAgent
  
• 创建定时使命
  
• pg_cron
  
  
  
  • 安装 pg_cron
    
  • 配置 pg_cron
    
  
  
• pg_timetable
  
  
  
  • 安装 pg_timetable
    
  • 使用 pg_timetable
    
  
  
• 总结
  

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>
复制代码

0 0 * * * pg_dump --no-password -U user db_name > backup.sql
复制代码

chmod 600 .pgpass
复制代码

-- 每周六 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
复制代码

sudo yum install -y pg_cron_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
复制代码

shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
复制代码

sudo service postgresql-12 restart
复制代码

-- 使用 superuser 运行以下命令
CREATE EXTENSION pg_cron;

-- 可选操作，为其他用户授予访问权限
GRANT USAGE ON SCHEMA cron TO username;
复制代码

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
复制代码

$ env GIT_TERMINAL_PROMPT=1 go get github.com/cybertec-postgresql/pg_timetable/
Username for 'https://github.com': <Github Username>
Password for 'https://cyberboy@github.com': <Github Password>
复制代码

$ 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 ./...
复制代码

$ RUN_DOCKER=true go test ./...
复制代码

# ./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]
复制代码

SELECT timetable.job_add('MyJob', 'SELECT public.my_func()' , NULL, 'SQL', '5 0 * 8 *', live := TRUE);
复制代码

SELECT timetable.job_add('MyJob', 'SELECT public.my_func()' , NULL, 'SQL', '23 0-20/2 * * *', live := TRUE);
复制代码

  

• 第一个阶段用于配置 base_task，界说必要执行的操作。包罗 SQL 语句、外部程序以及内置的操作。  
  
• 第二个阶段用于配置 task_chain，界说一组序次执行的根本使命。  
  
• 第三阶段用于配置 chain_execution_config，界说使命链的执行筹划。