本文简单介绍了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](https://github.com/citusdata/pg_cron)