Using pg_cron in Managed Service for PostgreSQL

The pg_cron extension is a job scheduler that enables you to add scheduled jobs to a database and execute SQL commands directly from a job.

Install the pg_cron extension in a PostgreSQL clusterInstall the pg_cron extension in a PostgreSQL cluster

To install the pg_cron extension in a PostgreSQL cluster:

1. Enable the shared library with the name pg_cron for a cluster.

2. Add the pg_cron extension to one of the databases. You cannot enable the extension for two databases.

   Warning

   Installing the pg_cron extension will cause PostgreSQL to restart sequentially on all cluster hosts.

3. Add a user with the mdb_admin role to manage the tasks.

In the selected database, the cron schema with tables and functions required for the extension to work will appear:

• Tables:

  • cron.job: Contains scheduled jobs. The SELECT command is allowed.
  • cron.job_run_details: Contains the history of runs. The SELECT, UPDATE, and DELETE commands are allowed.

• Functions:

  • schedule: Creates a job in the database where the pg_cron extension is installed.
  • schedule_in_database: Creates a job in another database.
  • unschedule: Deletes a job.
  • alter_job: Changes a job.

To learn more about the pg_cron extension, see the official documentation.