Skip to content
v4_dev
Switch branches/tags
Go to file
Code

Files

Permalink
Failed to load latest commit information.

We are actively developing pg_timetable v4. Please refer to the v3 branch for previous version documentation and sources.


License: MIT Coverage Status Go Report Card Release Mentioned in Awesome Go Docker Pulls Dependabot Status

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.

# ./pg_timetable
Udage
  pg_timetable

Application Options:
  -c, --clientname=                   Unique name for application instance [$PGTT_CLIENTNAME]
      --config=                       YAML configuration file
      --no-program-tasks              Disable executing of PROGRAM tasks [$PGTT_NOPROGRAMTASKS]

Connection:
  -h, --host=                         PostgreSQL host (default: localhost) [$PGTT_PGHOST]
  -p, --port=                         PostgreSQL port (default: 5432) [$PGTT_PGPORT]
  -d, --dbname=                       PostgreSQL database name (default: timetable) [$PGTT_PGDATABASE]
  -u, --user=                         PostgreSQL user (default: scheduler) [$PGTT_PGUSER]
      --password=                     PostgreSQL user password [$PGTT_PGPASSWORD]
      --sslmode=[disable|require]     What SSL priority use for connection (default: disable)
      --pgurl=                        PostgreSQL connection URL [$PGTT_URL]

Logging:
      --loglevel=[debug|info|error]   Verbosity level for stdout and log file (default: info)
      --logdblevel=[debug|info|error] Verbosity level for database storing (default: info)
      --logfile=                      File name to store logs
      --logfileformat=[json|text]     Format of file logs (default: json)

Start:
  -f, --file=                         SQL script file to execute during startup
      --init                          Initialize database schema to the latest version and exit. Can
                                      be used with --upgrade
      --upgrade                       Upgrade database to the latest version
      --debug                         Run in debug mode. Only asynchronous chains will be executed

Resource:
      --cronworkers=                  Number of parallel workers for scheduled chains (default: 16)
      --intervalworkers=              Number of parallel workers for interval chains (default: 16)      

Table of Contents

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

pg_timetable is compatible with the latest supported PostgreSQL versions: 11, 12 and 13.

If you want to use pg_timetable with older versions (9.5, 9.6 and 10)...

please, execute this SQL script before running pg_timetable:

CREATE OR REPLACE FUNCTION starts_with(text, text)
RETURNS bool AS 
$$
SELECT 
	CASE WHEN length($2) > length($1) THEN 
		FALSE 
	ELSE 
		left($1, length($2)) = $2 
	END
$$
LANGUAGE SQL
IMMUTABLE STRICT PARALLEL SAFE
COST 5;

2.1 Official release packages

You may find binary package for your platform on the official Releases page. Right now Windows, Linux and macOS packages are available.

2.2 Docker

The official docker image can be found here: https://hub.docker.com/r/cybertecpostgresql/pg_timetable

The latest tag is up to date with the master branch thanks to this github action.

CLI:

docker run --rm \
  cybertecpostgresql/pg_timetable:latest \
  -h 10.0.0.3 -p 54321 -c worker001

Environment variables:

docker run --rm \
  -e PGTT_PGHOST=10.0.0.3 \
  -e PGTT_PGPORT=54321 \
  cybertecpostgresql/pg_timetable:latest \
  -c worker001

2.3 Build from sources

  1. Download 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 --clientname=worker001 --user=scheduler --password=strongpwd

Alternatively, build a binary and run it:

$ go build
$ ./pg_timetable --dbname=dbname --clientname=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, command, defines what to do.
The second stage, task, contains a list of base tasks to run sequentially.
The third stage consists of the chain_id 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:

Base task kind Task kind type Example
SQL snippet SQL Starting a cleanup, refreshing a materialized view or processing data.
External program PROGRAM Anything that can be called as an external binary, including shells, e.g. bash, pwsh, etc.
Internal Task BUILTIN A prebuilt functionality included in pg_timetable. These include:
  • Sleep
  • Log
  • SendMail
  • Download

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

Excerpt of timetable.command

Column Type Definition
name text The name of the base task.
kind timetable.command_kind The type of the base task. Can be SQL(default), PROGRAM or BUILTIN.
script text Contains either a SQL script or a command string which will be executed.

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 PROGRAM and BUILTIN tasks.

Excerpt of timetable.task

Column Type Definition
parent_id bigint The ID of the previous chain task. Set this to NULL if it is the first base task in the chain.
command_id bigint The ID of the base task.
run_as text The role as which the chain should be executed as.
database_connection integer The ID of the timetable.database_connection that should be used.
ignore_error boolean Specify if the chain should resume after encountering an error (default: false).

If the chain has been configured with ignore_error set to true (the default value is false), the worker process will report a success on execution even if the task within the chain fails.

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

Column Type Definition
task_id bigint The id of the task chain.
chain_name text The name of the chain.
run_at timetable.cron To achieve the cron equivalent of *, set the value to NULL.
max_instances integer The amount of instances that this chain may have running at the same time.
live boolean Control if the chain may be executed once it reaches its schedule.
self_destruct boolean Self destruct the chain.
exclusive_execution boolean Specifies whether the chain should be executed exclusively while all other chains are paused.
client_name text Specifies which client should execute the chain. Set this to NULL to allow any client.

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

Column Type Definition
chain_id bigint The ID of the chain execution configuration.
task_id bigint The ID of the chain.
order_id integer The order of the parameter.
value jsonb A string JSON array containing the parameters.

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.add_job function. With this function you can add a new one-step chain with a cron-syntax.

Parameter Type Definition Default
job_name text The name of the Task
job_schedule timetable.cron Time schedule in сron syntax. NULL stands for '* * * * *'
job_command text The function which will be executed.
job_client_name text Specifies which client should execute the chain. Set this to NULL to allow any client. NULL
job_type text Type of the function SQL,PROGRAM and BUILTIN SQL
job_max_instances integer The amount of instances that this chain may have running at the same time. NULL
job_live boolean Control if the chain may be executed once it reaches its schedule. TRUE
job_self_destruct boolean Self destruct the chain. FALSE
job_ignore_errors boolean Ignore error during execution. TRUE

3.5 Usage

Run "MyJob" at 00:05 in August. SELECT timetable.add_job('execute-func', '5 0 * 8 *', 'SELECT public.my_func()');

Run VACUUM at minute 23 past every 2nd hour from 0 through 20. SELECT timetable.add_job('run-vacuum', '23 0-20/2 * * *', 'VACUUM');

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

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.