Using pg_cron in Managed Service for PostgreSQL

The pg_cron extension is a job scheduler allowing you to schedule database tasks and run SQL queries directly within a job.

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

To install pg_cron in a PostgreSQL cluster:

1. Load the shared library pg_cron into your cluster.

2. Enable the pg_cron extension in one of your databases. You cannot enable this extension for two databases.

   Warning

   Installing pg_cron will trigger a rolling restart of PostgreSQL on all cluster hosts.

3. Assign the mdb_admin role to the user who will be managing the jobs.

The database will now contain the cron schema with tables and functions required for the extension:

• Tables:

  • cron.job: Contains scheduled jobs. You can run SELECT queries on this table.
  • cron.job_run_details: Contains extension run history. You can run SELECT, UPDATE, and DELETE queries on this table.

• Functions:

  • schedule: Creates a job in the database with the installed pg_cron extension.
  • schedule_in_database: Creates a job in a different database.
  • unschedule: Deletes a job.
  • alter_job: Alters a job.

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