pg_timetable alternatives and similar packages
Based on the "Database" category
Do you think we are missing an alternative of pg_timetable or a related project?
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 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]
Table of Contents
- 1. Main features
- 2. Installation
- 3. Features and advanced functionality
- 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
pg_timetable is compatible with the latest supported PostgreSQL versions: 11 and 12.
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
macOS packages are available.
2.2 Container installation
When using Docker, simply replace all
- Get the Dockerfile:
wget -O pg_timetable.Dockerfile https://raw.githubusercontent.com/cybertec-postgresql/pg_timetable/master/Dockerfile
- Build the Docker image:
podman build -f pg_timetable.Dockerfile -t pg_timetable:latest
- Run the image:
podman run --rm pg_timetable:latest
- 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.3 Build from sources
- Download and install Go on your system.
- Clone pg_timetable using
sh $ env GIT_TERMINAL_PROMPT=1 go get github.com/cybertec-postgresql/pg_timetable/ Username for 'https://github.com': <Github Username> Password for 'https://email@example.com': <Github Password>
$ 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
(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:
|Base task kind||Task kind type||Example|
||Starting a cleanup, refreshing a materialized view or processing data.|
||Anything that can be called from the command line.|
||A prebuilt functionality included in pg_timetable. These include: SleepLogSendMailDownload|
A new base task can be created by inserting a new entry into
Excerpt of timetable.base_task
||The name of the base task.|
||The type of the base task. Can be
||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
Excerpt of timetable.task_chain
||The ID of the previous chain task. Set this to
||The ID of the base task.|
||The role as which the chain should be executed as.|
||The ID of the
||Specify if the chain should resume after encountering an error (default:
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_execution_config
||The id of the task chain.|
||The name of the chain.|
||To achieve the
||The amount of instances that this chain may have running at the same time.|
||Control if the chain may be executed once it reaches its schedule.|
||Self destruct the chain.|
||Specifies whether the chain should be executed exclusively while all other chains are paused.|
||Specifies which client should execute the chain. Set this to
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_parameters
||The ID of the chain execution configuration.|
||The ID of the chain.|
||The order of the parameter.|
3.3 Example usages
A variety of examples can be found in the
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.
||The name of the Task|
||The function which will be executed.|
||Specifies which client should execute the chain. Set this to
||Type of the function
||Time schedule in сron syntax.
||The amount of instances that this chain may have running at the same time.||NULL|
||Control if the chain may be executed once it reaches its schedule.||FALSE|
||Self destruct the chain.||FALSE|
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.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](timetable_schema.png?raw=true "Schema diagram")
For professional support, please contact Cybertec.
*Note that all licence references and agreements mentioned in the pg_timetable README section above are relevant to that project's source code only.