Purging job schedules for hypertables

In order to purge old data, the pg_cron job scheduler is used to invoke a stored procedure at a preconfigured time to check for and purge old data. The preconfigured time is 00:10 hours (ten minutes after midnight) and can be modified if required. The job can be configured in the on-prem PostgreSQL environment and also in the Azure cloud environment. We are using the Azure Flexible Server for PostgreSQL in the Azure cloud.

Important! These procedures should be performed after database creation (after the database migrator execution) and should be executed under the parent database (Postgres).

Scheduling a Run

Run the following script to schedule a purge while connected to the Postgres database.

CREATE OR REPLACE PROCEDURE public.schedule_purge_job (    
    db_name character varying,
    is_onprem boolean   
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    job_id integer;
    matching_jobs integer;
    existing_cron_expr character varying;
    cron_expr character varying;
begin
    select count(1) into matching_jobs
    from cron.job 
    where database = db_name and command = 'CALL public.drop_old_chunks()'; 
    
    cron_expr = '10 0 * * *';
    
    if (matching_jobs = 0) then
       RAISE NOTICE 'No matching jobs exist. Proceeding to schedule cron job using cron % for database %.', cron_expr, db_name;
       SELECT cron.schedule(cron_expr, $$CALL public.drop_old_chunks()$$) into job_id;
       PERFORM cron.alter_job(job_id:=job_id, database:=db_name);
       if(is_onprem) then
           update cron.job set nodename = '';
       end if;
       RAISE NOTICE 'Job created. Job Id %.', job_id;
    else
        select schedule into existing_cron_expr
        from cron.job
        where database = db_name and command = 'CALL public.drop_old_chunks()'; 
        
        RAISE NOTICE 'Cron job already exists with the schedule %. Use modify_purge_job procedure to alter the job schedule.', existing_cron_expr;
    end if;  
end; 
$BODY$;

After this script is applied to the Postgres database, it can be invoked to schedule a purge job using the following syntax:

CALL public.schedule_purge_job(<DB Name>, <Is Onprem Env>);

Where <DB Name> is the name of the target database. The value for <Is Onprem Env> is true for an on-premise database and false for a cloud database. This configures the purge job to run at 00:10 on a daily basis for the specified database. For example:

• CALL public.schedule_purge_job('ims', true);

  This call schedules the purge job at 00:10 on a daily basis on the ims database in an on-prem environment.

• CALL public.schedule_purge_job('readings', true);

  This call schedules the purge job at 00:10 on a daily basis on the readings database in an on-prem environment.

• CALL public.schedule_purge_job('ims', false);

  This call schedules the purge job at 00:10 on a daily basis on the ims database in a cloud environment.

• CALL public.schedule_purge_job('readings', false);

  This call schedules the purge job at 00:10 on a daily basis on the readings database in a cloud environment.

Modifying a Schedule

Run the following script to modify a scheduled purge while connected to the Postgres database.

CREATE OR REPLACE PROCEDURE public.modify_purge_job(
    cron_expr character varying, 
    db_name character varying,
    is_onprem boolean    
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    job_id integer;
    existing_job_id integer;
    matching_jobs integer;
    existing_cron_expr character varying;
begin
    select count(1) into matching_jobs
    from cron.job 
    where database = db_name and command = 'CALL public.drop_old_chunks()';    
    
    if (matching_jobs = 0) then
       RAISE NOTICE 'No matching jobs exist. Use schedule_purge_job procedure to schedule a new job schedule.';       
    else
        select schedule, jobid into existing_cron_expr, existing_job_id
        from cron.job
        where database = db_name and command = 'CALL public.drop_old_chunks()'; 
        
        if (existing_cron_expr = cron_expr) then
           RAISE NOTICE 'The provided cron expression is same as the currently configured cron expression. No action taken';       
        else
           RAISE NOTICE 'Cron job exists with the schedule %. Proceeding to modify its schedule', existing_cron_expr;

           PERFORM cron.unschedule(existing_job_id);
           SELECT cron.schedule(cron_expr, $$CALL public.drop_old_chunks()$$) into job_id;
           PERFORM cron.alter_job(job_id:=job_id, database:=db_name);
           if(is_onprem) then
               update cron.job set nodename = '';
           end if;
           RAISE NOTICE 'Job created. Job Id %.', job_id;
        end if;
        
       
    end if;  
end; 
$BODY$;

After this script is applied to the Postgres database, it can be invoked to modify a scheduled purge job using the following syntax:

CALL public.modify_purge_job(<Cron schedule>, <DB Name>, <Is Onprem Env>);

Where <Cron schedule> is the modified schedule and <DB Name> is the name of the target database. The value for <Is Onprem Env> is true for an on-premise database and false for a cloud database. For example:

• CALL public.modify_purge_job('11 0 * * *', 'ims', true);

  This call modifies the purge job to be scheduled at 00:11 on a daily basis on the ims database in an on-prem environment.

• CALL public.modify_purge_job('11 0 * * *', 'readings', true);

  This call modifies the purge job to be scheduled at 00:11 on a daily basis on the readings database in an on-prem environment.

• CALL public.modify_purge_job('11 0 * * *', 'ims', false);

  This call modifies the purge job to be scheduled at 00:11 on a daily basis on the ims database in a cloud environment.

• CALL public.modify_purge_job('11 0 * * *', 'readings', false);

  This call modifies the purge job to be scheduled at 00:11 on a daily basis on the readings database in a cloud environment.

Other Queries

To view all cron job schedules:

select * from cron.job;

To unschedule a run:

SELECT cron.unschedule(14);

To see all runs:

select * from cron.job_run_details;