pg_timetable: Advanced scheduling for PostgreSQL

Overview

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

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-program-tasks            Disable executing of PROGRAM tasks [$PGTT_NOPROGRAMTASKS]

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://[email protected]': <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, 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
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.base_task.

Excerpt of timetable.base_task

Column Type Definition
name text The name of the base task.
kind timetable.task_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_chain

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.
task_id bigint The ID of the base task.
run_uid 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_execution_config

Column Type Definition
chain_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.
excluded_execution_configs integer[] TODO
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.chain_execution_parameters

Column Type Definition
chain_execution_config bigint The ID of the chain execution configuration.
chain_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.job_add function. With this function you can add a new one step chain with a cron-syntax.

Parameter Type Definition Default
task_name text The name of the Task
task_function text The function which will be executed.
client_name text Specifies which client should execute the chain. Set this to NULL to allow any client. NULL
task_type text Type of the function SQL,PROGRAM and BUILTIN SQL
run_at timetable.cron Time schedule in сron syntax. NULL stands for '* * * * *' NULL
max_instances integer The amount of instances that this chain may have running at the same time. NULL
live boolean Control if the chain may be executed once it reaches its schedule. FALSE
self_destruct boolean Self destruct the chain. FALSE

3.5 Usage

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

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 * * *', live := TRUE);

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.

