March 28, 2025
Summary: The pg_timetable program is an advanced scheduling tool for PostgreSQL.
Table of Contents
Introduction to pg_timetable
In database management, job scheduling has always played an important role. And for PostgreSQL, PgAgent and pg_cron are examples of job schedulers that are already popular. However, there is another job scheduler called pg_timetable, which is completely database-driven and provides a couple of advanced concepts. In this article, we are going to highlight some of the main features, installation, and some use cases of pg_timetable.
Main features
- Fully database-driven configuration
- Cron-style scheduling at the PostgreSQL server time zone
- Built-in tasks such as sending emails, etc.
- Full support for database-driven logging
- Tasks can be arranged in chains
- A chain can consist of built-in commands, SQL, and executables
- Parameters can be passed to chains/tasks
Installation
There are currently two options on how you can install and run pg_timetable.
- Container installation. For this, please refer to this page.
- Local installation, i.e., build from sources. Here, we will discuss this approach.
1. Download and install GO on your system.
2. Clone pg_timetable repo
$ git clone https://github.com/cybertec-postgresql/pg_timetable.git
$ cd pg_timetable
3. Run pg_timetable
$ go run main.go --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpassword
4. Alternatively, build a binary and run it:
$ go build
$ ./pg_timetable --dbname=dbname --clientname=worker001 --user=scheduler --password=strongpassword
Demonstration of job scheduling with pg_timetable
As a use case here, it will be shown how to use pg_timetable as a scheduler to schedule a job, which will refresh the materialized view every day at 12 midnight.
1. Download pg_timetable executable (Follow step 2 mentioned above in the installation section).
2. Make sure the PostgreSQL server is up and running and has a role with CREATE privilege for a target database, e.g.:
CREATE ROLE scheduler PASSWORD '***********';
GRANT CREATE ON DATABASE postgres TO scheduler;
CREATE TABLE t_demo (grp int, data numeric);
INSERT INTO t_demo SELECT 1, random()
FROM generate_series(1, 5000000);
CREATE MATERIALIZED VIEW mat_view AS
SELECT grp, avg(data), count(*)
FROM t_demo
GROUP BY 1;
ALTER MATERIALIZED VIEW mat_view OWNER TO scheduler;
GRANT SELECT ON mat_view TO scheduler;
SELECT * FROM mat_view;
grp | avg | count
-----+--------------------------+---------
1 | 0.5001807958659610956005 | 5000000
(1 row)
INSERT INTO t_demo
SELECT 2, random()
FROM generate_series(1, 5000000);
3. Create a new job to refresh the materialized view each night at 12:00 Postgres server time zone.
SELECT timetable.add_job('refresh-matview', '0 12 * * *', 'REFRESH MATERIALIZED VIEW public.mat_view');
add_job
---------
1
(1 row)
4. Run the pg_timetable
$ ./pg_timetable --dbname=postgres --clientname=worker001 --user=scheduler --password=********
2024-09-09 11:59:20.929 [INFO] [sid:697146069] Starting new session...
2024-09-09 11:59:20.941 [INFO] Database connection established
2024-09-10 12:00:00.961 [INFO] Accepting asynchronous chains execution requests...
2024-09-10 12:00:00.970 [INFO] [count:0] Retrieve scheduled chains to run @reboot
2024-09-10 12:00:00.991 [INFO] [count:3] Retrieve scheduled chains to run
2024-09-10 12:00:00.994 [INFO] [count:0] Retrieve interval chains to run
2024-09-10 12:00:00.019 [INFO] [chain:1] Starting chain
2024-09-10 12:00:00.722 [INFO] [chain:1] [task:1] [txid:2613] Starting task
2024-09-10 12:00:00.074 [INFO] [chain:1] [task:1] [txid:2613] Starting task
2024-09-10 12:00:00.141 [INFO] [chain:1] [task:1] [txid:2613] Closing remote session
2024-09-10 12:00:00.141 [INFO] [chain:1] [task:1] [txid:2613] Task executed successfully
2024-09-10 12:00:00.185 [INFO] [chain:1] [txid:2613] Chain executed successfully
During the first start of pg_timetable, the necessary schema timetable gets created. For reference, below is the catalog structure.
postgres=# \dn
List of schemas
Name | Owner
-----------+-----------
public | postgres
timetable | scheduler
(2 rows)
postgres=# set search_path to timetable ;
postgres=# \dt
List of relations
Schema | Name | Type | Owner
-----------+----------------+-------+-----------
timetable | active_chain | table | scheduler
timetable | active_session | table | scheduler
timetable | chain | table | scheduler
timetable | execution_log | table | scheduler
timetable | log | table | scheduler
timetable | migration | table | scheduler
timetable | parameter | table | scheduler
timetable | task | table | scheduler
(8 rows)
5. From database logs, it can be observed that MATERIALIZED VIEW gets refreshed as per the schedule.
Output from DB logs:
2024-09-10 12:00:00 UTC [14334] LOG: statement: REFRESH MATERIALIZED VIEW public.mat_view
From psql prompt:
SELECT * FROM mat_view;
grp | avg | count
-----+---------------------------+---------
1 | 0.5001807958659610956005 | 5000000
2 | 0.50000009110202547559215 | 5000000
(2 rows)
Below is the output from the pg_timetable catalog tables.
select * from active_session ;
client_pid | server_pid | client_name | started_at
------------+------------+-------------+-----------------------------
697146069 | 20137 | worker001 | 2024-09-10 11:59:205672+00
(1 row)
select * from chain;
-[ RECORD 1 ]--------+--------------------
chain_id | 1
chain_name | refresh-matview
run_at | 0 12 * * *
max_instances |
timeout | 0
live | t
self_destruct | f
exclusive_execution | f
client_name |
on_error |
select * from execution_log where chain_id=1;
-[ RECORD 1 ]-+------------------------------------------
chain_id | 1
task_id | 1
txid | 2613
last_run | 2024-09-10 12:00:00.137404+00
finished | 2024-09-10 12:00:00.586543+00
pid | 697146069
returncode | 0
kind | SQL
command | REFRESH MATERIALIZED VIEW public.mat_view
output | REFRESH MATERIALIZED VIEW
client_name | worker001
In conclusion, we can say that pg_timetable is open source and can be used freely by everyone. The main advantages include that pg_timetable is an independent process written in GO, which connects to PostgreSQL just like any other client program. So, if the scheduler crashes, it will not harm your server. Pg_timetable provides a variety of built-in tasks that help you to flexibly combine those operations in an easy way. Further, pg_timetable has been implemented in GO and, therefore, comes as ONE executable that can be started directly. So, there is no need to worry about libraries or dependencies during the installation.