pg_timetable flow chart

¡Hola, queridos amigos! We’ve released several valuable features for pg_timetable in May. It’s summer already, and time is flying fast! I hope all of you are well and safe, as well as your families and friends.

Here I want to introduce what’s new and how that is useful briefly. If you are unfamiliar with pg_timetable, the best PostgreSQL scheduler in the world, you may read previous posts about it. 😊

--init pg_timetable command-line argument

--init command-line argument, if specified, will force pg_timetable to create the database schema and exit immediately.

During start-up, the database schema is created automatically if absent. Later a worker proceeds into the main loop checking chains and executing tasks.

This behavior is stable, however, mostly unusable in automation scenarios. Usually, you split preparation and work into separate stages. Now you can automate your scripts not only init pg_timetable environment but upgrade it as well. Consider this:

$ pg_timetable -c worker01 --init --upgrade postgresql://[email protected]/baz

--file pg_timetable command-line argument

That’s not all! After schema initialization, one usually wants to add some tasks and chains. Either you are debugging your environment or deploying into production, there are traditionally prepared tasks you want to put into work. In previous versions, one should execute an additional step for that. But we’ve added --file command-line argument. You can now specify SQL script to be executed, not only during initialization or upgrade, but during an everyday routine.

--no-help pg_timetable command-line argument

If you want more control over output, you may use --no-help command-line argument. The reason might be the same. Pretend you’re using pg_timetable in some script, and the only thing you need to know is an exit code. But pg_timetable often tries to provide users with hints about why termination occurred. To suppress help messages, add this command-line argument.

Examples

Usual start

If you start pg_timetable with only parameters needed and a clean database, it will create the necessary schema and proceed into the main loop. Below you can see a typical output. As you can see no tasks available.

$ pg_timetable -c worker01 postgresql://[email protected]/timetable
[ 2020-06-25 12:16:09.744 | LOG   ]: Connection established...
[ 2020-06-25 12:16:09.800 | LOG   ]: Proceeding as 'worker01' with client PID 15668
[ 2020-06-25 12:16:09.801 | LOG   ]: Executing script: DDL
[ 2020-06-25 12:16:09.898 | LOG   ]: Schema file executed: DDL
[ 2020-06-25 12:16:09.905 | LOG   ]: Executing script: JSON Schema
[ 2020-06-25 12:16:09.909 | LOG   ]: Schema file executed: JSON Schema
[ 2020-06-25 12:16:09.910 | LOG   ]: Executing script: Built-in Tasks
[ 2020-06-25 12:16:09.911 | LOG   ]: Schema file executed: Built-in Tasks
[ 2020-06-25 12:16:09.913 | LOG   ]: Executing script: Job Functions
[ 2020-06-25 12:16:09.917 | LOG   ]: Schema file executed: Job Functions
[ 2020-06-25 12:16:09.917 | LOG   ]: Configuration schema created...
[ 2020-06-25 12:16:09.954 | LOG   ]: Checking for @reboot task chains...
[ 2020-06-25 12:16:09.957 | LOG   ]: Number of chains to be executed: 0
[ 2020-06-25 12:16:09.958 | LOG   ]: Checking for task chains...
[ 2020-06-25 12:16:09.958 | LOG   ]: Checking for interval task chains...
[ 2020-06-25 12:16:09.960 | LOG   ]: Number of chains to be executed: 0
[ 2020-06-25 12:16:09.993 | LOG   ]: Number of active interval chains: 0
[ 2020-06-25 12:17:09.958 | LOG   ]: Checking for task chains...
[ 2020-06-25 12:17:09.958 | LOG   ]: Checking for interval task chains...
[ 2020-06-25 12:17:09.960 | LOG   ]: Number of chains to be executed: 0
[ 2020-06-25 12:17:09.993 | LOG   ]: Number of active interval chains: 0
...

Initialize and exit immediately

Create schema if needed and exit returning 0 code. Can be combined with --upgrade argument, if new version of pg_timetable supposed to be run against old schema.

