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

6 comments:

  1. 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
  2. This maintenance scheduler I often use for splatoon 3 and find it really useful. I really like this

    ReplyDelete
  3. Exploring the top shipping companies in Karachi can be overwhelming for anyone who needs to transport goods. However, with a comprehensive guide, the process becomes easier and less daunting. As one of the busiest shipping ports in Pakistan, Karachi has a plethora of shipping companies that offer various shipping services. From container shipping and air freight to customs clearance and warehousing, there's a shipping company in Karachi that will meet your needs. In this guide, we highlight the top shipping companies in Karachi to help you make an informed decision when choosing a shipping partner.

    ReplyDelete
  4. Victim of a BTC Scam? Contact Ultimate Hacker Jerry to recover your Lost Bitcoin.

    Have you ever been a victim of a scam? or have you Suffered a loss from a Fraudulent Ponzi Scheme? I implore you to Contact Ultimate Hacker Jerry, A Certified hacker and Lost Crypto Recovery Expert. I once fell victim to an online imposter who convinced me to invest in a phoney Cryptocurrency scheme by claiming to have made large profits from the plan. My Trezor wallet contained $219,100 in Crypto that I lost, I had been reporting to the Authorities tirelessly for a longtime without getting assistance before I finally got in touch with Ultimate Hacker Jerry. Fortunately after a serious conversation with Ultimate Hacker Jerry all my funds were recovered back.
    I recommend this Expert to any victim who has lost Crypto to any fake online Ponzi Scheme..

    Get in touch with ULTIMATE HACKER JERRY
    Through ; Mail (Ultimatehackerjerry@seznam. cz)
    WhatsAp, (+1,5/20,2827,15,1)

    ReplyDelete
  5. As an essay writer from https://bestwritinghelp.org/articles/essay-writer/ , I think this tutorial is a valuable resource for anyone who wants to expand their knowledge about GCP SQL Server instances, private IP configuration, and secure connection practices. Whether you are a novice, a power user, or even an essay writer working on technical topics, this tutorial promises to provide you with the necessary skills to effectively connect to GCP SQL Server instances using private IP configurations.

    ReplyDelete
  6. I liked your proposal. I also wanted to ask, did you play cricket? The game of cricket https://cricmod.com/latest-news/ also offers social benefits. It creates an opportunity to meet new people, build friendships, and form team solidarity.

    ReplyDelete