Issues
  • Critical: Pg_timetable hangs after some time

    Critical: Pg_timetable hangs after some time

    Describe the bug The engine do nothing after some time. It repeats several times.

    To Reproduce I install many short jobs (about 100-150)

    Expected behavior It should work :)

    Latest version (docker).

    I turn on debug level. Console out is:

    pg_timetable_1     | ($1, $2, $3, $4, $5)] [args:[77 CHAIN_DONE 658817 77 worker001]] [commandTag:INSERT 0 1] [pid:744] [time:14.656865ms] Exec
    pg_timetable_1     | 2021-11-10 12:15:08.047 [INFO] [chain:77] Chain executed successfully (/build/internal/scheduler/chain.go:219 scheduler.(*Scheduler).executeChain)
    pg_timetable_1     | 2021-11-10 12:15:08.047 [DEBUG] [chain:75] [sql:SELECT task_id, command, kind, run_as, ignore_error, autonomous, database_connection, timeout
    pg_timetable_1     | FROM timetable.task WHERE chain_id = $1 ORDER BY task_order ASC] [args:[75]] [pid:731] [rowCount:1] [time:14.63167ms] Query
    pg_timetable_1     | 2021-11-10 12:15:08.047 [INFO] [chain:75] [task:75] Starting task (/build/internal/scheduler/chain.go:196 scheduler.(*Scheduler).executeChain)
    pg_timetable_1     | 2021-11-10 12:15:08.047 [DEBUG] [chain:77] [sql:commit] [args:[]] [commandTag:COMMIT] [pid:729] [time:906.902µs] Exec
    pg_timetable_1     | 2021-11-10 12:15:08.048 [DEBUG] [sql:INSERT INTO timetable.execution_log (
    pg_timetable_1     | chain_id, task_id, command, kind, last_run, finished, returncode, pid, output, client_name) 
    pg_timetable_1     | VALUES ($1, $2, $3, $4, clock_timestamp() - $5 :: interval, clock_timestamp(), $6, $7, NULLIF($8, ''), $9)] [args:[76 76 DO $$ begin
    pg_timetable_1     |                               CALL  STOP_JOB_PCKG.C (truncated 139 bytes) SQL 0.013225 seconds 0 1 DO worker001]] [commandTag:INSERT 0 1] [pid:723] [time:1.687252ms] Exec
    pg_timetable_1     | 2021-11-10 12:15:08.048 [DEBUG] [sql:INSERT INTO timetable.execution_log (
    pg_timetable_1     | chain_id, task_id, command, kind, last_run, finished, returncode, pid, output, client_name) 
    pg_timetable_1     | VALUES ($1, $2, $3, $4, clock_timestamp() - $5 :: interval, clock_timestamp(), $6, $7, NULLIF($8, ''), $9)] [args:[13 13 DO $SQL$ declare
    pg_timetable_1     |                                                (truncated 371 bytes) SQL 0.013254 seconds 0 1 DO worker001]] [commandTag:INSERT 0 1] [pid:722] [time:2.183699ms] Exec
    pg_timetable_1     | 2021-11-10 12:15:08.048 [DEBUG] [chain:3] [task:3] [sql:INSERT INTO timetable.run_status 
    pg_timetable_1     | (task_id, execution_status, start_status_id, chain_id, client_name)
    pg_timetable_1     | VALUES 
    pg_timetable_1     | ($1, $2, $3, $4, $5)] [args:[3 TASK_STARTED 658824 3 worker001]] [commandTag:INSERT 0 1] [pid:727] [time:2.064047ms] Exec
    pg_timetable_1     | 2021-11-10 12:15:08.049 [DEBUG] [chain:76] [task:76] [sql:INSERT INTO timetable.run_status 
    pg_timetable_1     | (task_id, execution_status, start_status_id, chain_id, client_name)
    pg_timetable_1     | VALUES 
    pg_timetable_1     | ($1, $2, $3, $4, $5)] [args:[76 CHAIN_DONE 658818 76 worker001]] [commandTag:INSERT 0 1] [pid:723] [time:1.154631ms] Exec
    pg_timetable_1     | 2021-11-10 12:15:08.049 [INFO] [chain:76] Chain executed successfully (/build/internal/scheduler/chain.go:219 scheduler.(*Scheduler).executeChain)
    pg_timetable_1     | 2021-11-10 12:16:11.456 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:16:11.456 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:17:11.457 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:17:11.457 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:18:11.458 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:18:11.458 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:19:11.460 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:19:11.460 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:20:11.461 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:20:11.461 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:21:11.462 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:21:11.462 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:22:11.463 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:22:11.463 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:23:11.464 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:23:11.464 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:24:11.465 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:24:11.465 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:25:11.468 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:25:11.468 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:26:11.469 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:26:11.469 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:27:11.470 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:27:11.470 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:28:11.474 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:28:11.474 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:29:11.475 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:29:11.475 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:30:11.476 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:30:11.476 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:31:11.480 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:31:11.480 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:32:11.482 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:32:11.482 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:33:11.484 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:33:11.484 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:34:11.485 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:34:11.485 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:35:11.488 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:35:11.488 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:36:11.489 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:36:11.489 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:37:11.490 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:37:11.490 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:38:11.491 [DEBUG] Checking for task chains... (/build/internal/scheduler/scheduler.go:106 scheduler.(*Scheduler).Run)
    pg_timetable_1     | 2021-11-10 12:38:11.491 [DEBUG] Checking for interval task chains... (/build/internal/scheduler/scheduler.go:108 scheduler.(*Scheduler).Run)
    

    Checking sessions: `select pid, application_name, backend_start, query_start, wait_event, state from pg_stat_activity;

    pid,application_name,backend_start,query_start,wait_event,state
    31,"",,,,
    29,"",,,,
    1228,pgAdmin 4 - DB:mvno_db,2021-11-10 12:47:12.449305 +00:00,2021-11-10 12:49:48.483390 +00:00,ClientRead,idle
    1210,pgAdmin 4 - CONN:2757245,2021-11-10 12:41:48.547423 +00:00,2021-11-10 12:41:49.210300 +00:00,,active
    48,pgAdmin 4 - DB:mvno_db,2021-11-10 07:08:34.455633 +00:00,2021-11-10 12:41:48.486353 +00:00,ClientRead,idle
    1211,pgAdmin 4 - CONN:3785933,2021-11-10 12:41:49.837562 +00:00,2021-11-10 12:41:55.599331 +00:00,ClientRead,idle
    1214,RubyMine 2021.2.3,2021-11-10 12:42:37.055644 +00:00,2021-11-10 12:49:49.085140 +00:00,,active
    183,pgAdmin 4 - DB:mvno_db,2021-11-10 08:14:09.243633 +00:00,2021-11-10 12:26:18.669312 +00:00,ClientRead,idle
    186,pgAdmin 4 - CONN:7101011,2021-11-10 08:15:05.777636 +00:00,2021-11-10 08:15:09.663315 +00:00,ClientRead,idle
    207,pgAdmin 4 - CONN:7821198,2021-11-10 08:25:13.736627 +00:00,2021-11-10 08:25:17.495312 +00:00,ClientRead,idle
    271,pgAdmin 4 - CONN:4427893,2021-11-10 08:56:45.722682 +00:00,2021-11-10 08:56:46.188694 +00:00,ClientRead,idle
    711,"",2021-11-10 12:09:43.046685 +00:00,2021-11-10 12:31:24.809328 +00:00,ClientRead,idle
    717,pg_timetable,2021-11-10 12:11:10.723692 +00:00,2021-11-10 12:15:17.479418 +00:00,ClientRead,idle in transaction
    833,pg_timetable,2021-11-10 12:12:13.671823 +00:00,2021-11-10 12:15:14.123503 +00:00,ClientRead,idle in transaction
    720,pg_timetable,2021-11-10 12:11:11.461636 +00:00,2021-11-10 12:15:16.226433 +00:00,ClientRead,idle in transaction
    727,pg_timetable,2021-11-10 12:11:11.487737 +00:00,2021-11-10 12:15:16.176249 +00:00,ClientRead,idle in transaction
    728,pg_timetable,2021-11-10 12:11:11.492655 +00:00,2021-11-10 12:15:17.042172 +00:00,ClientRead,idle in transaction
    732,pg_timetable,2021-11-10 12:11:11.496748 +00:00,2021-11-10 12:15:16.896660 +00:00,ClientRead,idle in transaction
    731,pg_timetable,2021-11-10 12:11:11.498397 +00:00,2021-11-10 12:15:16.210435 +00:00,ClientRead,idle in transaction
    730,pg_timetable,2021-11-10 12:11:11.499606 +00:00,2021-11-10 12:15:16.181579 +00:00,ClientRead,idle in transaction
    721,pg_timetable,2021-11-10 12:11:11.501228 +00:00,2021-11-10 12:15:17.348453 +00:00,ClientRead,idle in transaction
    729,pg_timetable,2021-11-10 12:11:11.505657 +00:00,2021-11-10 12:15:16.181402 +00:00,ClientRead,idle in transaction
    722,pg_timetable,2021-11-10 12:11:11.507337 +00:00,2021-11-10 12:15:14.369509 +00:00,ClientRead,idle in transaction
    723,pg_timetable,2021-11-10 12:11:11.509050 +00:00,2021-11-10 12:15:15.610615 +00:00,ClientRead,idle in transaction
    734,pg_timetable,2021-11-10 12:11:11.509358 +00:00,2021-11-10 12:15:16.181742 +00:00,ClientRead,idle in transaction
    726,pg_timetable,2021-11-10 12:11:11.514068 +00:00,2021-11-10 12:15:16.731399 +00:00,ClientRead,idle in transaction
    724,pg_timetable,2021-11-10 12:11:11.522302 +00:00,2021-11-10 12:15:17.253562 +00:00,ClientRead,idle in transaction
    733,pg_timetable,2021-11-10 12:11:11.523314 +00:00,2021-11-10 12:15:16.930528 +00:00,ClientRead,idle in transaction
    725,pg_timetable,2021-11-10 12:11:11.523929 +00:00,2021-11-10 12:15:16.269401 +00:00,ClientRead,idle in transaction
    735,pg_timetable,2021-11-10 12:11:11.531098 +00:00,2021-11-10 12:15:16.181921 +00:00,ClientRead,idle in transaction
    1144,pgAdmin 4 - DB:mvno_db,2021-11-10 12:27:37.596660 +00:00,2021-11-10 12:28:18.794345 +00:00,ClientRead,idle
    743,pg_timetable,2021-11-10 12:11:12.027882 +00:00,2021-11-10 12:15:14.465412 +00:00,ClientRead,idle in transaction
    741,pg_timetable,2021-11-10 12:11:12.029709 +00:00,2021-11-10 12:15:17.297606 +00:00,ClientRead,idle in transaction
    742,pg_timetable,2021-11-10 12:11:12.031388 +00:00,2021-11-10 12:15:17.047680 +00:00,ClientRead,idle in transaction
    744,pg_timetable,2021-11-10 12:11:12.800838 +00:00,2021-11-10 12:15:17.398552 +00:00,ClientRead,idle in transaction
    831,pg_timetable,2021-11-10 12:12:12.159367 +00:00,2021-11-10 12:15:17.266761 +00:00,ClientRead,idle in transaction
    832,pg_timetable,2021-11-10 12:12:12.937779 +00:00,2021-11-10 12:15:16.846847 +00:00,ClientRead,idle in transaction
    1095,pg_timetable,2021-11-10 12:15:11.765649 +00:00,2021-11-10 12:15:17.584343 +00:00,ClientRead,idle in transaction
    1092,pg_timetable,2021-11-10 12:15:11.767439 +00:00,2021-11-10 12:15:17.498862 +00:00,ClientRead,idle in transaction
    1093,pg_timetable,2021-11-10 12:15:11.768975 +00:00,2021-11-10 12:15:17.633126 +00:00,ClientRead,idle in transaction
    1096,pg_timetable,2021-11-10 12:15:11.778663 +00:00,2021-11-10 12:15:17.630501 +00:00,ClientRead,idle in transaction
    1094,pg_timetable,2021-11-10 12:15:11.780836 +00:00,2021-11-10 12:15:17.700766 +00:00,ClientRead,idle in transaction
    1097,pg_timetable,2021-11-10 12:15:11.787960 +00:00,2021-11-10 12:15:17.300022 +00:00,ClientRead,idle in transaction
    1098,pg_timetable,2021-11-10 12:15:11.799663 +00:00,2021-11-10 12:15:17.585902 +00:00,ClientRead,idle in transaction
    1099,pg_timetable,2021-11-10 12:15:11.801347 +00:00,2021-11-10 12:15:17.659792 +00:00,ClientRead,idle in transaction
    27,"",,,,
    26,"",,,,
    28,"",,,,
    
    After I restart PG_TIMETABLE, it works fine, but after some time stops again.
    
    I don't know where dig more.
    
    @pashagolub , if you wish, I can share screen or give another access to test system (i.e. Team Viewer), write private message, please.
    bug question 
    opened by ukolovda 17
  • "Cannot log to the database: context deadline exceeded" on subsequent task runs

    Description Output of tasks isn't logged to the timetable.log table on subsequent runs of a task; the first runs are OK, then "Cannot log to the database: context deadline exceeded" is logged on the console.

    I'm not certain what triggers this, and hence how to reproduce this. I'm running a SQL task to create partitions, and which will log a NOTICE when partitions already exist. I've let it run for a while and it doesn't seem to recover once it enters this state.

    Log output Run with run_at set to NULL to run every minute for illustrating the issue.

    [ 2020-08-27 12:47:19.584 | LOG ]: Connection established... [ 2020-08-27 12:47:19.584 | LOG ]: Proceeding as 'timetable_worker' with client PID 1 [ 2020-08-27 12:47:19.597 | LOG ]: Accepting asynchronous chains execution requests... [ 2020-08-27 12:47:19.597 | LOG ]: Checking for @reboot task chains... [ 2020-08-27 12:47:19.599 | LOG ]: Number of chains to be executed: 1 [ 2020-08-27 12:47:19.599 | LOG ]: Checking for task chains... [ 2020-08-27 12:47:19.600 | LOG ]: Checking for interval task chains... [ 2020-08-27 12:47:19.601 | LOG ]: Number of chains to be executed: 1 [ 2020-08-27 12:47:19.604 | LOG ]: Number of active interval chains: 0 [ 2020-08-27 12:47:19.606 | LOG ]: Starting chain ID: 8; configuration ID: 11 [ 2020-08-27 12:47:19.609 | LOG ]: Starting chain ID: 8; configuration ID: 12 [ 2020-08-27 12:47:19.610 | LOG ]: Setting Role to meterology [ 2020-08-27 12:47:19.613 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_08_01" already exists, skipping [ 2020-08-27 12:47:19.613 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_09_01" already exists, skipping [ 2020-08-27 12:47:19.614 | LOG ]: Resetting Role [ 2020-08-27 12:47:19.616 | LOG ]: Setting Role to meterology [ 2020-08-27 12:47:19.617 | LOG ]: Executed successfully chain ID: 8; configuration ID: 11 [ 2020-08-27 12:47:19.618 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_08_01" already exists, skipping [ 2020-08-27 12:47:19.619 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_09_01" already exists, skipping [ 2020-08-27 12:47:19.619 | LOG ]: Resetting Role [ 2020-08-27 12:47:19.621 | LOG ]: Executed successfully chain ID: 8; configuration ID: 12 [ 2020-08-27 12:48:19.604 | LOG ]: Checking for task chains... [ 2020-08-27 12:48:19.618 | LOG ]: Checking for interval task chains... [ 2020-08-27 12:48:19.619 | LOG ]: Number of chains to be executed: 1 [ 2020-08-27 12:48:19.621 | LOG ]: Number of active interval chains: 0 [ 2020-08-27 12:48:19.627 | LOG ]: Starting chain ID: 8; configuration ID: 11 [ 2020-08-27 12:48:19.632 | LOG ]: Setting Role to meterology [ 2020-08-27 12:48:19.634 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_08_01" already exists, skipping [ 2020-08-27 12:48:19.635 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_09_01" already exists, skipping [ 2020-08-27 12:48:19.636 | LOG ]: Resetting Role [ 2020-08-27 12:48:19.638 | LOG ]: Executed successfully chain ID: 8; configuration ID: 11 [ 2020-08-27 12:49:19.619 | LOG ]: Checking for task chains... [ 2020-08-27 12:49:19.633 | LOG ]: Checking for interval task chains... [ 2020-08-27 12:49:19.634 | LOG ]: Number of chains to be executed: 1 [ 2020-08-27 12:49:19.635 | LOG ]: Number of active interval chains: 0 [ 2020-08-27 12:49:19.643 | LOG ]: Starting chain ID: 8; configuration ID: 11 [ 2020-08-27 12:49:19.645 | LOG ]: Setting Role to meterology [ 2020-08-27 12:49:19.647 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_08_01" already exists, skipping [ 2020-08-27 12:49:19.647 | ERROR ]: Cannot log to the database: context deadline exceeded [ 2020-08-27 12:49:19.647 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_09_01" already exists, skipping [ 2020-08-27 12:49:19.647 | ERROR ]: Cannot log to the database: context deadline exceeded [ 2020-08-27 12:49:19.647 | LOG ]: Resetting Role [ 2020-08-27 12:49:19.648 | LOG ]: Executed successfully chain ID: 8; configuration ID: 11 [ 2020-08-27 12:50:19.635 | LOG ]: Checking for task chains... [ 2020-08-27 12:50:19.636 | LOG ]: Checking for interval task chains... [ 2020-08-27 12:50:19.637 | LOG ]: Number of chains to be executed: 1 [ 2020-08-27 12:50:19.638 | LOG ]: Number of active interval chains: 0 [ 2020-08-27 12:50:19.650 | LOG ]: Starting chain ID: 8; configuration ID: 11 [ 2020-08-27 12:50:19.655 | LOG ]: Setting Role to meterology [ 2020-08-27 12:50:19.657 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_08_01" already exists, skipping [ 2020-08-27 12:50:19.657 | ERROR ]: Cannot log to the database: context deadline exceeded [ 2020-08-27 12:50:19.657 | USER ]: Severity: NOTICE; Message: relation "lightning_month_2020_09_01" already exists, skipping [ 2020-08-27 12:50:19.657 | ERROR ]: Cannot log to the database: context deadline exceeded [ 2020-08-27 12:50:19.657 | LOG ]: Resetting Role [ 2020-08-27 12:50:19.661 | LOG ]: Executed successfully chain ID: 8; configuration ID: 11

    Only the first runs are indeed logged:

    postgres=> SELECT * FROM timetable.log WHERE log_level != 'LOG' AND ts >= now() - '30 minute'::interval ORDER BY ts DESC limit 10;
      id   |              ts               |   client_name    | pid | log_level |                                          message                                          
    -------+-------------------------------+------------------+-----+-----------+-------------------------------------------------------------------------------------------
     37723 | 2020-08-27 12:48:19.635563+00 | timetable_worker |   1 | USER      | Severity: NOTICE; Message: relation "lightning_month_2020_09_01" already exists, skipping
     37722 | 2020-08-27 12:48:19.634825+00 | timetable_worker |   1 | USER      | Severity: NOTICE; Message: relation "lightning_month_2020_08_01" already exists, skipping
     37713 | 2020-08-27 12:47:19.619225+00 | timetable_worker |   1 | USER      | Severity: NOTICE; Message: relation "lightning_month_2020_09_01" already exists, skipping
     37712 | 2020-08-27 12:47:19.618139+00 | timetable_worker |   1 | USER      | Severity: NOTICE; Message: relation "lightning_month_2020_08_01" already exists, skipping
     37708 | 2020-08-27 12:47:19.613945+00 | timetable_worker |   1 | USER      | Severity: NOTICE; Message: relation "lightning_month_2020_09_01" already exists, skipping
     37707 | 2020-08-27 12:47:19.613332+00 | timetable_worker |   1 | USER      | Severity: NOTICE; Message: relation "lightning_month_2020_08_01" already exists, skipping
    (6 rows)
    

    execution_log is correct:

    postgres=> SELECT * FROM timetable.execution_log WHERE last_run >= now() - '30 minute'::interval ORDER BY last_run DESC;
     chain_execution_config | chain_id | task_id |           name           |                    script                     | kind |           last_run            |           finished            | returncode | pid | output |   client_name    
    ------------------------+----------+---------+--------------------------+-----------------------------------------------+------+-------------------------------+-------------------------------+------------+-----+--------+------------------
                         11 |        8 |      14 | CreateLightningPartition | CALL meterology.create_lightning_partitions() | SQL  | 2020-08-27 12:50:19.655466+00 | 2020-08-27 12:50:19.65922+00  |          0 |   1 |        | timetable_worker
                         11 |        8 |      14 | CreateLightningPartition | CALL meterology.create_lightning_partitions() | SQL  | 2020-08-27 12:49:19.646018+00 | 2020-08-27 12:49:19.647791+00 |          0 |   1 |        | timetable_worker
                         11 |        8 |      14 | CreateLightningPartition | CALL meterology.create_lightning_partitions() | SQL  | 2020-08-27 12:48:19.632418+00 | 2020-08-27 12:48:19.637294+00 |          0 |   1 |        | timetable_worker
                         12 |        8 |      14 | CreateLightningPartition | CALL meterology.create_lightning_partitions() | SQL  | 2020-08-27 12:47:19.6162+00   | 2020-08-27 12:47:19.620595+00 |          0 |   1 |        | timetable_worker
                         11 |        8 |      14 | CreateLightningPartition | CALL meterology.create_lightning_partitions() | SQL  | 2020-08-27 12:47:19.611106+00 | 2020-08-27 12:47:19.61568+00  |          0 |   1 |        | timetable_worker
    
    bug 
    opened by thormick 15
  • Too big `run_status` table after some time

    Too big `run_status` table after some time

    Is your feature request related to a problem? Please describe. The service insert many rows in the table run_status (one row when starting and one when finished). Over time it contains too many rows (about 3M rows in my test system). Function get_chain_running_statuses works too slow (~2-3 seconds for each call). CPU usage too high.

    Describe the solution you'd like I need procedure for remove old rows from run_status table. It can be started by schedule. It may be built-in internal command or stored function (preferred), or both.

    As I think, the query is:

    delete from timetable.run_status
    where run_status_id in (
      select unnest( array[ start_status.run_status_id, finish_status.run_status_id ] )
      from timetable.run_status start_status
             join timetable.run_status finish_status
                  on start_status.run_status_id = finish_status.start_status_id
                    AND finish_status.execution_status IN ('CHAIN_FAILED', 'CHAIN_DONE', 'DEAD')
      WHERE start_status.execution_status = 'CHAIN_STARTED'
    );
    

    Please, correct me if it is wrong.

    Describe alternatives you've considered Manual delete old rows from table run_status (see query above).

    Additional context Explain plan is: изображение

    question 
    opened by ukolovda 13
  • v4.4.0 No tasks run, Windows 10, testing CronStyle.sql

    v4.4.0 No tasks run, Windows 10, testing CronStyle.sql

    No tasks run, no message is inserted into timetable.dummy_log using the samples/CronStyle.sql case. I changed the CRON specification to run every minute '* * * * *'. I can manually insert a message into the log. There must be a configuration error? This test is running on Windows 10. Thank you for any guidance.

    pg_timetable /v
    pg_timetable:
      Version:      4.4.0
      DB Schema:    00381
      Git Commit:   52e12177d0025b9b01c737cea06048fc350315f5
      Built:        2022-02-07T14:06:57Z
    

    Tables: active chain table is empty

    active session image

    chain image

    dummy_log (manually inserted record) image

    execution log image

    log image

    migration image

    parameter image

    task image

    pg_timetable postgresql://[email protected]:5444/tdb /log-level:debug /debug --clientname=sched01
    2022-02-21 09:37:25.645 [DEBUG] [PID:41672] Starting new session...  (pgengine/bootstrap.go:69 pgengine.New)
    2022-02-21 09:37:26.021 [DEBUG] [host:localhost] Dialing PostgreSQL server
    2022-02-21 09:37:26.215 [DEBUG] [ConnPID:14412] [client:sched01] Trying to get lock for the session (pgengine/bootstrap.go:141 pgengine.(*PgEngine).getPgxConnConfig.func2)
    2022-02-21 09:37:26.216 [DEBUG] [sql:SELECT COALESCE(to_regproc('timetable.try_lock_client_name')::int4, 0)] [args:[]] [pid:14412] [rowCount:1] [time:531.1µs] Query
    2022-02-21 09:37:26.221 [DEBUG] [sql:SELECT timetable.try_lock_client_name(41672, $worker$sched01$worker$)] [args:[]] [pid:14412] [rowCount:1] [time:3.509ms] Query
    2022-02-21 09:37:26.222 [DEBUG] [sql:LISTEN "sched01"] [args:[]] [commandTag:LISTEN] [pid:14412] [time:1.219ms] Exec
    2022-02-21 09:37:26.222 [INFO] Database connection established (pgengine/bootstrap.go:93 pgengine.New)
    2022-02-21 09:37:26.223 [DEBUG] [sql:SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'timetable')] [args:[]] [pid:14412] [rowCount:1] [time:523.1µs] Query
    2022-02-21 09:37:26.223 [DEBUG] Check need of upgrading database... (pgengine/migration.go:39 pgengine.(*PgEngine).CheckNeedMigrateDb)
    2022-02-21 09:37:26.224 [DEBUG] [sql:SELECT to_regclass($1) IS NOT NULL] [args:[timetable.migration]] [pid:14412] [rowCount:1] [time:0s] Query
    2022-02-21 09:37:26.225 [DEBUG] [sql:SELECT count(*) FROM timetable.migration] [args:[]] [pid:14412] [rowCount:1] [time:508.5µs] Query
    2022-02-21 09:37:26.225 [INFO] Accepting asynchronous chains execution requests... (scheduler/scheduler.go:110 scheduler.(*Scheduler).Run)
    2022-02-21 09:37:28.232 [DEBUG] [host:localhost] Dialing PostgreSQL server
    2022-02-21 09:37:28.425 [DEBUG] [ConnPID:8340] [client:sched01] Trying to get lock for the session (pgengine/bootstrap.go:141 pgengine.(*PgEngine).getPgxConnConfig.func2)
    2022-02-21 09:37:28.427 [DEBUG] [sql:SELECT COALESCE(to_regproc('timetable.try_lock_client_name')::int4, 0)] [args:[]] [pid:8340] [rowCount:1] [time:1.6062ms] Query
    2022-02-21 09:37:28.431 [DEBUG] [sql:SELECT timetable.try_lock_client_name(41672, $worker$sched01$worker$)] [args:[]] [pid:8340] [rowCount:1] [time:4.3126ms] Query
    2022-02-21 09:37:28.432 [DEBUG] [sql:LISTEN "sched01"] [args:[]] [commandTag:LISTEN] [pid:8340] [time:0s] Exec
    2022-02-21 09:37:28.433 [DEBUG] [columnNames:[ts client_name pid log_level message message_data]] [pid:8340] [rowCount:1] [tableName:[timetable log]] [time:607.8µs] CopyFrom
    
    wontfix 
    opened by apiszcz 12
  • Can not remote to other server

    Can not remote to other server

    I always get result pq: relation "public.user_account" does not exist when using remote SQL.

    base_task table:

    INSERT INTO timetable.base_task (task_id, name, kind, script) VALUES (6, 'Remote SQL', 'SQL', 'SELECT * FROM public.user_account;');
    

    database_connection table:

    INSERT INTO timetable.database_connection (database_connection, connect_string, comment) VALUES (1, 'host=127.0.0.1 port=5432 dbname=abcxyz user=application password=p4ssw0rd sslmode=disable', '[email protected]/32');
    

    task_chain table:

    INSERT INTO timetable.task_chain (chain_id, parent_id, task_id, run_uid, database_connection, ignore_error, autonomous) VALUES (1, null, 6, null, 1, true, false);
    

    chain_execution_config table:

    INSERT INTO timetable.chain_execution_config (chain_execution_config, chain_id, chain_name, run_at, max_instances, live, self_destruct, exclusive_execution, excluded_execution_configs, client_name) VALUES (1, 1, 'truncate_deal_user', '* * * * *', 1, true, false, false, null, null);
    
    bug 
    opened by tunhvn 8
  • run_at generates timestamp of +1 month

    run_at generates timestamp of +1 month

    Describe the bug This is a strange one , if i use the cron string of '0 7 * * *', for today (30-march 2021), run at throws an date/time field value out of range: "2021-4-31 7:0" error.

    Have I configured something incorrectly on my server? I'm using a standard pg docker image?

    To Reproduce Steps to reproduce the behavior: Run SELECT timetable.next_run('0 7 * * *'::timetable.cron); on 30th march

    Expected behavior A clear and concise description of what you expected to happen.

    Screenshots I have added some debug notices to the function

    image

    bug priority 
    opened by adulion2 7
  • Collect client messages for SQL tasks

    Collect client messages for SQL tasks

    For functions and stored procedures in SQL it would be great to capture client messages from raise notice etc with a configurable log level like set client_min_messages.

    I was hoping to migrate from airflow for scheduled SQL tasks but the inability to see stored proc logs means I can't at this point.

    enhancement 
    opened by a-mckinley 7
  • Add some randomness to task intervals

    Add some randomness to task intervals

    Currently when adding a lot (tried with 500) of tasks the CPU graph looks like the image in attach which is not optimal. Had the some problem with pgwatch2, solution is to add some small delays for the first run of a task to make it more evenly distributed.

    enhancement 
    opened by kmoppel 7
  • Bump github.com/ory/dockertest/v3 from 3.6.0 to 3.6.3

    Bump github.com/ory/dockertest/v3 from 3.6.0 to 3.6.3

    Bumps github.com/ory/dockertest/v3 from 3.6.0 to 3.6.3.

    Commits

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Note: This repo was added to Dependabot recently, so you'll receive a maximum of 5 PRs for your first few update runs. Once an update run creates fewer than 5 PRs we'll remove that limit.

    You can always request more updates by clicking Bump now in your Dependabot dashboard.

    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    • @dependabot use these labels will set the current labels as the default for future PRs for this repo and language
    • @dependabot use these reviewers will set the current reviewers as the default for future PRs for this repo and language
    • @dependabot use these assignees will set the current assignees as the default for future PRs for this repo and language
    • @dependabot use this milestone will set the current milestone as the default for future PRs for this repo and language
    • @dependabot badge me will comment on this PR with code to add a "Dependabot enabled" badge to your readme

    Additionally, you can set the following in your Dependabot dashboard:

    • Update frequency (including time of day and day of week)
    • Pull request limits (per update run and/or open at any time)
    • Out-of-range updates (receive only lockfile updates, if desired)
    • Security updates (receive only security updates, if desired)
    dependencies 
    opened by dependabot-preview[bot] 6
  • Optional internal HTTP server for get service status or other metrics

    Optional internal HTTP server for get service status or other metrics

    Is your feature request related to a problem? Please describe. I try run pg_timetable as service in Gitlab CI (with PostgreSQL service). But I've got the error:

    Health check error:
    service "runner-z52vvf7a-project-66-concurrent-1-8460e97395a3dbe4-cybertecpostgresql__pg_timetable-1-wait-for-service" health check: exit code 1
    Health check container logs:
    FATAL: No HOST or PORT found
    

    Describe the solution you'd like Make internal HTTP-service, used for checking pg_timetable status (health).

    It also will be useful for running in Kubernetes. And read online info (currently running tasks, total processed tasks, etc) for Prometheus diagnostics.

    I think, It should be optional (by security reasons), and should not start by default.

    Describe alternatives you've considered I don't know.

    Additional context Add any other context or screenshots about the feature request here.

    enhancement 
    opened by ukolovda 5
  • Task not started

    Task not started

    Hi,

    In my chain_execution_config I have few chains and tasks. For description of the problem that I noticed only three tasks from my config are relevant. One task is executed every minute and two tasks every 5 minutes (0, 5, 10, 15, 20, 25, 30 ... 55). Chain config is in attachment. In normal situation every 5 minutes at least three chains will be executed.

    Other tasks are executed once daily, every hour etc. , this is not relevant for the description of the problem.

    I can see from log that when "Checking for task chains..." is executed at the very end of minute ( 58.398 sec ) then in next step "Number of chains to be executed:" is not ok.

    For example:

    [2020-09-09 01:55:55.327 | umbosspm | LOG ]: Checking for task chains... [2020-09-09 01:55:55.332 | umbosspm | LOG ]: Number of chains to be executed: 3 <- OK . . . [2020-09-09 02:00:58.398 | umbosspm | LOG ]: Checking for task chains... [2020-09-09 02:01:00.958 | umbosspm | LOG ]: Number of chains to be executed: 1 <- NOT OK ( this is chain that is executed every minute, other two chains are missing) . . . . [2020-09-09 02:05:05.003 | umbosspm | LOG ]: Checking for task chains... [2020-09-09 02:05:05.007 | umbosspm | LOG ]: Number of chains to be executed: 3 <- OK . . . [2020-09-09 02:10:08.605 | umbosspm | LOG ]: Checking for task chains... [2020-09-09 02:10:08.650 | umbosspm | LOG ]: Number of chains to be executed: 4 <- OK . . . . [2020-09-09 02:15:09.703 | umbosspm | LOG ]: Checking for task chains... [2020-09-09 02:15:09.917 | umbosspm | LOG ]: Number of chains to be executed: 3 <- OK

    docker_log_part.txt chain_execution_config.txt

    bug question 
    opened by drenbutkovic 5
  • Bump github.com/stretchr/testify from 1.7.4 to 1.7.5

    Bump github.com/stretchr/testify from 1.7.4 to 1.7.5

    Bumps github.com/stretchr/testify from 1.7.4 to 1.7.5.

    Commits

    Dependabot compatibility score

    Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.


    Dependabot commands and options

    You can trigger Dependabot actions by commenting on this PR:

    • @dependabot rebase will rebase this PR
    • @dependabot recreate will recreate this PR, overwriting any edits that have been made to it
    • @dependabot merge will merge this PR after your CI passes on it
    • @dependabot squash and merge will squash and merge this PR after your CI passes on it
    • @dependabot cancel merge will cancel a previously requested merge and block automerging
    • @dependabot reopen will reopen this PR if it is closed
    • @dependabot close will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually
    • @dependabot ignore this major version will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this minor version will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself)
    • @dependabot ignore this dependency will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself)
    dependencies go 
    opened by dependabot[bot] 0
  • Add `--cluster` mode

    Add `--cluster` mode

    Add support for multiple databases into pg_timetable service in the following way:   When connected to system database (postgres) or when --cluster argument is used then use pg_database (or some granted view above it for security) to find all databases. Then it will try to connect to each database and if it finds timetable functionality there, it will start handling timetable jobs for that database. The list of databases should be regularly refreshed or there should be some command to do it explicitly.  

    1. pg_timetable connects to any of databases (for simplicity, we can even use pg_template1 for this purpose in --cluster)
    2. pg_timetable checks the presence of the timetable schema in every database
    3. if timetable schema is present, that means database considered a target, start the main loop over it
    enhancement 
    opened by pashagolub 1
  • Refactor to an extension

    Refactor to an extension

    I was wondering if there is any chance to refactor this to an extension. There are several Golang psql extensions already, so - at least theoretically - that seems feasible. I haven't checked the code myself yet, but since there is a pg_cron extension, why not moving this to a more closely-coupled form?

    If there is a good argument for not doing that, before I will spend hours analyzing the code, please do share with me :-D

    enhancement 
    opened by uded 13
Releases(v4.7.0)
Owner
CYBERTEC PostgreSQL International GmbH
Professional PostgreSQL services since year 2000
CYBERTEC PostgreSQL International GmbH
Cross-platform client for PostgreSQL databases

pgweb Web-based PostgreSQL database browser written in Go. Overview Pgweb is a web-based database browser for PostgreSQL, written in Go and works on O

Dan Sosedoff 7.3k Jun 23, 2022
pREST (PostgreSQL REST), simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new

pREST pREST (PostgreSQL REST), simplify and accelerate development, instant, realtime, high-performance on any Postgres application, existing or new P

pREST 3.3k Jun 29, 2022
PostgreSQL style Parser splitted from CockroachDB

What's this PostgreSQL style Parser splitted from CockroachDB See: Complex SQL format example

auxten 133 Jun 27, 2022
Enhanced PostgreSQL logical replication

pgcat - Enhanced postgresql logical replication Why pgcat? Architecture Build from source Install Run Conflict handling Table mapping Replication iden

jinhua luo 357 May 28, 2022
Interactive client for PostgreSQL and MySQL

dblab Interactive client for PostgreSQL and MySQL. Overview dblab is a fast and lightweight interactive terminal based UI application for PostgreSQL a

Daniel Omar Vergara Pérez 196 Jun 21, 2022
WAL-G is an archival restoration tool for PostgreSQL, MySQL/MariaDB, and MS SQL Server (beta for MongoDB and Redis).

WAL-G is an archival restoration tool for PostgreSQL, MySQL/MariaDB, and MS SQL Server (beta for MongoDB and Redis).

null 2.2k Jun 28, 2022
Worker failover support for PostgreSQL Citus extension using pg_auto_failover.

citus-failover Worker failover support for citus community version using pg_auto_failover. What is this? This is a simple service to monitor changes i

Navid Zarepak 20 Jun 5, 2022
Interactive terminal user interface and CLI for database connections. MySQL, PostgreSQL. More to come.

?? dbui dbui is the terminal user interface and CLI for database connections. It provides features like, Connect to multiple data sources and instance

Kanan Rahimov 88 Jun 18, 2022
Modify orca-zhang/borm in order to use in PostgreSQL

borm ??️ 针对 orca-zhang/borm 进行了修改,暂时只能兼容PostgreSQL 原因 在b站时候用过borm,用起来感觉非常简洁 自己学校里用PostgreSQL比较多 可变条件真的非常好用 问题 首先需要注意的是,这是写给PG的 PG 根本不存在某些 MySQL 独有的函数

null 13 Jul 27, 2021
Cross-platform client for PostgreSQL databases

pgweb Web-based PostgreSQL database browser written in Go. Overview Pgweb is a web-based database browser for PostgreSQL, written in Go and works on O

Dan Sosedoff 7.3k Jun 21, 2022
PolarDB Cluster Manager is the cluster management component of PolarDB for PostgreSQL, responsible for topology management, high availability, configuration management, and plugin extensions.

What is PolarDB Cluster Manager PolarDB Cluster Manager is the cluster management component of PolarDB for PostgreSQL, responsible for topology manage

null 8 Dec 15, 2021
A simple Golang-based application that queries a PostgreSQL database

Qwik-E-Mart Demo App A simple Golang-based application that queries a PostgreSQL database named qwikemart to read and return customer data stored in t

Joe Garcia 0 Nov 6, 2021
CloudQuery extracts, transforms, and loads your cloud assets into normalized PostgreSQL tables.

The open-source cloud asset inventory backed by SQL. CloudQuery extracts, transforms, and loads your cloud assets into normalized PostgreSQL tables. C

CloudQuery 2.3k Jun 21, 2022
Implemented PostgreSQL with Golang

Customer Information Web Api Implemented PostgreSQL with Golang docker run --name postgresql-container -p 5432:5432 -e POSTGRES_PASSWORD=Password! -d

bseyhan 2 Nov 15, 2021
Typescript type declaration to PostgreSQL CREATE TABLE converter

ts2psql NOTE: This is WIP. Details in this readme are ideal state. Current usage: go build && ./ts2psql (or go build && ts2psql if on Windows OS). A s

null 1 Jan 13, 2022
An experimental toolkit for injecting alternate authentication strategies into a PostgreSQL-compatible wire format.

PG Auth Proxy This is an experimental toolkit for injecting alternate authentication strategies into a PostgreSQL-compatible wire format. This is a pr

Cockroach Labs 1 Jan 20, 2022
Thin clones of PostgreSQL to build powerful development, test, QA, staging environments

Database Lab Engine (DLE) ⚡ Blazing-fast cloning of PostgreSQL databases ?? Thin clones of PostgreSQL to build powerful development, test, QA, staging

Postgres.ai 1.3k Jun 28, 2022
Dugopg - PostgreSQL tool For Golang

⚡️ DuGoPG Installation go get -u github.com/durudex/dugopg Example import (

Durudex 5 May 9, 2022
📊 parallel black box PostgreSQL unit tests run against a real database.

?? psql-docker-tests-example Parallel black box PostgreSQL unit tests run against a real database. Consider reading the Medium Story first. This packa

Adrian Brad 4 May 12, 2022