pgAgent job gives no signs of life

Question

On Windows7 professional (a 64bit Hyper-V virtual machine), Service Pack 1, I'm running PostgreSQL 10, with pgAdmin4 and pgAgent installed.
I successfully created a new database, NewDB, the owner is NewUser, with password NewPassd and a new schema NewSchema (the names are made up for this post).
Inside NewSchema, I created the table NewTable and I can easily import a CSV file, New.csv, from the query tool

COPY "NewSchema"."NewTable" FROM 'PathToFile\New.csv' DELIMITER ';' CSV HEADER ENCODING 'WIN1256';

Then, I created a pgAgent Job, and in the following is the CREATE Script

DO $$
DECLARE
    jid integer;
    scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
    jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
    2::integer, 'importNewTable'::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, 'importNewTableStep'::text, true, 's'::character(1),
    ''::text, 'NewDB'::name, 'f'::character(1),
    'BEGIN
 TRUNCATE "NewSchema"."NewTable";
 COPY "NewSchema"."NewTable" FROM ''PathToFile\New.csv'' DELIMITER '';'' CSV HEADER ENCODING ''WIN1256'';
END;'::text, ''::text
) ;

-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
    jscjobid, jscname, jscdesc, jscenabled,
    jscstart, jscend,    jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
    jid, 'importNewTableSch'::text, ''::text, true,
    '2018-03-13 23:37:48+01'::timestamp with time zone, '2091-08-20 23:13:26+02'::timestamp with time zone,
    -- Minutes
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
    -- Hours
    ARRAY[false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false]::boolean[],
    -- Week days
    ARRAY[false, true, true, true, true, true, false]::boolean[],
    -- Month days
    ARRAY[false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false]::boolean[],
    -- Months
    ARRAY[false, false, false, false, false, false, false, false, false, false, false, false]::boolean[]
) RETURNING jscid INTO scid;
END
$$;

The pgpass.conf is in the folder ~\postgresql and is the following:

localhost:5432:postgres:postgres:***
localhost:5432:postgres:postgres:***
localhost:5432:NewDB:NewUser:NewPassd

I can't get the job to work. I'm not even able to retrieve a log with the error description. Whenever, from PgAdmin4, I run the job, nothing happens.
Elsewhere on the internet, I could only find posts that dealt with connection issues. Here I don't have any clue about what my issue is. Also, the suggestions from other posts in Stack Overflow were not helpful to my case.
In an attempt to find a way out, I created in Windows the user NewUser and copied the pgpass.conf in ~\NewUser: it was useless.
I'm looking for directions about any error I may have created or directions on how to test my job.

Thank you. Nicolò Antonietti

Have you checked `pgadmin4.log` for any errors? Ref: https://www.pgadmin.org/faq/#8 — Murtuza Z, Mar 16 '18 at 09:10
Hello timiTao and thank you for your reply. Unfortunately, when I run the job from the pgAdmin4 console, no information is added to the log file. Regards. — user2717377, Mar 18 '18 at 04:31
@user2717377 have you found a solution? I'd argue that pgAgent isn't running at all, and you can see only its tables. In a unix machine you would start it like this `pgagent_10 -f "host=server port=5432 dbname=newdb user=newuser"` — Jim Jones, Mar 28 '18 at 13:25

pgAgent job gives no signs of life

0 Answers0