原 GreenPlum 6.27.0版本新特性及pg_cron模块配置定时任务说明
简介
GreenPlum 6.27.0于2024-04-05已发布,GreenPlum的发布历史请参考:https://www.dbaup.com/greenplumbanbenfabulishi.html
GreenPlum 6.27.0 有以下几个新特性:
Greenplum Database 6.27.0 includes these new and changed features:
- The gpfdist parallel file distribution utility now supports multi-threaded data compression and transmission.
- The PgBouncer distributed with Greenplum Database has been updated to version 1.21.0.
- The
gpcheckcat
utility now includes a new test --mix_distribution_policy
-- which checks for tables created with legacy and non-legacy hash operations. - This release updates the plcontainer version to 2.4.0 and the python3 image version to 2.4.0. Previous image releases cannot be used with the 2.4.0+ plcontainer.
- The
pg_config
utility option--version
now displays the version of the PostgreSQL backend server, and the new option--gp_version
prints the version of Greenplum. - VMware Greenplum 6.27.0 introduces the pg_cron module, which provides a cron-based job scheduler that runs inside the database.
- VMware Greenplum 6.27.0 supports Run-length encoding (RLE) compression with the Zstandard algorith, or
zstd
for column-oriented tables. - The
log_checkpoints
server configuration parameter is now set toon
by default. - The
gpsupport gp_log_collector
tool now supports gathering logs for VMware Greenplum Disaster Recovery, via the new-with-gpdr-primary
and-with-gpdr-recovery
options. - The
ip4r
module distributed with VMware Greenplum has been updated to version 2.4.2. - With the 6.27.0 release, VMware Greenplum now supports the VMware Greenplum Virtual Appliance. The virtual machine appliance contains everything you may need for an easy deploying of VMware Greenplum on vSphere. See VMware Greenplum on vSphere for more details.
- VMware Greenplum 6.27.0 enhances WAL archive recovery by preventing scanning of nonexistent WAL segment files.
- You may now pass the
-i
and--differential
in the samegprecoverseg
command.
其中,最令我关注的还是pg_cron模块,这个模块可以配置定时任务,接下来着重介绍该特性。
6.27.0测试环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | docker rm -f gpdb6270 docker run -itd --name gpdb6270 -h gpdb6270 \ -p 5627:5432 -p 26270:28080 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/greenplum:6.27.0 \ /usr/sbin/init docker exec -it gpdb6270 bash su - gpadmin gpstart -a gpcc start gpcc status gpstate |
pg_cron模块使用说明
参考:
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-modules-pg_cron.html
https://github.com/citusdata/pg_cron
在启用pg_cron模块后,后台会有一个进程:
1 2 3 | [gpadmin@gpdb6270 ~]$ ps -ef|grep cron | grep post gpadmin 16678 16660 0 14:02 ? 00:00:00 postgres: 5432, bgworker: pg_cron launcher [gpadmin@gpdb6270 ~]$ |
pg_cron的时间配置:
1 2 3 4 5 6 7 8 9 | ┌───────────── min (0 - 59) │ ┌────────────── hour (0 - 23) │ │ ┌─────────────── day of month (1 - 31) or last day of the month ($) │ │ │ ┌──────────────── month (1 - 12) │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to │ │ │ │ │ Saturday, or use names; 7 is also Sunday) │ │ │ │ │ │ │ │ │ │ * * * * * |
安装pg_cron
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- 1、默认在postgres数据库中,可以通过如下命令修改成其它数据库(可选) gpconfig -c cron.database_name -v 'db_name' --skipvalidation -- 2、修改shared_preload_libraries参数,注意该参数之前的值 gpconfig -s shared_preload_libraries gpconfig -c shared_preload_libraries -v 'metrics_collector,pg_cron' gpconfig -c cron.timezone -v PRC gpconfig -c cron.max_running_jobs -v 100 gpstop -M fast -ar gpconfig -s shared_preload_libraries gpconfig -s cron.timezone gpconfig -s cron.max_running_jobs -- 3、只能在第1步中cron.database_name配置的数据库中创建扩展,否则会报错 CREATE EXTENSION pg_cron; GRANT USAGE ON SCHEMA cron TO user1; [root@mdw soft]# find /usr/local/greenplum-db/ -name *pg_cron.so* /usr/local/greenplum-db/lib/postgresql/pg_cron.so |
报错:
1 2 3 | 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 = 'lhrgpdb' in postgresql.conf to use the current database. |
升级pg_cron
1 | ALTER EXTENSION pg_cron UPDATE TO '1.6.2'; |