pg_cron

最新推荐文章于 2024-08-13 08:22:27 发布

数据库人生

最新推荐文章于 2024-08-13 08:22:27 发布

阅读量2k

点赞数

分类专栏： # postgresql extension 文章标签： pg_cron

本文链接：https://blog.csdn.net/ctypyb2002/article/details/83987902

版权

postgresql extension 专栏收录该内容

65 篇文章 3 订阅

订阅专栏

pg_cron

postgresql 本身没有类似 oracle schedule、job的功能，如果没有集中调度中心的话，运行job基本依赖操作系统的crontab。pg_cron 是 citusdata 公司实现的 extension，看名字基本就类似操作系统的crontab。

另外，在slave节点，pg_cron是不会执行的。

os: ubuntu 16.04
postgresql: 9.6.8

版本信息

root@pgsql1:~# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 16.04.3 LTS
Release:	16.04
Codename:	xenial

root@pgsql1:~# su - postgres
postgres@pgsql1:~$ psql
psql (9.6.10)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select version();
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Ubuntu 9.6.10-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

下载、安装

# vi /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# apt-get update

# apt list |grep -i cron
# apt install postgresql-9.6-cron
# dpkg -L postgresql-9.6-cron
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-9.6-cron
/usr/share/doc/postgresql-9.6-cron/changelog.Debian.gz
/usr/share/doc/postgresql-9.6-cron/README.md.gz
/usr/share/doc/postgresql-9.6-cron/copyright
/usr/share/postgresql
/usr/share/postgresql/9.6
/usr/share/postgresql/9.6/extension
/usr/share/postgresql/9.6/extension/pg_cron--1.0.sql
/usr/share/postgresql/9.6/extension/pg_cron--1.1.sql
/usr/share/postgresql/9.6/extension/pg_cron--1.0--1.1.sql
/usr/share/postgresql/9.6/extension/pg_cron--0.1--1.0.sql
/usr/share/postgresql/9.6/extension/pg_cron.control
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/9.6
/usr/lib/postgresql/9.6/lib
/usr/lib/postgresql/9.6/lib/pg_cron.so

创建

# vi /etc/postgresql/9.6/main/postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_cron'
cron.database_name = 'postgres'

# /etc/init.d/postgresql restart
[ ok ] Restarting postgresql (via systemctl): postgresql.service.
# su - postgres
postgres@pgsql1:~$ psql
psql (9.6.8)
Type "help" for help.

postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(1 row)

postgres=# CREATE EXTENSION pg_cron;
CREATE EXTENSION
postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 cron   | postgres |                      | 
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(2 rows)

postgres=#

创建了 cron 的schema，在实际工作中，通常是创建在某个业务数据库下，然后授权给其他用户：GRANT USAGE ON SCHEMA cron TO marco;

postgres=# \d cron.*
                             Table "cron.job"
  Column  |  Type   |                      Modifiers                       
----------+---------+------------------------------------------------------
 jobid    | bigint  | not null default nextval('cron.jobid_seq'::regclass)
 schedule | text    | not null
 command  | text    | not null
 nodename | text    | not null default 'localhost'::text
 nodeport | integer | not null default inet_server_port()
 database | text    | not null default current_database()
 username | text    | not null default "current_user"()
 active   | boolean | not null default true
Indexes:
    "job_pkey" PRIMARY KEY, btree (jobid)
Policies:
    POLICY "cron_job_policy" FOR ALL
      USING ((username = ("current_user"())::text))
Triggers:
    cron_job_cache_invalidate AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE ON cron.job FOR EACH STATEMENT EXECUTE PROCEDURE cron.job_cache_invalidate()

    Index "cron.job_pkey"
 Column |  Type  | Definition 
--------+--------+------------
 jobid  | bigint | jobid
primary key, btree, for table "cron.job"

           Sequence "cron.jobid_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | jobid_seq
 last_value    | bigint  | 1
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

只有一个表 cron.job。

使用

postgres=# set search_path='cron';
SET
postgres=# create table public.tmp_t0(c0 timestamp,c1 varchar(100));
postgres=# INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username)
VALUES ('*/1 * * * *', 'insert into public.tmp_t0(c0,c1) values(now(),'||E'\''||'done'||E'\''||');', 'localhost', 5432, 'postgres', 'postgres');

postgres=# select * from job;
 jobid |  schedule   |                        command                         | nodename  | nodeport | database | username | active 
-------+-------------+--------------------------------------------------------+-----------+----------+----------+----------+--------
     1 | */1 * * * * | insert into public.tmp_t0(c0,c1) values(now(),'done'); | localhost |     5432 | postgres | postgres | t
(1 row)

查看日志时可以发现。

2018-11-12 10:26:00.000 CST,,,11168,,5be8dd6c.2ba0,44,,2018-11-12 09:54:52 CST,1/0,0,LOG,00000,"cron job 1 starting: insert into public.tmp_t0(c0,c1) values(now(),'done');",,,,,,,,,""
2018-11-12 10:26:00.001 CST,,,11410,"localhost:52268",5be8e4b8.2c92,1,"",2018-11-12 10:26:00 CST,,0,LOG,00000,"connection received: host=localhost port=52268",,,,,,,,,""
2018-11-12 10:26:00.005 CST,"postgres","postgres",11410,"localhost:52268",5be8e4b8.2c92,2,"authentication",2018-11-12 10:26:00 CST,4/105,0,LOG,00000,"connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)",,,,,,,,,""
2018-11-12 10:26:00.008 CST,,,11168,,5be8dd6c.2ba0,45,,2018-11-12 09:54:52 CST,1/0,0,LOG,00000,"cron job 1 completed: INSERT 0 1 1",,,,,,,,,""
2018-11-12 10:26:00.009 CST,"postgres","postgres",11410,"localhost:52268",5be8e4b8.2c92,3,"idle",2018-11-12 10:26:00 CST,,0,LOG,00000,"disconnection: session time: 0:00:00.007 user=postgres database=postgres host=localhost port=52268",,,,,,,,,"pg_cron"

查看目标表

postgres=# select * from public.tmp_t0 ;
             c0             |  c1  
----------------------------+------
 2018-11-12 10:23:00.007271 | done
 2018-11-12 10:24:00.008084 | done
 2018-11-12 10:25:00.007255 | done
 2018-11-12 10:26:00.006316 | done
 2018-11-12 10:27:00.007748 | done
(5 rows)

参考：
https://github.com/citusdata/pg_cron