Shulou(Shulou.com)06/01 Report--

This article briefly introduces the plug-in for PostgreSQL: pg_cron. The plug-in can implement functions similar to crontab in PG.

Installation

Compilation and installation

[pg12@localhost pg_cron] $git clone https://github.com/citusdata/pg_cron.git[pg12@localhost pg_cron] $cd pg_ cron [pg12 @ localhost pg_cron] $makegcc-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.Pogcc-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 .d eps/job_metadata.Pogcc-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.Pogcc-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 .dep s/task_states.Pogcc-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.Pogcc-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] $

Experience

Create an extension

[local:/data/run/pg12]: 5120 pg12@testdb=# create extension pg_cron ERROR: can only create extension in database postgresDETAIL: 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 postgresYou 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=#

Modify system parameters

[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=#

Create a job to execute the VACUUM FULL every 1 minute

[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)

Log output

2020-02-11 1722 CST, "pg12", "postgres", 23764, "[local]", 5e427932.5cd4 CST,3/8,0,LOG,00000 1, "SELECT", 2020-02-11 17:51:46 CST,3/8,0,LOG,00000, "failed to parse entry 1", "SELECT cron.schedule ('0UB 1 *', 'VACUUM FULL') "psql" 2020-02-11 1715 52DA 59.936 CST, "pg12", "postgres", 23764, "[local]", 5e427932.5cd4 CST,3/8,0,ERROR,22023 2, "SELECT", 2020-02-11 17:51:46 CST,3/8,0,ERROR,22023, "invalid schedule: 0Unip 1 *", "SELECT cron.schedule ('0Uniple 1 *', 'VACUUM FULL') "psql" 2020-02-11 17 cron job 54 CST,1/0,0,LOG,00000 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 CST,1/0,0,LOG,00000 54 starting 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 cron job 55 CST,19164,5e426547.4adc,4,2020 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 cron job 55 starting 00.579 CST,19164,5e426547.4adc,5,2020-02-11 16:26:47 CST,1/0,0,LOG,00000, "cron job 1 completed: VACUUM" , "

Cancel scheduling

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

references

Pg_cron

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.