postgres=# select version(); version --------------------------------------------------------- PostgreSQL 14.5 (Debian 14.5-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc sudo apt install postgresql-14-cron alter system set shared_preload_libraries = 'pg_cron' ; sudo systemctl restart postgresql@14-main.service ps -ef |grep postgres |grep pg_cron |grep -v grep postgres 1152 1145 0 16:06 ? 00:00:00 postgres: 14/main: pg_cron launcher postgres=# CREATE EXTENSION pg_cron; postgres=# \dn List of schemas Name | Owner --------+---------- cron | postgres public | postgres
Using pg_cron has the same logic as cron on Unix/Linux systems.
15 03 * * * → Means 03:15 every night.
First, the running postgresql version is checked and the appropriate version of pg_
cron is installed.
shared_preload_libraries is set in the database settings, restarted and the
extension is created.
A schema named cron comes with the extension. Details about scheduled tasks can be
seen from the cron and job_run_details tables of the cron schema
Settings
Step 1vim /etc/postgresql/14/main/postgresql.conf
cron.database_name = postgres cron.enable_superuser_jobs = on cron.host = localhost cron.log_min_messages = debug2 # warning, info, debug gibi değerler alabilir cron.log_run = on cron.log_statement = on cron.max_running_jobs = 5 cron.use_background_workers = on
It can be set manually by entering values in the above format in the postgresql.
conf file. A restart is required for the settings to be enabled.The max_worker_processes value must be higher than the cron.max_running_jobs value.
Step 2vim /etc/postgresql/14/main/pg_hba.conf the following line is added,
after restarting, the settings are checked.
host postgres postgres localhost trust sudo systemctl restart postgresql@14-main.service SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE ‘cron.%’ ORDER BY name; name | setting | short_desc --------------------------------------------------------------------------- cron.database_name | postgres | Database in which pg_cron metadata is kept. cron.enable_superuser_jobs | on | Allow jobs to be scheduled as superuser cron.host | localhost | Hostname to connect to postgres. cron.log_min_messages | debug2 | log_min_messages for the launcher bgworker. cron.log_run | on | Log all jobs runs into the job_run_details cron.log_statement | on | Log all cron statements prior to execu cron.max_running_jobs | 5 | Maximum number of jobs that can run conc cron.use_background_workers | on | Use background workers instead of client (8 rows)
Whatever database will be used for cron, the same database name should be entered
in step 1 and 2.
Example
SELECT cron.schedule(‘av off’,’43 9 * * *’,’alter system set auotvacuum=off ;’); SELECT cron.schedule(‘reload’, ‘43 9 * * *’, ‘select pg_reload_conf();’); SELECT * FROM cron.job ; jid| schedule | command |nodename |port|database|username| -----+----------+---------------------------------+---------+-----+--------+- 20 |43 9 * * *|alter system set autovacuum=off ;|localhost|5432 |postgres|postgres 21 |43 9 * * *|select pg_reload_conf(); |localhost|5432 |postgres|postgres SELECT * FROM cron.job_run_details ; jid|runid|job_pid|db|user|command|status|return_message|start_time| end_time ---+-------+---------+----------+----------+-------+-----------+-------------- 21|10 |8617|postgres|postgres|select|succeeded|SELECT 1 |12:43:00 |12:43:00 20|11 |8619|postgres|postgres|alter|succeeded |ALTER SYS|12:43:00 |12:43:00
It is necessary to enter in the format ‘job name’, ‘time’, ‘SQL to run’.
In the example above, the first sentence disables the autovacuum feature at 12:43 pm
every day.
The second sentence performs the reload operation at 12:43 for the settings to take
effect.
The entered times are not incorrect, they work in the UTC time zone. In other words, it
is 3 hours behind Turkey time, so it works at 12:43 Turkey time.SELECT now() at time zone ‘utc’; — Can be controlled.
It would be better to delete scheduled tasks with the unschedule function. Can be
deleted with Job ID.SELECT cron.unschedule(21);
Monitoring
postgres=# \dt cron.* List of relations Schema | Name | Type | Owner --------+-----------------+-------+---------- cron | job | table | postgres cron | job_run_details | table | postgres tail -f /var/lib/postgresql/14/main/log/postgresql-%d.log 12:43:00 +03 [919]:[23-1]user=,db=,app=,client=LOG:cron job 20 starting:alter system set autovacuum=off ; 12:43:00 +03 [919]:[24-1]user=,db=,app=,client= LOG: cron job 21 COMMAND completed: SELECT 1 1 12:43:00 +03 [919]:[25-1]user=,db=,app=,client= LOG: cron job 20 COMMAND completed: ALTER SYSTEM sudo systemctl status postgresql@14-main.service ├─ 919 postgres: 14/main: pg_cron launcher sudo ps -ef |grep pg_cron |grep -v grep postgres 0 Sep13 00:00:08 postgres: 14/main: pg_cron launcher
It can be tracked from the job and job_run_details tables in the cron schema and
from the postgresql log file.
Process information can also be seen with systemctl and ps commands.
You can visit github.com/citusdata/pg_cron for details.