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)
This is a great resource for understanding how to use the PostgreSQL pg cron Extension to schedule maintenance tasks. It provides a clear explanation of how to set up a cron job and provides helpful examples and also i need someone to do my assignment please let me help out the best writer please!
ReplyDelete