{"id":1668,"date":"2024-01-13T14:00:46","date_gmt":"2024-01-13T11:00:46","guid":{"rendered":"http:\/\/pgdataeraold.local\/?p=1668"},"modified":"2024-01-13T14:00:46","modified_gmt":"2024-01-13T11:00:46","slug":"pg_cron-2","status":"publish","type":"post","link":"https:\/\/pgdataera.com\/en\/pg_cron-2\/","title":{"rendered":"pg_cron"},"content":{"rendered":"\n
postgres=# select version();\nversion\n---------------------------------------------------------\nPostgreSQL 14.5 (Debian 14.5-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc\nsudo apt install postgresql-14-cron\nalter system set shared_preload_libraries = 'pg_cron' ;\nsudo systemctl restart postgresql@14-main.service\nps -ef |grep postgres |grep pg_cron |grep -v grep\npostgres 1152 1145 0 16:06 ? 00:00:00 postgres: 14\/main: pg_cron launcher\npostgres=# CREATE EXTENSION pg_cron;\npostgres=# \\dn\nList of schemas\nName   |  Owner\n--------+----------\ncron   | postgres\npublic | postgres<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Using pg_cron has the same logic as cron on Unix\/Linux systems.
15 03 * * *  \u2192  Means 03:15 every night.<\/p>\n\n\n\n
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<\/code> tables of the cron schema<\/p>\n\n\n\n
Settings<\/h2>\n\n\n\n
Step 1<\/strong>
vim \/etc\/postgresql\/14\/main\/postgresql.conf<\/code><\/p>\n\n\n\n
cron.database_name = postgres\ncron.enable_superuser_jobs = on\ncron.host = localhost\ncron.log_min_messages = debug2 # warning, info, debug gibi\nde\u011ferler alabilir\ncron.log_run = on\ncron.log_statement = on\ncron.max_running_jobs = 5\ncron.use_background_workers = on<\/pre>\n\n\n\n
<\/p>\n\n\n\n
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<\/code> value must be higher than the cron.max_running_jobs<\/code> value.<\/p>\n\n\n\n
Step 2<\/strong>
vim \/etc\/postgresql\/14\/main\/pg_hba.conf<\/code> the following line is added,
after restarting, the settings are checked.<\/p>\n\n\n\n
host postgres postgres localhost trust\nsudo systemctl restart postgresql@14-main.service\nSELECT name, setting, short_desc FROM pg_settings WHERE name\nLIKE \u2018cron.%\u2019 ORDER BY name;\nname | setting | short_desc\n---------------------------------------------------------------------------\ncron.database_name | postgres | Database in which pg_cron metadata is kept.\ncron.enable_superuser_jobs | on | Allow jobs to be scheduled as superuser\ncron.host | localhost | Hostname to connect to postgres.\ncron.log_min_messages | debug2 | log_min_messages for the launcher bgworker.\ncron.log_run | on | Log all jobs runs into the job_run_details\ncron.log_statement | on | Log all cron statements prior to execu\ncron.max_running_jobs | 5 | Maximum number of jobs that can run conc\ncron.use_background_workers | on | Use background workers instead of client\n(8 rows)<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Whatever database will be used for cron, the same database name should be entered
in step 1 and 2.<\/p>\n\n\n\n
Example<\/strong><\/p>\n\n\n\n
SELECT cron.schedule(\u2018av off\u2019,\u201943 9 * * *\u2019,\u2019alter system set auotvacuum=off ;\u2019);\nSELECT cron.schedule(\u2018reload\u2019, \u201843 9 * * *\u2019, \u2018select pg_reload_conf();\u2019);\nSELECT * FROM cron.job ;\njid| schedule | command |nodename |port|database|username|\n-----+----------+---------------------------------+---------+-----+--------+-\n20 |43 9 * * *|alter system set autovacuum=off ;|localhost|5432 |postgres|postgres\n21 |43 9 * * *|select pg_reload_conf(); |localhost|5432 |postgres|postgres\nSELECT * FROM cron.job_run_details ;\njid|runid|job_pid|db|user|command|status|return_message|start_time| end_time\n---+-------+---------+----------+----------+-------+-----------+--------------\n21|10 |8617|postgres|postgres|select|succeeded|SELECT 1 |12:43:00 |12:43:00\n20|11 |8619|postgres|postgres|alter|succeeded |ALTER SYS|12:43:00 |12:43:00<\/pre>\n\n\n\n
<\/p>\n\n\n\n
It is necessary to enter in the format \u2018job name\u2019, \u2018time\u2019, \u2018SQL to run\u2019.
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 \u2018utc\u2019;<\/strong><\/code> — Can be controlled.
<\/p>\n\n\n\n
It would be better to delete scheduled tasks with the unschedule function. Can be
deleted with Job ID.
SELECT cron.unschedule(21);<\/strong><\/code><\/p>\n\n\n\n
Monitoring<\/h2>\n\n\n\n
postgres=# \\dt cron.*\nList of relations\nSchema | Name | Type | Owner\n--------+-----------------+-------+----------\ncron | job | table | postgres\ncron | job_run_details | table | postgres\ntail -f \/var\/lib\/postgresql\/14\/main\/log\/postgresql-%d.log\n12:43:00 +03 [919]:[23-1]user=,db=,app=,client=LOG:cron job 20\nstarting:alter system set autovacuum=off ;\n12:43:00 +03 [919]:[24-1]user=,db=,app=,client= LOG: cron job 21\nCOMMAND completed: SELECT 1 1\n12:43:00 +03 [919]:[25-1]user=,db=,app=,client= LOG: cron job 20\nCOMMAND completed: ALTER SYSTEM\nsudo systemctl status postgresql@14-main.service\n\u251c\u2500 919 postgres: 14\/main: pg_cron launcher\nsudo ps -ef |grep pg_cron |grep -v grep\npostgres 0 Sep13 00:00:08 postgres: 14\/main: pg_cron launcher<\/pre>\n\n\n\n
<\/p>\n\n\n\n
It can be tracked from the job and job_run_details <\/code>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<\/code> for details.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Using pg_cron has the same logic as cron on Unix\/Linux systems.15 03 * * * \u2192 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 theextension is created.A schema named cron comes with the extension. Details about …  pg_cron<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[307],"tags":[],"class_list":["post-1668","post","type-post","status-publish","format-standard","hentry","category-scheduled-tasks"],"_links":{"self":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts\/1668","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/comments?post=1668"}],"version-history":[{"count":0,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts\/1668\/revisions"}],"wp:attachment":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/media?parent=1668"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/categories?post=1668"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/tags?post=1668"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}