Every now and then someone complains (me included) that PostgreSQL doesn't have job scheduler.
This is true, to some extent. I'll try to show you couple of approaches to solving this particular problem.
First option, available to anyone (as long as you're running on some kind of Unix/Linux) is system Cron. Virtually every Unix/Linux system has cron daemon, and you can see it easily using:
=$ ps u -C cron USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND root 1212 0.0 0.0 11944 3100 ? Ss Jan12 0:00 /usr/sbin/cron -f
quick note: in all the examples I'm using Linux, and it's standard tools, but it shouldn't be complicated to convert the commands to any other Unix-like system.
With cron, we can put any kind of jobs, with pretty flexible scheduling syntax.
Let's assume, as it normally happens, that PostgreSQL runs using postgres system account, and postgres user in database is superuser.
I can, for example, put this in a file (named pg.crontab):
* * * * * psql -qAtX -d postgres -c "create table if not exists cronned (when_ts timestamptz, backend_count int4); insert into cronned select now(), count(*) from pg_stat_activity;"
and then I can install this crontab for user postgres:
=$ sudo crontab -u postgres pg.crontab ; sudo crontab -u postgres -l * * * * * psql -qAtX -d postgres -c "create table if not exists cronned (when_ts timestamptz, backend_count int4); insert into cronned select now(), count(*) from pg_stat_activity;"
From this moment on, every minute, psql will start, will try to make a table cronned, and then will insert there a row.
After short wait, in /var/log/syslog, I saw:
2021-01-13T02:47:01.129781+01:00 korsarz CRON[697881]: (postgres) CMD (psql -qAtX -d postgres -c "create table if not exists cronned (when_ts timestamptz, backend_count int4); insert into cronned select now(), count(*) from pg_stat_activity;")
Unfortunately almost immediately I'm getting mails, to postgres account, from cron, with body:
NOTICE: relation "cronned" already exists, skipping
Many people, myself included, tend to slap >/dev/null 2>&1 at the end of cron jobs, so that it will avoid sending mails.
But there is better option. Before create table we can add: set client_min_messages = error;. This will hide all notices or warnings, but will still show us (via email) errors.
This is not really pleasant, though. There are certain limitations to what you can put in cronjob, and besides – putting it all in single line doesn't really seem readable.
What we can do, is convert this to script.
First, let's make place for such cron scripts, and then let's make the script:
sudo -u postgres mkdir -pv ~postgres/crons sudo -u postgres vim /var/lib/postgresql/crons/keep.track.of.connection.count.sh
In the script I wrote:
#!/usr/bin/env bash psql -qAtX -U postgres -d postgres << _END_OF_SQL_ set client_min_messages = error; create table if not exists cronned (when_ts timestamptz, backend_count int4); insert into cronned select now(), count(*) from pg_stat_activity; _END_OF_SQL_
Now I need to make it executable by postgres:
sudo chmod 700 /var/lib/postgresql/crons/keep.track.of.connection.count.sh sudo chown postgres: /var/lib/postgresql/crons/keep.track.of.connection.count.sh
now, I'll change crontab to:
* * * * * ~/crons/keep.track.of.connection.count.sh
and it works.
One thing that many people skip is that crontab can be configured.
When crond runs your cronjob, it uses /bin/sh, and PATH environment variable is set to very conservative /usr/bin:/bin.
I tend to add 3 bits of configuration:
SHELL="/bin/bash" MAILTO="depesz@depesz.com" PATH="/usr/local/bin:/usr/bin:/bin"
This changes shell that is used to run cronjobs, modifies default PATH, and makes it so that mails from cron will get send to me, and not to postgres account on the server.
With config, out crontab could look like this:
# Configuration SHELL="/bin/bash" MAILTO="depesz@depesz.com" PATH="/usr/local/bin:/usr/bin:/bin" # Cronjobs # m h dom mon dow command * * * * * ~/crons/keep.track.of.connection.count.sh
Please note that there are also magical specification for when cronjob has to be run. My favorite is @reboot, which causes the cronjob to be called only when system is booting. This can be used, for example, for starting tools that don't have startup scripts, like:
# Run on system boot @reboot system_monitoring.pl -d /usr/local/etc/system_monitoring.cfg >/dev/null 2>&1
With this in place, sky is the limit – you can write any script/program, and add it to crontab, and it will generally speaking, run.
The problem is that it requires access to shell, and accessing log can be tricky.
Luckily, there are other options. First, chronologically, is pgAgent – which is developed as part of pgAdmin, but you can install it also without pgAdmin.
The problem with it is that docs suggest that the only sane way to manage jobs is via pgAdmin. If this is not a problem for you – feel free to go that way. It's been developed for many years, so I would assume that it's rock solid.
There is also relatively new project: pg_timetable – git logs seem to suggest that it's been under development since December 2020.
Installation, on Ubuntu, was simple:
wget https://github.com/cybertec-postgresql/pg_timetable/releases/download/v3.1.0/pg_timetable_3.1.0_Linux_x86_64.deb sudo apt-get install ./pg_timetable_3.1.0_Linux_x86_64.deb
After installation, the only thing that appears is /usr/local/bin/pg_timetable binary.
Docs are, as far as I can tell, only in form of README.md in github. But let's try…
First test run of daemon:
=$ sudo -u postgres pg_timetable --dbname=postgres --clientname=worker001 --user=postgres [ 2021-01-13 03:24:26.977 | LOG ]: Connection established... [ 2021-01-13 03:24:26.977 | LOG ]: Proceeding as 'worker001' with client PID 730294 [ 2021-01-13 03:24:26.979 | LOG ]: Accepting asynchronous chains execution requests... [ 2021-01-13 03:24:26.992 | LOG ]: Checking for @reboot task chains... [ 2021-01-13 03:24:26.993 | LOG ]: Number of chains to be executed: 0 [ 2021-01-13 03:24:26.995 | LOG ]: Checking for task chains... [ 2021-01-13 03:24:26.996 | LOG ]: Checking for interval task chains... [ 2021-01-13 03:24:26.998 | LOG ]: Number of active interval chains: 0 [ 2021-01-13 03:24:27.000 | LOG ]: Number of chains to be executed: 0
And it keeps occupying terminal, but I'll look into it later.
With daemon running, let's see what we can do.
Based on docs, I know that I can insert directly to tables in timetable schema, or use functions. Let's use functions. Just like previously, I'll add some stuff to some simple table, but given that I don't know, yet, how to chain jobs, let's make the table once, and then add job:
$ CREATE TABLE tted (when_ts timestamptz, backend_count int4); $ SELECT timetable.job_add( task_name := 'FirstJob', task_function := 'insert into tted select now(), count(*) from pg_stat_activity', client_name := NULL, task_type := 'SQL', run_at := '* * * * *', max_instances := 1, live := TRUE, self_destruct := FALSE ); job_add --------- 1 (1 ROW)
After a while I saw rows in tted table. Which is great.
To test something less trivial, I tried to add job to vacuum:
$ SELECT timetable.job_add( task_name := 'TestVac', task_function := 'vacuum verbose analyze tted', client_name := NULL, task_type := 'SQL', run_at := '* * * * *', max_instances := 1, live := TRUE, self_destruct := FALSE ); job_add --------- 2 (1 ROW)
and then I noticed problem, in the shell that runs daemon:
[ 2021-01-13 03:54:57.223 | LOG ]: Starting chain ID: 1; configuration ID: 1
[ 2021-01-13 03:54:57.223 | LOG ]: Starting chain ID: 2; configuration ID: 2
[ 2021-01-13 03:54:57.233 | ERROR ]: Exec {"args":[],"err":{"Severity":"ERROR","Code":"25001","Message":"VACUUM cannot run inside a transaction block","Detail":"","Hint":"","Position":0,"InternalPosition":0,"InternalQuery":"","Where":"","SchemaName":"","TableName":"","ColumnName":"","DataTypeName":"","ConstraintName":"","File":"xact.c","Line":3357,"Routine":"PreventInTransactionBlock"},"pid":752475,"sql":"vacuum verbose analyze tted"}
[ 2021-01-13 03:54:57.238 | ERROR ]: Task execution failed: {"ChainConfig":2,"ChainID":2,"TaskID":7,"TaskName":"TestVac","Script":"vacuum verbose analyze tted","Kind":"SQL","RunUID":{"String":"","Valid":false},"IgnoreError":true,"Autonomous":false,"DatabaseConnection":{"String":"","Valid":false},"ConnectString":{"String":"","Valid":false},"StartedAt":"2021-01-13T03:54:57.233725845+01:00","Duration":305}; Error: ERROR: VACUUM cannot run inside a transaction block (SQLSTATE 25001)
[ 2021-01-13 03:54:57.241 | LOG ]: Executed successfully chain ID: 1; configuration ID: 1
[ 2021-01-13 03:54:57.243 | LOG ]: Executed successfully chain ID: 2; configuration ID: 2There are two logs that are accessible via SQL:
$ SELECT * FROM timetable.execution_log ; chain_execution_config │ chain_id │ task_id │ name │ script │ kind │ last_run │ finished │ returncode │ pid │ output │ client_name ────────────────────────┼──────────┼─────────┼──────────┼───────────────────────────────────────────────────────────────┼──────┼───────────────────────────────┼───────────────────────────────┼────────────┼────────┼──────────────────────────────────────────────────────────────────────┼───────────── 1 │ 1 │ 6 │ FirstJob │ INSERT INTO tted SELECT now(), COUNT(*) FROM pg_stat_activity │ SQL │ 2021-01-13 03:51:57.192419+01 │ 2021-01-13 03:51:57.193205+01 │ 0 │ 731504 │ [NULL] │ worker001 1 │ 1 │ 6 │ FirstJob │ INSERT INTO tted SELECT now(), COUNT(*) FROM pg_stat_activity │ SQL │ 2021-01-13 03:52:57.203146+01 │ 2021-01-13 03:52:57.203705+01 │ 0 │ 731504 │ [NULL] │ worker001 1 │ 1 │ 6 │ FirstJob │ INSERT INTO tted SELECT now(), COUNT(*) FROM pg_stat_activity │ SQL │ 2021-01-13 03:53:57.217778+01 │ 2021-01-13 03:53:57.218315+01 │ 0 │ 731504 │ [NULL] │ worker001 2 │ 2 │ 7 │ TestVac │ vacuum verbose analyze tted │ SQL │ 2021-01-13 03:54:57.234092+01 │ 2021-01-13 03:54:57.234397+01 │ -1 │ 731504 │ ERROR: VACUUM cannot run inside a TRANSACTION block (SQLSTATE 25001) │ worker001 1 │ 1 │ 6 │ FirstJob │ INSERT INTO tted SELECT now(), COUNT(*) FROM pg_stat_activity │ SQL │ 2021-01-13 03:54:57.23804+01 │ 2021-01-13 03:54:57.238589+01 │ 0 │ 731504 │ [NULL] │ worker001 1 │ 1 │ 6 │ FirstJob │ INSERT INTO tted SELECT now(), COUNT(*) FROM pg_stat_activity │ SQL │ 2021-01-13 03:55:57.240584+01 │ 2021-01-13 03:55:57.241208+01 │ 0 │ 731504 │ [NULL] │ worker001 2 │ 2 │ 7 │ TestVac │ vacuum verbose analyze tted │ SQL │ 2021-01-13 03:55:57.241757+01 │ 2021-01-13 03:55:57.241937+01 │ -1 │ 731504 │ ERROR: VACUUM cannot run inside a TRANSACTION block (SQLSTATE 25001) │ worker001 2 │ 2 │ 7 │ TestVac │ vacuum verbose analyze tted │ SQL │ 2021-01-13 03:56:57.247261+01 │ 2021-01-13 03:56:57.247444+01 │ -1 │ 731504 │ ERROR: VACUUM cannot run inside a TRANSACTION block (SQLSTATE 25001) │ worker001 1 │ 1 │ 6 │ FirstJob │ INSERT INTO tted SELECT now(), COUNT(*) FROM pg_stat_activity │ SQL │ 2021-01-13 03:56:57.248469+01 │ 2021-01-13 03:56:57.249086+01 │ 0 │ 731504 │ [NULL] │ worker001 2 │ 2 │ 7 │ TestVac │ vacuum verbose analyze tted │ SQL │ 2021-01-13 03:57:57.262226+01 │ 2021-01-13 03:57:57.262428+01 │ -1 │ 731504 │ ERROR: VACUUM cannot run inside a TRANSACTION block (SQLSTATE 25001) │ worker001 1 │ 1 │ 6 │ FirstJob │ INSERT INTO tted SELECT now(), COUNT(*) FROM pg_stat_activity │ SQL │ 2021-01-13 03:57:57.263296+01 │ 2021-01-13 03:57:57.263842+01 │ 0 │ 731504 │ [NULL] │ worker001 (11 ROWS)
This shows information about executions of jobs. Specifically, we see that TestVac have returncode of -1, and output shows ERROR message. Nice.
The other log is basically, from what I can tell, copy of what pg_timetable daemon prints to stdout:
$ SELECT * FROM timetable.log ORDER BY id DESC LIMIT 10; id | ts | client_name | pid | log_level | message -----+-------------------------------+-------------+--------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 193 | 2021-01-13 03:59:57.302076+01 | worker001 | 731504 | LOG | Executed successfully chain ID: 2; configuration ID: 2 192 | 2021-01-13 03:59:57.300995+01 | worker001 | 731504 | LOG | Executed successfully chain ID: 1; configuration ID: 1 191 | 2021-01-13 03:59:57.297522+01 | worker001 | 731504 | ERROR | Task execution failed: {"ChainConfig":2,"ChainID":2,"TaskID":7,"TaskName":"TestVac","Script":"vacuum verbose analyze tted","Kind":"SQL","RunUID":{"String":"","Valid":FALSE},"IgnoreError":TRUE,"Autonomous":FALSE,"DatabaseConnection":{"String":"","Valid":FALSE},"ConnectString":{"String":"","Valid":FALSE},"StartedAt":"2021-01-13T03:59:57.295413855+01:00","Duration":188}; Error: ERROR: VACUUM cannot run inside a TRANSACTION block (SQLSTATE 25001) 190 | 2021-01-13 03:59:57.284518+01 | worker001 | 731504 | LOG | Starting chain ID: 1; configuration ID: 1 189 | 2021-01-13 03:59:57.284341+01 | worker001 | 731504 | LOG | Starting chain ID: 2; configuration ID: 2 188 | 2021-01-13 03:59:57.278114+01 | worker001 | 731504 | LOG | NUMBER OF chains TO be executed: 2 187 | 2021-01-13 03:59:57.277565+01 | worker001 | 731504 | LOG | NUMBER OF active INTERVAL chains: 0 186 | 2021-01-13 03:59:57.275884+01 | worker001 | 731504 | LOG | Checking FOR INTERVAL task chains... 185 | 2021-01-13 03:59:57.263144+01 | worker001 | 731504 | LOG | Checking FOR task chains... 184 | 2021-01-13 03:58:57.284896+01 | worker001 | 731504 | LOG | Executed successfully chain ID: 2; configuration ID: 2 (10 ROWS)
There is way more functionality in here – you can make pg_timetable call external programs, make tasks run only when other tasks finish successfully, send mails from within db, and more.
So, as you can see there are ways to get automated jobs in PostgreSQL. I haven't fully tested pgAgent solely because I don't really use GUI db clients.
As for wishlist – I'd like to get better docs for pgAgent and pg_timetable, with explained meaning of every function and table.
And possibly – more functions – currently I wasn't able to find a function to delete a task from system. I could delete it's data in:
- timetable.base_task
- timetable.task_chain
- timetable.chain_execution_config
but it doesn't strike me as very user-friendly. But – in time, I guess, it will only get better.
Hello, Pavlo Golub, the author of pg_timetable is here.
Thanks a lot for your review. There are a lot of improvements needed indeed. One can help us by creating issues with bugs or feature requests (https://github.com/cybertec-postgresql/pg_timetable/issues) or ask us any questions starting a discussion (https://github.com/cybertec-postgresql/pg_timetable/discussions).
Your feedback is even more valuable than pull requests or patches. It’s extremely important to know what we missed, e.g. VACUUM job can be done with `autonomous` task that runs a separate transaction. But I found we didn’t mention it in the documentation properly.
@Pavlo:
Thanks for responding. I’ll think about adding comments on github (recently removed my account there for … reasons). Generally my only issue, so far, is documentation/examples shortcomings.
How do I list all jobs? How do I remove job? I can, and probably will, figure it out for myself, but it would be really cool to have these ready in docs. Preferably as views/functions that show only relevant information.
@depesz:
Thanks for the details. Fair enough. I know docs are less informative than they should have been. I will focus on this aspect more! 🙂
What about extension pg_cron? We have found it is tremendously helpful and is smart enough to know not to run jobs on the hot-standby. So when failover occurs, pg_cron just runs correctly. Additionally, pg_cron recognizes when there is another of the same job running.
@Joshua:
missed that one. Will test and blog. Not sure when, but I added it to my queue 🙂
@Joshua:
Posted about pg_cron: https://www.depesz.com/2021/01/28/how-to-run-some-tasks-without-user-intervention-at-specific-times-part-2/