PostgreSQL数据库定时任务扩展，定时任务（pg_cron）。What is pg_cron ？_postgresql定时作业在哪查看

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
What is pg_cron?

pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 or higher) that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database:

#连接数据库
psql -d test -U test
--每天6点30分(GMT) 运行vacuum test  
test=> SELECT cron.schedule('30 6 * * *', 'VACUUM test');
 schedule 
----------
(1 row)
--每分钟调用存储过程test()
test=> SELECT cron.schedule('process-new-events', '* * * * *', 'CALL test()');
 schedule 
----------
(1 row)
--PostgreSQL重启更新pg_cron扩展
test=> SELECT cron.schedule('upgrade-pgcron', '@reboot', 'ALTER EXTENSION pg_cron UPDATE');
 schedule 
----------
(1 row)
--每天0点0分(GMT)删除1年前的历史数据
test=> SELECT cron.schedule('delete-old-events','0 0 * * *', $$DELETE FROM test WHERE createtime < now() - interval '1 year'$$);
 schedule 
----------
(1 row)
-- 周六3:30am (GMT) 删除过期数据。 
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
----------
-- 每天的10:00am (GMT) 执行磁盘清理。
SELECT cron.schedule('0 10 * * *', 'VACUUM');
----------
-- 每分钟执行指定脚本。
SELECT cron.schedule('* * * * *', 'select 1;')；
----------
-- 每个小时的23分执行指定脚本。
SELECT cron.schedule('23 * * * *', 'select 1;')；
----------
-- 每个月的4号执行指定脚本。
SELECT cron.schedule('* * 4 * *', 'select 1;')；
-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
 schedule
----------
-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
 schedule
----------
-- Change to vacuum at 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
 schedule
----------
-- Stop scheduling jobs
SELECT cron.unschedule('nightly-vacuum' );
 unschedule 
------------
(1 row)
SELECT cron.unschedule(42);
 unschedule
------------
指定数据库执行任务 
--指定数据库执行任务
SELECT cron.schedule('<定时计划>', '<定时任务>', '<指定数据库>')
pg_cron can run multiple jobs in parallel, but it runs at most one instance of a job at a time. If a second run is supposed to start before the first one finishes, then the second run is queued and started as soon as the first run completes.
 
The schedule uses the standard cron syntax, in which * means “run every time period”, and a specific number means “but only at this time”:
 
 ┌───────────── min (0 - 59)
 │ ┌────────────── hour (0 - 23)
 │ │ ┌─────────────── day of month (1 - 31)
 │ │ │ ┌──────────────── month (1 - 12)
 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
 │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *
 ┌───────────── 分钟： 0 ~ 59
 │ ┌────────────── 小时： 0 ~ 23
 │ │ ┌─────────────── 日期： 1 ~ 31
 │ │ │ ┌──────────────── 月份： 1 ~ 12
 │ │ │ │ ┌───────────────── 一周中的某一天 ：0 ~ 6，0表示周日。
 │ │ │ │ │                  
 │ │ │ │ │
 │ │ │ │ │
 * * * * *
An easy way to create a cron schedule is: crontab.guru. 
The code in pg_cron that handles parsing and scheduling comes directly from the cron source code by Paul Vixie, hence the same options are supported. Be aware that pg_cron always uses GMT! 
Installing pg_cron
 
Install on Red Hat, CentOS, Fedora, Amazon Linux with PostgreSQL 12 using PGDG: 
# Install the pg_cron extension
sudo yum install -y pg_cron_12
Install on Debian, Ubuntu with PostgreSQL 12 using apt.postgresql.org: 
# Install the pg_cron extension
sudo apt-get -y install postgresql-12-cron
You can also install pg_cron by building it from source: 
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
Setting up pg_cron
 
To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to shared_preload_libraries in postgresql.conf. Note that pg_cron does not run any jobs as a long a server is in hot standby mode, but it automatically starts when the server is promoted.




    
 
By default, the pg_cron background worker expects its metadata tables to be created in the “postgres” database. However, you can configure this by setting the cron.database_name configuration parameter in postgresql.conf. 
# add to postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
After restarting PostgreSQL, you can create the pg_cron functions and metadata tables using CREATE EXTENSION pg_cron. 
-- run as superuser:
CREATE EXTENSION pg_cron;
-- optionally, grant usage to regular users:
GRANT USAGE ON SCHEMA cron TO marco;
Important: Internally, pg_cron uses libpq to open a new connection to the local database. It may be necessary to enable trust authentication for connections coming from localhost in pg_hba.conf for the user running the cron job. Alternatively, you can add the password to a .pgpass file, which libpq will use when opening a connection. 
For security, jobs are executed in the database in which the cron.schedule function is called with the same permissions as the current user. In addition, users are only able to see their own jobs in the cron.job table. 
配置pg_cron
 
