文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

PostgreSQL DBA(163) - Extension(pg_cron)

2024-04-02 19:55

关注

本文简单介绍了PostgreSQL的插件:pg_cron。该插件可在PG中实现类似crontab的功能。

安装
编译安装


[pg12@localhost pg_cron]$git clone https://github.com/citusdata/pg_cron.git
[pg12@localhost pg_cron]$cd pg_cron
[pg12@localhost pg_cron]$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/pg_cron.o src/pg_cron.c -MMD -MP -MF .deps/pg_cron.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/job_metadata.o src/job_metadata.c -MMD -MP -MF .deps/job_metadata.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/misc.o src/misc.c -MMD -MP -MF .deps/misc.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/task_states.o src/task_states.c -MMD -MP -MF .deps/task_states.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/entry.o src/entry.c -MMD -MP -MF .deps/entry.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -shared -o pg_cron.so src/pg_cron.o src/job_metadata.o src/misc.o src/task_states.o src/entry.o -L/appdb/pg12/pg12.1/lib    -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12.1/lib',--enable-new-dtags  -L/appdb/pg12/pg12.1/lib -lpq 
cat pg_cron.sql > pg_cron--1.0.sql
[pg12@localhost pg_cron]$ make install
/bin/mkdir -p '/appdb/pg12/pg12.1/lib/postgresql'
/bin/mkdir -p '/appdb/pg12/pg12.1/share/postgresql/extension'
/bin/mkdir -p '/appdb/pg12/pg12.1/share/postgresql/extension'
/bin/install -c -m 755  pg_cron.so '/appdb/pg12/pg12.1/lib/postgresql/pg_cron.so'
/bin/install -c -m 644 .//pg_cron.control '/appdb/pg12/pg12.1/share/postgresql/extension/'
/bin/install -c -m 644 .//pg_cron--1.0--1.1.sql .//pg_cron--1.1--1.2.sql pg_cron--1.0.sql '/appdb/pg12/pg12.1/share/postgresql/extension/'
[pg12@localhost pg_cron]$

体验
创建扩展


[local:/data/run/pg12]:5120 pg12@testdb=# create extension pg_cron;
ERROR:  can only create extension in database postgres
DETAIL:  Jobs must be scheduled from the database configured in cron.database_name, since the pg_cron background worker reads job descriptions from this database.
HINT:  Add cron.database_name = 'testdb' in postgresql.conf to use the current database.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at RAISE
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# \c postgres
You are now connected to database "postgres" as user "pg12".
[local:/data/run/pg12]:5120 pg12@postgres=# create extension pg_cron;
CREATE EXTENSION
[local:/data/run/pg12]:5120 pg12@postgres=#

修改系统参数


[local:/data/run/pg12]:5120 pg12@postgres=# select name,setting from pg_settings where name like '%cron%';
         name          |  setting  
-----------------------+-----------
 cron.database_name    | postgres
 cron.host             | localhost
 cron.log_statement    | on
 cron.max_running_jobs | 32
(4 rows)
[local:/data/run/pg12]:5120 pg12@postgres=#

创建job,目的是每个1分钟执行VACUUM FULL


[local:/data/run/pg12]:5120 pg12@postgres=# SELECT cron.schedule('*/1 * * * *', 'VACUUM FULL');
 schedule 
----------
        1
(1 row)
[local:/data/run/pg12]:5120 pg12@postgres=# 
[local:/data/run/pg12]:5120 pg12@postgres=# \df cron.schedule
                             List of functions
 Schema |   Name   | Result data type |     Argument data types     | Type 
--------+----------+------------------+-----------------------------+------
 cron   | schedule | bigint           | schedule text, command text | func
(1 row)
[local:/data/run/pg12]:5120 pg12@postgres=# 
[local:/data/run/pg12]:5120 pg12@postgres=# \df cron.*
                                   List of functions
 Schema |         Name         | Result data type |     Argument data types     | Type 
--------+----------------------+------------------+-----------------------------+------
 cron   | job_cache_invalidate | trigger          |                             | func
 cron   | schedule             | bigint           | schedule text, command text | func
 cron   | unschedule           | boolean          | job_id bigint               | func
(3 rows)

日志输出


2020-02-11 17:52:59.935 CST,"pg12","postgres",23764,"[local]",5e427932.5cd4,1,"SELECT",2020-02-11 17:51:46 CST,3/8,0,LOG,00000,"failed to parse entry 1",,,,,,"SELECT cron.schedule('0/1 * * * *', 'VACUUM FULL');",,,"psql"
2020-02-11 17:52:59.936 CST,"pg12","postgres",23764,"[local]",5e427932.5cd4,2,"SELECT",2020-02-11 17:51:46 CST,3/8,0,ERROR,22023,"invalid schedule: 0/1 * * * *",,,,,,"SELECT cron.schedule('0/1 * * * *', 'VACUUM FULL');",,,"psql"
2020-02-11 17:54:00.001 CST,,,19164,,5e426547.4adc,2,,2020-02-11 16:26:47 CST,1/0,0,LOG,00000,"cron job 1 starting: VACUUM FULL",,,,,,,,,""
2020-02-11 17:54:00.740 CST,,,19164,,5e426547.4adc,3,,2020-02-11 16:26:47 CST,1/0,0,LOG,00000,"cron job 1 completed: VACUUM ",,,,,,,,,""
2020-02-11 17:55:00.002 CST,,,19164,,5e426547.4adc,4,,2020-02-11 16:26:47 CST,1/0,0,LOG,00000,"cron job 1 starting: VACUUM FULL",,,,,,,,,""
2020-02-11 17:55:00.579 CST,,,19164,,5e426547.4adc,5,,2020-02-11 16:26:47 CST,1/0,0,LOG,00000,"cron job 1 completed: VACUUM ",,,,,,,,,""

取消调度


[local:/data/run/pg12]:5120 pg12@postgres=# select cron.unschedule(1);
 unschedule 
------------
 t
(1 row)
[local:/data/run/pg12]:5120 pg12@postgres=#

参考资料
pg_cron

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 资料下载
  • 历年真题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