running pg_dump via pgAgent

Hi Dave,
Yes, it was the password prompt for pg_dump, Thank you! using pgAgent to backup is working PERFECTLY now. I'll post a little more information in case anyone else needs to do this:

installed pgAgent:
C:\Program Files\pgAdmin III\1.4>pgAgent INSTALL pgAgent -l 2 -u postgres -p PASS hostaddr=127.0.0.1 dbname=postgres user=postgres password=PASS

ran pgagent.sql, started the service...

created pgpass.conf with the line "*:*:*:*:PASS" in
D:\Data\Documents and Settings\postgres\Application Data\postgresql\

Created a job with a schedule once a day and a step called "dump me" that calls the batch file "C:\TEMP2\pg_backup.bat c c:\temp 64800 postgres test"

This script does the following:

1. Backs up the database to a specified target directory using pg_dump
2. Archives old backups using the current date as the name of the file using the format: C:\path\dump_[dbname]_[date].backup
2. Deletes all files in the target directory older than a specified number of days

Here is what the parameters do:

1. Path to pg_backup.bat
2. Drive where the backup file is located
3. Path to the backup file
4. Number of minutes the backup file should be archived
5. The username used to access the database
6. The name of the database that needs to be backed up

pg_backup.bat looks like this:

:: START pg_backup.bat -------------

@echo off

::parameters:

set backupdrive=%1
set backuppath=%2
set deleteminutes=%3
set pguser=%4
set pgdb=%5

if (%backupdrive%)==() goto :EXIT
if (%backuppath%)==() goto :EXIT
if (%deleteminutes%)==() goto :EXIT
if (%pguser%)==() goto :EXIT
if (%pgdb%)==() goto :EXIT

@echo on
%backupdrive%:
CD %backuppath%
@echo off

:: Backup the database

echo. | date | FIND "(mm" > NUL
if errorlevel 1 (call :Parsedate DD MM) else (call :Parsedate MM DD)

::echo YYYY=%YYYY% MM=%MM% DD=%DD% Dow=%Dow%

goto :EOF

:Parsedate ----------------------------------------------------------
for /f "tokens=1-4 delims=/.- " %%A in ('date /t') do (
set Dow=%%B%%C%%D.backup)

@echo on
pg_dump -F t -b -c -C -f %backuppath%\dump_%pgdb%_%Dow% -U %pguser% %pgdb%

:: ---------------DELETE OLDER THAN deleteminutes-----------------
@ECHO OFF
:: ------------
:: This is where you set the number
:: of minutes you want subtracted
:: from the current date/time.
:: ------------
SET MyMins=%deleteminutes%

:: ------------
:: Get current date/time
:: ------------
FOR /F "TOKENS=2-4 DELIMS=/ " %%F IN ('DATE /T') DO (
SET YYYY=%%H
SET MM=%%F
SET DD=%%G
)
FOR /F "TOKENS=5-6 DELIMS=: " %%F IN ('ECHO.^|TIME') DO (
SET HR=%%F
SET MN=%%G
)

IF %DD% LSS 10 (SET DD=%DD:~1%)
IF %MM% LSS 10 (SET MM=%MM:~1%)
IF %HR% LSS 10 (SET HR=%HR:~1%)
IF %MN% LSS 10 (SET MN=%MN:~1%)

: ------------
:: Subtract minutes from current time.
:: ------------
SET /A MN=%MN% - %MyMins%

