-1

I am pretty new to postgresql and not able to understand what the following query does, so want help of some communuty memebers who can explain me this to me.

DO $$
DECLARE
    jid integer;
    scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
    jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
    1::integer, 'backup'::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, 'backup'::text, true, 'b'::character(1),
    ''::text, ''::name, 'f'::character(1),
    'set PGPASSWORD=password
pg_dump -h "localhost" -U "postgres" -f "D:\backup\backuppg_%date:~-4%_%date:~3,2%_%date:~0,2%__%time:~0,2%_%time:~3,2%_%time:~6,2%" "postgres"'::text, ''::text
) ;
Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Rahul Mistry
  • 1
  • 3
  • 3
    Which parts of it do you understand? And which parts are you struggling with? If your answer to the second qustion is "all of it", then it may be rather difficult to help you this way and you would be better off studying on your own for a bit. –  Jul 17 '21 at 14:11
  • The part above comment “Creating a new job” and the part under the same comment. – Rahul Mistry Jul 17 '21 at 15:52

1 Answers1

0

for backup in PostgreSQL you use pg_dump

pg_dump -h "localhost" -U "postgres" -f "D:\backup\backuppg_%date:~-4%%date:~3,2%%date:~0,2%__%time:~0,2%%time:~3,2%%time:~6,2%" "postgres"'::text, ''::text ) ;

in the above backup command, you are using psql with options which are explained below

-h --host     --> Specifies the hostname of the machine
-U --username --> The user which is used for login 
-f --file     --> This option is used to send output to the specified file
post that "postgres" is the database which is being backed up

now D:\backup\backuppg_%date:~-4%_%date:~3,2%_%date:~0,2%__%time:~0,2%_%time:~3,2%_%time:~6,2% this will create a file named backuppg_<date>_<time> in directory D:\backup

rest all are INSERT commands are appending information about backup in table named pgagent.pga_job and pgagent.pga_jobstep

Nikhil B
  • 353
  • 2
  • 7