How To Export Table Data Backup Using pgAgent Jobs Scheduler In PostgreS...

DO $$

DECLARE

    jid integer;

    scid integer;

BEGIN

-- Creating a new job

INSERT INTO pgagent.pga_job(

    jobjclid, jobname, jobdesc, jobhostagent, jobenabled

) VALUES (

    3::integer, 'Scheduler1'::text, ''::text, ''::text, true

) RETURNING jobid INTO jid;

-- Steps

-- Inserting a step (jobid: NULL)

INSERT INTO pgagent.pga_jobstep (

    jstjobid, jstname, jstenabled, jstkind,

    jstconnstr, jstdbname, jstonerror,

    jstcode, jstdesc

) VALUES (

    jid, 'Step1'::text, true, 's'::character(1),

    ''::text, 'postgres'::name, 'f'::character(1),

    E'copy (select * from public.jobs) to ''D:\\DataExports\\jobs_export.csv'' DELIMITER '','' HEADER;'::text, ''::text

) ;

-- Schedules

-- Inserting a schedule

INSERT INTO pgagent.pga_schedule(

    jscjobid, jscname, jscdesc, jscenabled,

    jscstart,     jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths

) VALUES (

    jid, 'Scheduler1'::text, ''::text, true,

    '2024-03-25 22:41:00+05:30'::timestamp with time zone, 

    -- Minutes

    '{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}'::bool[]::boolean[],

    -- Hours

    '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Week days

    '{f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Month days

    '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Months

    '{f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[]

) RETURNING jscid INTO scid;

END

$$;