:: ------------
:: Do the massively painful
:: reverse calculations.. :(
:: ------------
:LoopMins
IF /I %MN% GEQ 0 (GOTO LoopHrs)
SET /A MN=%MN% + 60
SET /A HR=%HR% - 1
GOTO LoopMins
:LoopHrs
IF /I %HR% GTR 0 (GOTO LoopDate)
SET /A HR=%HR% + 23
SET /A DD=%DD% - 1
GOTO LoopHrs
:LoopDate
IF /I %DD% GTR 0 (GOTO DONE)
set /A mm=%mm% - 1
if /I %mm% GTR 0 goto ADJUSTDAY
set /A mm=12
set /A yyyy=%yyyy% - 1
:ADJUSTDAY
if %mm%==1 goto SET31
if %mm%==2 goto LEAPCHK
if %mm%==3 goto SET31
if %mm%==4 goto SET30
if %mm%==5 goto SET31
if %mm%==6 goto SET30
if %mm%==7 goto SET31
if %mm%==8 goto SET31
if %mm%==9 goto SET30
if %mm%==10 goto SET31
if %mm%==11 goto SET30
if %mm%==12 goto SET31
goto ERROR
:SET31
set /A dd=31 + %dd%
goto LoopDate
:SET30
set /A dd=30 + %dd%
goto LoopDate
:LEAPCHK
set /A tt=%yyyy% %% 4
if not %tt%==0 goto SET28
set /A tt=%yyyy% %% 100
if not %tt%==0 goto SET29
set /A tt=%yyyy% %% 400
if %tt%==0 goto SET29
:SET28
set /A dd=28 + %dd%
goto LoopDate
:SET29
set /A dd=29 + %dd%
goto LoopDate
:DONE
IF %dd% LSS 10 set dd=0%dd%
IF %mm% LSS 10 set mm=0%mm%
IF %HR% LSS 10 SET HR=0%HR%
IF %MN% LSS 10 SET MN=0%MN%

for %%i in (*.*) do (
set FileName=%%i
SET FTIME=%%~ti
CALL :PROCESSFILE
)

set mm=
set yyyy=
set dd=
set thedate=
goto :EOF

:PROCESSFILE
set fyyyy=%FTIME:~6,4%
set fmm=%FTIME:~0,2%
set fdd=%FTIME:~3,2%
SET fhr=%FTIME:~11,2%
SET fmn=%FTIME:~14,2%

if /I %fyyyy% GTR 2069 set fyyyy=19%FTIME:~6,2%

:: --------
:: Deal with File times
:: reported as AM/PM vs.
:: calculated times as 24hr.
:: --------
IF /I "%FTIME:~17,1%" == "P" (
IF %fhr% LSS 10 (
SET /A fhr=%fhr:~1,1% + 12
) ELSE (
IF %fhr% LEQ 11 (
SET /A fhr=%fhr% + 12
)
)
)
IF /I "%FTIME:~17,1%" == "A" (
IF %fhr%==12 (
SET fhr=00
)
)

:: +*************************************+
:: | This is where the files are deleted |
:: | Change the ECHO command to DEL to |
:: | delete. ECHO is used for test. |
:: +*************************************+
if /I %yyyy%%mm%%dd% GEQ %fyyyy%%fmm%%fdd% (
IF /I 1%hr%%mn% GEQ 1%fhr%%fmn% (

ECHO %FileName%

)
)
ECHO Calcdate=%yyyy%%mm%%dd%%hr%%mn%

set temp=
set fyyyy=
set fmm=
set fdd=

:EXIT
ECHO Exit
:: END pg_backup.bat -------------

that's it
thanks again Dave

> Hi,

Hi,

> I am trying to use pgAgent to automatically backup my database
> once a day. Wouldn't this be the most common use of pgAgent?

It's not one I considered when I finished writing it, but it does seem
perfectly reasonable.

> I have successfully installed pgAgent, and it runs jobs ok.
> However, I'm running into problems when I set up a job to run
> a "batch" step with the following definition:
>
> pg_dump -f c:tempdump.dat -U postgres test
>
> It starts the job, and the statistics for the step shows it is running
(I can also see in my
> task manager that pg_dump is running) ... but it never stops!

Hm, I've just tried it here and it worked perfectly. The only real
difference that I can see was that I used the full path to pg_dump:

"C:Program FilesPostgreSQL8.1binpg_dump" -f c:tempdump.dat -U
postgres test

Please check that libpq.dll and whatever dependencies the Pervasive
build of it may have are in the same directory as pg_dump, and please
try specifying the full path to pg_dump. Also, please make sure that the
Windows postgres user has write permissions on the c:temp directory.

Another thought that just crossed my mind - perhaps pg_dump is prompting
for a password (which pgAgent will never enter of course). Please check
that your postgres account has a pgpass.conf file configured to allow
access to the test database, or that pg_hba.conf has an appropriate
trust entry.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

_______________________________________________
No banners. No pop-ups. No kidding.
Make My Way your home on the Web - http://www.myway.com