pg_timetable

pg_timetable: Advanced scheduling for PostgreSQL

pg_timetable is 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

• 1. Main features
• 2. Installation
  • 2.1. Container installation
  • 2.2. Local installation
• 3. Features and advanced functionality
  • 3.1. Base task
  • 3.2. Task chain
    • 3.2.1. Chain execution configuration
    • 3.2.2. Chain execution parameters
  • 3.3. Example usages
• 4. Database logging and transactions
• 5. Runtime information
• 6. Schema diagram
• 7. Contributing
• 8. Support
• 9. Authors

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

1. Build the Docker image:

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

1. Run the image:

podman run --rm pg_timetable:latest

1. 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>

1. 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

1. (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 Example functions

Create a Job with the timetable.job_add function. With this function you can add a new one step chain with a cron-syntax.

3.5 Usage

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 * * *');

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.