Advanced scheduling for PostgreSQL

pg_timetable: Advanced scheduling for PostgreSQL

pg_timetableis an advanced job scheduler for PostgreSQL, offering many advantages over traditional schedulers such as cron and others. It is completely database driven and provides a couple of advanced concepts.

Table of Contents

• • 2.1. Container installation
  • 2.2. Local installation
• 3. Features and advanced functionality
• 4. Database logging and transactions
• 5. Runtime information

1. Main features

• Tasks can be arranged in chains
• A chain can consist of SQL and executables
• Parameters can be passed to chains
• Missed tasks (possibly due to downtime) can be retried automatically
• Support for configurable repetitions
• Builtin tasks such as sending emails, etc.
• Fully database driven configuration
• Full support for database driven logging
• Cron-style scheduling
• Optional concurrency protection

2. Installation

There are currently two options on how you can install and run pg_timetable.

If you feel the need for a .deb or .rpm package, please let us know by submitting an issue, or - which we would really appreciate! - creating a pull request that does said things.

2.1 Container installation

When using Docker, simply replace all podman occurrences with docker .

1. Get the Dockerfile:

wget -O pg_timetable.Dockerfile https://raw.githubusercontent.com/cybertec-postgresql/pg_timetable/master/Dockerfile

2. Build the Docker image:

podman build -f pg_timetable.Dockerfile -t pg_timetable:latest

3. Run the image:

podman run --rm pg_timetable:latest

4. To pass additional arguments to pg_timetable, such as where your database is located, simply attach the flags to the podman run , like so:

podman run --rm pg_timetable:latest -h 10.0.0.3 -p 54321

2.2 Local Installation

1. Downlod and install Go on your system.
2. Clone pg_timetable using go get :

$ env GIT_TERMINAL_PROMPT=1 go get github.com/cybertec-postgresql/pg_timetable/
Username for 'https://github.com': <Github Username>
Password for 'https://cyberboy@github.com': <Github Password>

3. Run pg_timetable :

$ cd ~/go/src/github.com/cybertec-postgresql/pg_timetable/
$ go run main.go --dbname=dbname --name=worker001 --user=scheduler --password=strongpwd

Alternatively, build a binary and run it:

$ go build
$ ./pg_timetable --dbname=dbname --name=worker001 --user=scheduler --password=strongpwd

4. (Optional) Run tests in all sub-folders of the project:

$ cd ~/go/src/github.com/cybertec-postgresql/pg_timetable/
$ go get github.com/stretchr/testify/
$ go test ./...

Alternatively, run tests using postgres docker image:

$ RUN_DOCKER=true go test ./...

3. Features and advanced functionality

The scheduling in pg_timetable encompasses three different stages to facilitate the reuse with other parameters or additional schedules.

The first stage, base_task , defines what to do.

The second stage, task_chain , contains a list of base tasks to run sequentially.

The third stage consists of the chain_execution_config and defines if , when , and how often a chain should be executed.

Additionally, to provide the base tasks with parameters and influence their behavior, each entry in a task chain can be accompanied by an execution parameter .

3.1. Base task

In pg_timetable , the most basic building block is a base task . Currently, there are three different kinds of task:

A new base task can be created by inserting a new entry into timetable.base_task .

Excerpt of timetable.base_task

3.2. Task chain

The next building block is a chain , which simply represents a list of tasks. An example would be:

• Download files from a server
• Import files
• Run aggregations
• Commit the transaction
• Remove the files from disk

All tasks of the chain in pg_timetable are executed within one transaction. However, please, pay attention there is no opportunity to rollback SHELL and BUILTIN tasks.

Excerpt of timetable.task_chain

3.2.1. Chain execution configuration

Once a chain has been created, it has to be scheduled. For this, pg_timetable builds upon the standard cron -string, all the while adding multiple configuration options.

Excerpt of timetable.chain_execution_config

​

3.2.2. Chain execution parameters

As mentioned above, base tasks are simple skeletons (e.g. send email , vacuum , etc.). In most cases, they have to be brought to live by passing parameters to the execution.

Excerpt of timetable.chain_execution_paramaters

3.3. Example usages

A variety of examples can be found in the /samples directory.

3.4 Examle functions

Create a Job with the timetable.job_add function. With this function you can add a new Job with a specific time ( by_minute , by_hour , by_day , by_month , by_day_of_week ) as comma separated text list to run or with a in a cron-syntax.

If the parameter by_cron is used all other by_* ( by_minute , by_hour , by_day , by_month , by_day_of_week ) will be ignored.

3.4.1 Usage

3.4.1.1 With Cron-Style

Run "MyJob" at 00:05 in August. SELECT timetable.job_add('MyJob','Select public.my_func()',null,'SQL','5 0 * 8 *');

Run "MyJob" at minute 23 past every 2nd hour from 0 through 20. SELECT timetable.job_add('MyJob','Select public.my_func()',null,'SQL','23 0-20/2 * * *');

3.4.1.2 With specific time

Run "SQL" at 01:00 on first day of Month

SELECT timetable.job_add ('At minute 0 and 1st hour on first day of Month',
    'SELECT timetable.insert_dummy_log()',
    null,
    'SQL',
    null,
    '0',
    '1',
    '1',
    null,
    null,
    '1',
    TRUE,
    FALSE);

Run "SQL" at 01:00 and 02:00 on every Monday´s

SELECT timetable.job_add ('at 01:00 and 02:00 on every Monday´s',
   'SELECT timetable.insert_dummy_log()',
   null,
   'SQL',
   null,
   '0',
   null,
   '1,2',
   null,
   '1',
   '1',
   TRUE,
   FALSE);

4. Database logging and transactions

The entire activity of pg_timetable is logged in database tables ( timetable.log and timetable.execution_log ). Since there is no need to parse files when accessing log data, the representation through an UI can be easily achieved.

Furthermore, this behavior allows a remote host to access the log in a straightforward manner, simplifying large and/or distributed applications.

Note: Logs are written in a separate transaction, in case the chain fails.

5. Runtime information

In order to examine the activity of pg_timetable , the table timetable.run_status can be queried. It contains information about active jobs and their current parameters.

6. Schema diagram

7. Contributing

If you want to contribute to pg_timetable and help make it better, feel free to open an issue or even consider submitting a pull request.

8. Support

For professional support, please contact Cybertec .

9. Authors

Pavlo Golub and Hans-Jürgen Schönig .