PostgreSQL数据库定时任务扩展， 定时任务（pg_cron）。What is pg_cron ？

PostgreSQL不自带定时任务功能，但是可以安装第三方的扩展或者使用操作系统的cron，第三方扩展使用比较多的有pgAgent和pg_cron
.
.
本文来源：
https://github.com/citusdata/pg_cron

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 
----------
        7
(1 row)
--每分钟调用存储过程test()
test=> SELECT cron.schedule('process-new-events', '* * * * *', 'CALL test()');
 schedule 
----------
        8
(1 row)
--PostgreSQL重启更新pg_cron扩展
test=> SELECT cron.schedule('upgrade-pgcron', '@reboot', 'ALTER EXTENSION pg_cron UPDATE');
 schedule 
----------
        9
(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 
----------
        10
(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
----------
       42

-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
 schedule
----------
       43

-- Change to vacuum at 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
 schedule
----------
       43

-- Stop scheduling jobs
SELECT cron.unschedule('nightly-vacuum' );
 unschedule 
------------
 t
(1 row)

SELECT cron.unschedule(42);
 unschedule
------------
          t

指定数据库执行任务

--指定数据库执行任务
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 
----------
        11
(1 row)

更新定时任务

--每周0点0分(GMT)调用存储过程test()，通过jobname名称执行upsert
 SELECT cron.schedule('process-new-events', '0 0 * * 0', 'CALL test()');
 
 schedule 
----------
        8
(1 row)

删除定时任务

-- 通过jobname名称删除
 SELECT cron.unschedule('process-new-events');
 
 unschedule 
------------
 t
(1 row)
-- 通过jobid删除
-- SELECT cron.unschedule(<定时任务ID>)
 SELECT cron.unschedule(7);
 
 unschedule 
------------
 t
(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	✔️