要在PostgreSQL启动时启动pg_cron后台工作程序，你需要把pg_cron添加到shared_preload_libraries参数中。如果是备库，pg_cron不会运行任何作业，但是备库提升为主库后它将自动启动。默认情况下，pg_cron的元数据会在postgres库中创建。不过可以通过cron.database_name参数进行灵活配置。在内部，pg_cron使用libpq打开到本地数据库的新连接。对于运行cron作业的用户，需要在pg_hba.conf中为来自本地主机的连接启用trust身份验证，或者使用.pgpass文件，但是在1.3版本简化了这个配置，你可以通过cron.use_background_workers=on在postgresql.conf中进行设置来选择使用动态后台工作程序代替连接。这样，你不需要任何pg_hba.conf更改。后台工作程序的一个缺点是并发作业的数量限制为max_worker_processes（默认为8个）。连接到localhost的标准方式仅受限制max_connections（默认情况下为100，通常更高）。如果选择使用动态后台工作程序建议调大max_worker_processes的参数值。 
#vi postgresql.conf:
shared_preload_libraries = ‘pg_cron’
cron.database_name = ‘test’
cron.use_background_workers = on
max_worker_processes = 16
#重启数据库
service postgresql-10 restart
#连接数据库
psql -d test
#创建扩展插件，需超级用户
postgres=# CREATE EXTENSION pg_cron;
#删除插件
postgres=# DROP EXTENSION pg_cron;
postgres=# GRANT USAGE ON SCHEMA cron TO test;
执行某个任务
 
SELECT cron.schedule('<定时计划>', '<定时任务>')
查看定时任务
 
--查看定时任务  
 SELECT * FROM cron.job;
 jobid |  schedule  |                            command                            | nodename  | nodeport | database | username | active |      jobname       
-------+------------+---------------------------------------------------------------+-----------+----------+----------+----------+--------+--------------------
查看定时任务运行情况
 
--查看定时任务  
 SELECT * FROM cron.job_run_details;
 jobid | runid | job_pid | database | username |                     command                      |  status   |  return_message                        |          start_time           |           end_time            