$ pg_timetable -c worker01 --init postgresql://[email protected]/timetable
[ 2020-06-25 12:33:04.232 | LOG   ]: Connection established...
[ 2020-06-25 12:33:04.285 | LOG   ]: Proceeding as 'worker01' with client PID 9684
[ 2020-06-25 12:33:04.286 | LOG   ]: Executing script: DDL
[ 2020-06-25 12:33:04.385 | LOG   ]: Schema file executed: DDL
[ 2020-06-25 12:33:04.401 | LOG   ]: Executing script: JSON Schema
[ 2020-06-25 12:33:04.405 | LOG   ]: Schema file executed: JSON Schema
[ 2020-06-25 12:33:04.406 | LOG   ]: Executing script: Built-in Tasks
[ 2020-06-25 12:33:04.407 | LOG   ]: Schema file executed: Built-in Tasks
[ 2020-06-25 12:33:04.409 | LOG   ]: Executing script: Job Functions
[ 2020-06-25 12:33:04.410 | LOG   ]: Schema file executed: Job Functions
[ 2020-06-25 12:33:04.413 | LOG   ]: Configuration schema created...
[ 2020-06-25 12:33:04.413 | LOG   ]: Closing session

$ echo $?
0

Execute custom SQL script at the start

You may specify arbitrary SQL script to execute during start-up. But beware you are not duplicating tasks in case of a working system. Here I will add sample chain and pg_timetable will execute it in the main loop when the time has come.

$ pg_timetable -c worker01 --file="samples/basic.sql" postgresql://[email protected]/timetable
[ 2020-06-25 12:41:59.661 | LOG   ]: Connection established...
[ 2020-06-25 12:41:59.712 | LOG   ]: Proceeding as 'worker01' with client PID 7964
[ 2020-06-25 12:41:59.713 | LOG   ]: Executing script: DDL
[ 2020-06-25 12:41:59.801 | LOG   ]: Schema file executed: DDL
[ 2020-06-25 12:41:59.812 | LOG   ]: Executing script: JSON Schema
[ 2020-06-25 12:41:59.814 | LOG   ]: Schema file executed: JSON Schema
[ 2020-06-25 12:41:59.816 | LOG   ]: Executing script: Built-in Tasks
[ 2020-06-25 12:41:59.817 | LOG   ]: Schema file executed: Built-in Tasks
[ 2020-06-25 12:41:59.818 | LOG   ]: Executing script: Job Functions
[ 2020-06-25 12:41:59.819 | LOG   ]: Schema file executed: Job Functions
[ 2020-06-25 12:41:59.822 | LOG   ]: Configuration schema created...
[ 2020-06-25 12:41:59.823 | LOG   ]: Executing script: samples/basic.sql
[ 2020-06-25 12:41:59.824 | LOG   ]: Script file executed: samples/basic.sql
[ 2020-06-25 12:41:59.862 | LOG   ]: Checking for @reboot task chains...
[ 2020-06-25 12:41:59.867 | LOG   ]: Number of chains to be executed: 0
[ 2020-06-25 12:41:59.867 | LOG   ]: Checking for task chains...
[ 2020-06-25 12:41:59.868 | LOG   ]: Checking for interval task chains...
[ 2020-06-25 12:41:59.871 | LOG   ]: Number of chains to be executed: 1
[ 2020-06-25 12:41:59.875 | LOG   ]: Starting chain ID: 1; configuration ID: 1
[ 2020-06-25 12:41:59.905 | LOG   ]: Number of active interval chains: 0
[ 2020-06-25 12:41:59.920 | LOG   ]: Executed successfully chain ID: 1; configuration ID: 1
...

The same example on existing schema adding some interval chains. As you can see, no schema is created this time, but the custom script is executed either way.

