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 pg_cron in a PostgreSQL cluster:

1. Connect the shared library named pg_cron to your cluster.

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

   Warning

   Installing pg_cron will restart PostgreSQL on all cluster hosts one by one.

3. Add a user with the mdb_admin role or mdb_superuser role to manage the jobs.

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 extension 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: Modifies the job.

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