Thread: 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
littlemore 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=postgrespassword=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
cc:\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=%%HSET MM=%%FSET DD=%%G
)
FOR /F "TOKENS=5-6 DELIMS=: " %%F IN ('ECHO.^|TIME') DO (SET HR=%%FSET 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=%%iSET FTIME=%%~tiCALL :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
<div class="Section1"><p class="MsoPlainText"><font face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt">Hi,</span></font><p class="MsoPlainText"><font color="black" face="Courier New" size="2"><span lang="EN-GB"style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"><font color="black" face="CourierNew" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">If this is a known issue, please ignore…just direct me to the fix:</span></font><p class="MsoPlainText"><font color="black" face="Courier New" size="2"><spanlang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">Receiving an error “Column‘no’ does not exist” when trying to add a default value to a <u>new</u> column via properties panel. </span></font><pclass="MsoPlainText"><font color="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText"><font color="black" face="Courier New" size="2"><spanlang="EN-GB" style="font-size:10.0pt;color:black">When you create the new column without a default value, it’sfine. The default value can then be added afterwards. </span></font><p class="MsoPlainText"><font color="black" face="CourierNew" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"><fontcolor="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">Alsosubsequent deletion and re-creation of the column does not produce the same conditionand the default value can then be added with no further problems. </span></font><p class="MsoPlainText"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"><fontcolor="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">Theerror indicates the column “no” does not exist, but I am trying to add straight textto the default value. Can’t see why that field is trying to link/check columns. I have tried quotes.</span></font><pclass="MsoPlainText"><font color="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText"><font color="black" face="Courier New" size="2"><spanlang="EN-GB" style="font-size:10.0pt;color:black">Using Version 8.1 on Windows XP SP2 and pgadmin 1.4.1.</span></font><pclass="MsoPlainText"><font color="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText"><font color="black" face="Courier New" size="2"><spanlang="EN-GB" style="font-size:10.0pt;color:black">Thank you, kindly</span></font><p class="MsoPlainText"><fontcolor="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">L.</span></font><pclass="MsoPlainText"><font color="black" face="Courier New" size="2"><spanlang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"><img height="120" src="cid:image001.jpg@01C6280E.ADB08DC0"width="209" /></span></font></div>
<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy">Ok, This was me. When I looked at the SQL statement to trace problem, I (sheepishly)used the wrong quotes !</span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> Can the single quotes be added automatically if they are not supplied?</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal" style="margin-left:36.0pt"><font face="Tahoma" size="2"><spanstyle="font-size:10.0pt;font-family:Tahoma">-----Original Message-----<br /><b><span style="font-weight:bold">From:</span></b>pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org]<b><span style="font-weight:bold">On Behalf Of </span></b>Leon du Plessis<br/><b><span style="font-weight:bold">Sent:</span></b> </span></font><font face="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma">02February 2006</span></font><font face="Tahoma" size="2"><span style="font-size:10.0pt; font-family:Tahoma"> </span></font><font face="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma">03:38 PM</span></font><fontface="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma"><br /><b><span style="font-weight:bold">To:</span></b>pgadmin-support@postgresql.org<br /><b><span style="font-weight:bold">Subject:</span></b>[pgadmin-support] Error when trying to add DEFAULT Value to NEW column</span></font><pclass="MsoNormal" style="margin-left:36.0pt"><font face="Times New Roman" size="3"><span style="font-size:12.0pt"> </span></font><pclass="MsoPlainText" style="margin-left:36.0pt"><font face="Courier New" size="2"><spanlang="EN-GB" style="font-size:10.0pt">Hi, </span></font><p class="MsoPlainText" style="margin-left:36.0pt"><fontcolor="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText" style="margin-left:36.0pt"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">If this is a known issue,please ignore… just direct me to the fix:</span></font><p class="MsoPlainText" style="margin-left:36.0pt"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"style="margin-left:36.0pt"><font color="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">Receivingan error “Column ‘no’ does not exist” when trying to add a default value toa <u>new</u> column via properties panel. </span></font><p class="MsoPlainText" style="margin-left:36.0pt"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"style="margin-left:36.0pt"><font color="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">Whenyou create the new column without a default value, it’s fine. The default valuecan then be added afterwards. </span></font><p class="MsoPlainText" style="margin-left:36.0pt"><font color="black" face="CourierNew" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"style="margin-left:36.0pt"><font color="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">Alsosubsequent deletion and re-creation of the column does not produce the same conditionand the default value can then be added with no further problems. </span></font><p class="MsoPlainText" style="margin-left:36.0pt"><fontcolor="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText" style="margin-left:36.0pt"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">The error indicates thecolumn “no” does not exist, but I am trying to add straight text to the default value. Can’t see why that field is tryingto link/check columns. I have tried quotes.</span></font><p class="MsoPlainText" style="margin-left:36.0pt"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><p class="MsoPlainText"style="margin-left:36.0pt"><font color="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">UsingVersion 8.1 on Windows XP SP2 and pgadmin 1.4.1.</span></font><p class="MsoPlainText"style="margin-left:36.0pt"><font color="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText" style="margin-left:36.0pt"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black">Thank you, kindly</span></font><pclass="MsoPlainText" style="margin-left:36.0pt"><font color="black" face="Courier New" size="2"><spanlang="EN-GB" style="font-size:10.0pt;color:black">L.</span></font><p class="MsoPlainText" style="margin-left:36.0pt"><fontcolor="black" face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"> </span></font><pclass="MsoPlainText" style="margin-left:36.0pt"><font color="black"face="Courier New" size="2"><span lang="EN-GB" style="font-size:10.0pt;color:black"><img height="120" src="cid:image001.jpg@01C62818.8AE09CE0"width="209" /></span></font></div><br /><p><font size="2">--<br /> No virus foundin this incoming message.<br /> Checked by AVG Free Edition.<br /> Version: 7.1.375 / Virus Database: 267.14.23/243- Release Date: 2006/01/27<br /></font><p><font face="Arial" size="2"> </font>