$ pg_timetable -c worker01 --file="samples/interval.sql" postgresql://[email protected]/timetable
[ 2020-06-25 12:48:07.300 | LOG   ]: Connection established...
[ 2020-06-25 12:48:07.360 | LOG   ]: Proceeding as 'worker01' with client PID 8840
[ 2020-06-25 12:48:07.363 | LOG   ]: Executing script: samples/interval.sql
[ 2020-06-25 12:48:07.374 | LOG   ]: Script file executed: samples/interval.sql
[ 2020-06-25 12:48:07.411 | LOG   ]: Checking for @reboot task chains...
[ 2020-06-25 12:48:07.414 | LOG   ]: Number of chains to be executed: 0
[ 2020-06-25 12:48:07.415 | LOG   ]: Checking for task chains...
[ 2020-06-25 12:48:07.416 | LOG   ]: Checking for interval task chains...
[ 2020-06-25 12:48:07.419 | LOG   ]: Number of chains to be executed: 1
[ 2020-06-25 12:48:07.423 | LOG   ]: Starting chain ID: 1; configuration ID: 1
[ 2020-06-25 12:48:07.454 | LOG   ]: Number of active interval chains: 2
[ 2020-06-25 12:48:07.456 | LOG   ]: Starting chain ID: 2; configuration ID: 3
[ 2020-06-25 12:48:07.466 | LOG   ]: Executed successfully chain ID: 1; configuration ID: 1
[ 2020-06-25 12:48:07.494 | LOG   ]: Starting chain ID: 2; configuration ID: 2
[ 2020-06-25 12:48:07.500 | USER  ]: Severity: NOTICE; Message: Sleeping for 5 sec in Configuration @after 10 seconds
[ 2020-06-25 12:48:07.512 | USER  ]: Severity: NOTICE; Message: Sleeping for 5 sec in Configuration @every 10 seconds
...

Backup and clean log before upgrade

Suppose we want to upgrade pg_timetable version, but with that we want clean logs for the new version. In this case, one may start it in --upgrade mode, provide special actions to be done with --file="backup_logs.sql" and specify --init to exit immediately after upgrade.

$ cat backup_logs.sql
CREATE TABLE timetable.log_backup AS SELECT * FROM timetable.log;
TRUNCATE timetable.log;

$ pg_timetable -c worker01 --init --upgrade --file="backup_logs.sql" postgresql://[email protected]/timetable
[ 2020-06-25 13:22:16.161 | LOG   ]: Connection established...
[ 2020-06-25 13:22:16.215 | LOG   ]: Proceeding as 'worker01' with client PID 16652
[ 2020-06-25 13:22:16.220 | LOG   ]: Executing script: backup_logs.sql
[ 2020-06-25 13:22:16.250 | LOG   ]: Script file executed: backup_logs.sql
[ 2020-06-25 13:22:16.251 | LOG   ]: Upgrading database...
[ 2020-06-25 13:22:16.255 | USER  ]: Severity: NOTICE; Message: relation "migrations" already exists, skipping
[ 2020-06-25 13:22:16.255 | LOG   ]: Closing session

$ psql -U scheduler -d timetable -c "\dt timetable.log*"
             List of relations
  Schema   |    Name    | Type  |   Owner
-----------+------------+-------+-----------
 timetable | log        | table | scheduler
 timetable | log_backup | table | scheduler
(2 rows)

Suppress help message, but leave error output

$ pg_timetable
the required flag `-c, --clientname' was not specified
Usage:
  pg_timetable

Application Options:
  -c, --clientname=               Unique name for application instance
  -v, --verbose                   Show verbose debug information [$PGTT_VERBOSE]
  -h, --host=                     PG config DB host (default: localhost) [$PGTT_PGHOST]
  -p, --port=                     PG config DB port (default: 5432) [$PGTT_PGPORT]
  -d, --dbname=                   PG config DB dbname (default: timetable) [$PGTT_PGDATABASE]
  -u, --user=                     PG config DB user (default: scheduler) [$PGTT_PGUSER]
  -f, --file=                     SQL script file to execute during startup
      --password=                 PG config DB password (default: somestrong) [$PGTT_PGPASSWORD]
      --sslmode=[disable|require] What SSL priority use for connection (default: disable)
      --pgurl=                    PG config DB url [$PGTT_URL]
      --init                      Initialize database schema and exit. Can be used with --upgrade
      --upgrade                   Upgrade database to the latest version
      --no-shell-tasks            Disable executing of shell tasks [$PGTT_NOSHELLTASKS]
[ 2020-06-25 12:57:59.122 | PANIC ]: Error parsing command line arguments: the required flag `-c, --clientname' was not specified

$ echo $?
2

$ pg_timetable --no-help
the required flag `-c, --clientname' was not specified

$ echo $?
2

Further reading

If you want to know about every improvement and a bug fix in the previous version, please, check our repository Release page.

And do not forget to Star it, Watch it and maybe even Fork it. Why not? We are Open Source after all!

Stay safe! Love! Peace! Elephant! ❤🕊🐘