1

I'm new to pgAgent, and trying to set up a job that will export a file every 5 minutes (just for testing; once I have the syntax down it'll be dispersed to numerous jobs). The below test code works fine when I run it in a query editor, however pgAgent doesn't seem to like it. Any help on what I need to change?

DO $$
DECLARE ssql varchar := 'COPY (SELECT * FROM public.users LIMIT 10) TO ''C:\Program Files\PostgreSQL\11\pgExport' || TO_CHAR(NOW(),'YYYYMMDDHH24MI') || '.csv'' WITH CSV DELIMITER '','' HEADER;';

BEGIN
    EXECUTE ssql;
END; $$

I can't find any sort of error as far as what's happening; rather the file just doesn't appear when I run the job manually nor when I let the schedule run.

Jake
  • 893
  • 2
  • 9
  • 17

1 Answers1

2

So it turned out to not be a syntax issue, but rather a permissions issue. Found the answer here (couldn't get a connection to the database - Postgres Job Scheduling issued (Windows based machine)) if anyone is interested - basically have to set job as a "remote" server (even if running locally) and supply the connection string with localhost.

Jake
  • 893
  • 2
  • 9
  • 17