pg_cron extension TECH PREVIEW

The pg_cron extension provides a cron-based job scheduler that runs inside the database. It uses the same syntax as regular cron, and allows you to schedule YSQL commands directly from the database. You can also use '[1-59] seconds' to schedule a job based on an interval.

YugabyteDB supports all features of the pg_cron extension. Although YugabyteDB is a distributed database that operates on multiple nodes, pg_cron only runs on one of these nodes, called the pg_cron leader. Only the pg_cron leader schedules and runs the cron jobs. The queries executed by jobs do take advantage of all available resources in the cluster.

If the pg_cron leader node fails, another node is automatically elected as the new leader to ensure it is highly available. This process is transparent, and you can connect to any node in a cluster to schedule jobs.

Set up pg_cron

pg_cron in YugabyteDB is TP . Before you can use the feature, you must enable it by setting the enable_pg_cron flag. To do this, add enable_pg_cron to the allowed_preview_flags_csv flag and set the enable_pg_cron flag to true on all YB-Masters and YB-TServers.

The pg_cron extension is installed on only one database, which stores the extension data. The default cron database is yugabyte. You can change it by setting the ysql_cron_database_name flag on all YB-TServers. You can create the database after setting the flag.

For example, to create a single-node yugabyted cluster with pg_cron on database 'db1', use the following command:

./bin/yugabyted start --master_flags "allowed_preview_flags_csv={enable_pg_cron},enable_pg_cron=true" --tserver_flags "allowed_preview_flags_csv={enable_pg_cron},enable_pg_cron=true,ysql_cron_database_name=db1" --ui false

To change the database after the extension is created, you must first drop the extension and then change the flag value.

Enable pg_cron

Create the extension as superuser on the cron database.

CREATE EXTENSION pg_cron;

You can grant access to other users to use the extension. For example:

GRANT USAGE ON SCHEMA cron TO elephant;

Use pg_cron

YugabyteDB supports all features and syntax of the pg_cron extension.

For example, the following command calls a stored procedure every five seconds:

SELECT cron.schedule('process-updates', '5 seconds', 'CALL process_updates()');

If you need to run jobs in multiple databases, use cron.schedule_in_database().

When running jobs, keep in mind the following:

• It may take up to 60 seconds for job changes to get picked up by the pg_cron leader.
• When a new pg_cron leader node is elected, no jobs are run for the first minute. Any job that were in flight on the failed node will not be retried, as their outcome is not known.

For more information on how to schedule jobs, refer to the pg_cron documentation.