How to Schedule Maintenance with PostgreSQL pg cron Extension | Google Cloud Platform SQL Tutorial

Topic:  How to Schedule Maintenance with PostgreSQL pg cron Extension

How to Connect to GCP SQL Server Instance by using Private IP Configuration | GCP SQL Tutorial 2022, in this video we are going to learn How to Connect to GCP SQL Server Instance by using Private IP Configuration | GCP SQL Tutorial 2022

Script:

--Postgres Instance Level Settings

cloudsql.enable_pg_cron 

value= on

cron.database_name 

Value : Provide your Database Name

  

-- check if extensions are installed

select * from pg_extension;

-- use this to create extension on db, it will create two tables job and job_run_detail

CREATE EXTENSION pg_cron;

-- list of jobs to run on schedule

Select * from cron.job;

  

-- Check executed jobs status

SELECT jobid, runid, job_pid, database, username, command, status, return_message, start_time, end_time

FROM cron.job_run_details;

  

-- Create Schedule to Delete records, insrt record or create index

SELECT cron.schedule('27 19 * * *', $$DELETE FROM public.Person$$);

SELECT cron.schedule('31 19 * * *', $$insert into public.Person values (2,'Raza',200)$$);

-- create schedule to create an index on table

SELECT cron.schedule('36 19 * * *', $$CREATE INDEX idx_pid ON public.Person(id)$$);

  

  -- Schedule to run script on another database

  -- SELECT cron.schedule('<Schedule>', '<Task>', '<Database>')

  SELECT cron.schedule('40 19 * * *', 'test2','create table public.crontab(id int)' )

  

  -- also we can insert into cron.job manually

  INSERT INTO cron.job(

jobid, schedule, command, nodename, nodeport, database, username, active, jobname)

VALUES (7, '55 19 * * *', 'create table public.crontab(id int)','localhost', '5432', 'test2', 'postgres', true,'testjob');

  

  

  

-- Delete schedule

-- SELECT cron.unschedule(<ID of the scheduled task>) -- you will get the schedule if from cron.job

  Select * from cron.job;

  -- Delete the Schedule

  select cron.unschedule(6)

Video Demo: How to Schedule Maintenance with PostgreSQL pg cron Extension