Search code examples
postgresqlbatch-filejob-schedulingpgagent

pgAgent scheduled job failed on Windows

I am trying to set up the step with Batch file path on particular time in pgAgent via pgAdmin. But when I run that it is failing and in Step statistics I got this Output

C:\Windows\system32>C:\postgresql\run.bat 'psql' is not recognized as an internal or external command, operable program or batch file.

Details:

Postgresql 9.3.5 on local system account (Current User)
pgAdmin 1.18.1
pgAgent via stack builder with Administrator account (Current User)

in run.bat I have only two statement

@echo off
psql -h localhost -p 5433 -U postgres -d test -a -f "test.sql"

I have psql in system path variable and able to access it in cmd. When I run that bat file manually it is executing without fail. But when I given the batch file path (C:\postgresql\run.bat) in pgAgent jobs it is giving that error in statistics.

Is there anything wrong in my configuration? Why it is always going to that C:\Windows\system32>?

Edit:

My run.bat file

 @ECHO OFF

SET LBSDatabaseName=Test
SET dbHost=localhost 
SET dbPort=5434 
SET dbUser=postgres 
SET logFile=DbInstall.log 
SET sqlFolder="D:\SOURCECODE\archivescripts"        

"C:\Program Files (x86)\PostgreSQL\9.3\bin\psql.exe" -h "%dbHost%" -p "%dbPort%" -d "%LBSDatabaseName%" -U "%dbUser%" -L "%logFile%" -q -f "%sqlFolder%\Archive.sql"

My Archive.sql

update "Archive".emp set "FirstName"='Srikanth Dyapa';
Solution

  • For example,

    D:\pgAgent_jobs

    is the path where psql located.

    D:\pgAgent_jobs\scripts\test.sql

    is the path in which my test.sql placed.

    D:\pgAgent_jobs\scripts\psqlss.bat

    is my bat file to execute test.sql

    so my bat file will be like below

     @echo off

cd /D D:\\pgAgent_jobs

psql -h localhost -p 5432 -U postgres -d db_name -a -f "D:\pgAgent_jobs\scripts\test.sql"

    Note : my pg_hba.conf is configured with trust for all hosts that's why am not passing any password in the above psql command