-------+-------+---------+----------+----------+--------------------------------------------------+-----------+----------------------------------------+-------------------------------+-------------------------------
(1 rows)
--清历史数据
--每天0点0分(GMT)删除7天前的数据
 SELECT cron.schedule('clean audit log', '0 0 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() – interval '7 days'$$);
 schedule 
----------
(1 row)
更新定时任务
 
--每周0点0分(GMT)调用存储过程test()，通过jobname名称执行upsert
 SELECT cron.schedule('process-new-events', '0 0 * * 0', 'CALL test()');
 schedule 
----------
(1 row)
删除定时任务
 
-- 通过jobname名称删除
 SELECT cron.unschedule('process-new-events');
 unschedule 
------------
(1 row)
-- 通过jobid删除
-- SELECT cron.unschedule(<定时任务ID>)
 SELECT cron.unschedule(7);
 unschedule 
------------
(1 row)
--查看job
 TABLE cron.job;
 jobid | schedule  |                            command                            | nodename  | nodeport | database | username | active |      jobname      
-------+-----------+---------------------------------------------------------------+-----------+----------+----------+----------+--------+-------------------
     9 | @reboot   | ALTER EXTENSION pg_cron UPDATE                                | localhost |     5432 | test     | test     | t      | upgrade-pgcron
    10 | 0 0 * * * | DELETE FROM test WHERE createtime < now() - interval '1 year' | localhost |     5432 | test     | test     | t      | delete-old-events
(2 rows)
查看任务执行记录
 
SELECT * FROM cron.job_log;
Example use cases
 
Articles showing possible ways of using pg_cron: 
Auto-partitioning using pg_partman
Computing rollups in an analytical dashboard
Deleting old data, vacuum
Feeding cats
Routinely invoking a function 
Managed services
 
The following table keeps track of which of the major managed Postgres services support pg_cron. 
Service Supported
Alibaba Cloud ✔️
Amazon RDS ❌
Azure ✔️
Citus Cloud ✔️
Crunchy Bridge ✔️
DigitalOcean ✔️
Google Cloud ❌
Heroku ❌
Supabase ✔️
                        原文链接：https://github.com/citusdata/pg_cron
                                    https://github.com/citusdata/pg_cron
一直都使用作業系統的 crontab 來執行 PostgreSQL 例行維護作業或資料整理工作。直到前陣子又被問到這個問題，才發現一直都不知道 pg_cron 這個好用的套件。
簡單來說，就是可以在資料庫內自行維護排程的工作，不只是管理者（postgres），也可以授權給一般使用者自行排程。
個人覺得使用上算是很直覺了，專案 README 的說明就很足夠了，上面也有一些例子可以參考。這邊就再提供一個簡單的例子。
作業系統的部份
pg_cron是postgresql的一个扩展，是一个基于cron的作业调度程序，需要pg9.5及以上版本才可以安装。
它的用法和操作系统的cron基本一致，但是允许我们直接在postgresql数据库中执行sql语句。并且作为一个独立运行的工作者进程，其生命周期管理、内存空间都依赖于 postgreSQL 。
例如我们安装完该插件后，可以看到相关的进程：
下载地址：
https://github.com/citusdata/pg_cron
编译安装：
cd pg_cron
                                    文章转载自公众号：SQL编程思想
数据库定时任务可以用于实现定期的备份、统计信息采集、数据汇总、数据清理与优化等。PostgreSQL 没有提供类似 Oracle、MySQL 以及 Microsoft SQL Sever 的内置任务调度功能，因此本文给大家介绍一下 PostgreSQL 数据库中实现定时任务的 4 种方法。
方法一：操作系统定时任务
Linux 定时任务（crontab）或者 Windows 任务计划程序（Task Scheduler）为我们提供了一个实现定时任务传统的方法。以 cronta
                                    在左上角右键点击servers，点击Register，点击server，输入名称、连接地址、数据库名，数据库用户以及密码。就可以正常访问了，还有漂亮的仪表盘。配置公网或者内网穿透还可以手机、pad之类的在公交、地铁之类的愉快写SQL代码（装逼）了。切换postgres用户，本地连接登录没问题便是成功。， 修改listen_addresses的值。我们联网使用源安装，直接执行以下代码即可。然后输入邮箱和密码登录便可以访问。如果需要改的可以自行查看修改方法。上面是我仅安装web模式的。文件最后添加如下内容。
                                    让我们看一下以下的比较表。如果想了解更多相关的信息，可以查看相关以下工具的相关文档。
pg_timetable:  https://github.com/cybertec-postgresql/pg_timetable
pg_cron: https://github.com/citusdata/pg_cron
pgAgent: https://github.com/postgres/pgagent
jpgAgent: https://github.com/GoSimpleLLC/jpgAgent
pgbuc
                                    likeshop基于「ThinkPHP + Vue + Nuxt + uni-app」实现的免费开源商城系统，支持H5、小程序、APP，微信支付、支付宝支付、短信、云存储、优惠券、秒杀、拼团、抢购等主流功能，专业团队维护，值得信赖，欢迎下载体验。likeshop基于「ThinkPHP + Vue + Nuxt + uni-app」实现的免费开源商城系统，支持H5、小程序、APP，微信支付、支付宝支付、短信、云存储、优惠券、秒杀、拼团、抢购等主流功能，专业团队维护，值得信赖，欢迎下载体验。
                                    pg_cron
pg_cron是Citus Data研发的一个PostgreSQL扩展。它包含一个后台工作程序 （pg_cron scheduler），用于在服务器端执行数据库任务。它使用与常规cron相同的语法，允许直接从数据库定期执行PostgreSQL命令。
2.1 环境
本次测试环境为：
CentOS Linux release 7.6.1810 (Core) 
Pos...
                                    定时任务（pg_cron）
pg_cron是基于cron的作业调度插件，语法与常规cron相同，但它可以直接从数据库执行PostgreSQL命令。
每一个定时任务分为两部分：
定时计划
规定使用插件的计划，例如每隔1分钟执行一次该任务。
定时计划使用标准的cron语法，其中*表示任意时间都运行，特定数字表示仅在这个时间时运行。
 ┌───────────── 分钟： 0 ~ 59
 │ ┌────────────── 小时： 0 ~ 23
 │ │ ┌─────────────── 日期： 1 ~ 3
                                    elastic job、xxl-job 二者相比，不看github star的话，elastic job 的优势在于使用体验，管理界面比 xxl-job 漂亮、好用，接入也比  xxl-job 简便；大众点评开源的弹性分布式任务调度框架，功能丰富强大，轻量，提供web管理界面，springboot + freemarker 界面一般，文档丰富，使用db持久化任务状态，无需额外引入组件。使用简单、功能单一，对复杂任务、多任务并发执行支持差，适用于简单的单个任务，eg. 间隔指定时间清除本地缓存。
Service	Supported
Alibaba Cloud	✔️
Amazon RDS	❌
Azure	✔️
Citus Cloud	✔️
Crunchy Bridge	✔️
DigitalOcean	✔️
Google Cloud	❌
Heroku	❌
Supabase	✔️