postgresql 计划任务插件pg_cron

简介：
pg_cron 相当于在database内部的一个crontab程序，类似于Oracle中的job和scheduler.
pg_cron 可以同时运行多个job，但在同一时刻，只有一个job在执行，后续的job会挂起，直到前一个执行完成。
项目地址：https://github.com/citusdata/pg_cron

先来一段实例后面附录官网的详细介绍：

create extension pg_cron;

create table t_cron ( insert_date timestamp);

创建cron，本机每分钟往测试表insert一条数据：

SELECT cron.schedule('* * * * *', 'insert into t_cron values (now())');

查看当前job：

select * from cron.job;

停止job：

select cron.unschedule(jobid);

远程调用（远程服务器上对应的d2库不需要建pg_cron插件，只需要对应的表存在即可）：

INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username)
              VALUES ('* * * * *', 'insert into t_cron values (now())', 'localhost', 5433, 'd2', 'postgres');

 

详细的官网介绍如下：

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:

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)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

Installing pg_cron
Install on Red Hat, CentOS, Fedora, Amazon Linux with PostgreSQL 11:

# Add Citus Data package repository
curl https://install.citusdata.com/community/rpm.sh | sudo bash

# Install the pg_cron extension
sudo yum install -y pg_cron_11
Install on Debian, Ubuntu with PostgreSQL 11 using apt.postgresql.org:

# Install the pg_cron extension
sudo apt-get -y install postgresql-11-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-11/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;

-- on PostgreSQL 9.x, do this instead:
CREATE EXTENSION pg_cron VERSION '1.0';
ALTER EXTENSION pg_cron UPDATE;

-- 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.