ࡱ> >@=%` 0bjbj"x"x 8&@@ $^g& $ hr!ZE"g+]]]]]]:,M %PxHO .0^R!R!M!M];"^t Dt  Auto Scheduled Backup in PostgreSQL on WINDOWS Using Windows Scheduled Tasks  First of all I want to explain the reason to select Windows scheduler instead of PostgreSQL pgAgent (Auto Job Scheduler of PostgreSQL): In my practice, I found the GUI tool(s) of PostgreSQL including pgAdmin III is inefficient in performance. Some times it works against the expectation without any reason and does the same job correctly after reattempting with an interval of 2 to 3 hours. For example the backup through pgAdmin III returns 0 to confirm the successful completion of backup but the file size of the backup file clearly shows that there is something wrong. I find the size of 1 kb of the file which previously showed more than 10,000 kb and got the expected size when reattempted after 3 hours. While on the other hand the COMMAND PROMPT never gives the unexpected result and always gives the accurate reason for any error(s) and warning(s) that may occur. Second important reason is that the GUI tool(s) hides the technical flow of the process which makes the work more smart and optimized for the developer. Any one may disagree with me. But its my practice. Auto Scheduled Backup Steps are as follows: Create a .bat file as shown (You may optimize for your system by using common sense): @echo off set dumptime=%date:~0,2%NOV08_%time:~0,2%%time:~3,2% set BACKUP_FILE=%dumptime% Comp.backup pg_dump -i -h 10.183.78.13 -p 5432 -U cbas -F c -D -v -f "D:\ICT\Backup\Nov-08\_13\%BACKUP_FILE%" cbas set BACKUP_FILE=%dumptime% Plain.backup pg_dump -i -h 10.183.78.13 -p 5432 -U cbas -F p -D -v -f "D:\ICT\Backup\Nov-08\_13\%BACKUP_FILE%" cbas xcopy "D:\ICT\Backup\Nov-08\_13" "\\10.183.78.13\d$\cBas-DB-Backup\Nov-08" /D set BACKUP_FILE=%dumptime% Comp.backup pg_dump -i -h 10.183.78.19 -p 5432 -U cbas -F c -D -v -f "D:\ICT\Backup\Nov-08\_19\%BACKUP_FILE%" cbas set BACKUP_FILE=%dumptime% Plain.backup pg_dump -i -h 10.183.78.19 -p 5432 -U cbas -F p -D -v -f "D:\ICT\Backup\Nov-08\_19\%BACKUP_FILE%" cbas xcopy "D:\ICT\Backup\Nov-08\_19" "\\10.183.78.19\e$\cBas-Backup\Nov-08" /D xcopy "D:\ICT\Backup\Nov-08" "\\10.183.78.28\cba\Database\Backup\Postgres\Nov-08" /D /S Create Scheduled Tasks of windows (XP) as shown: Click Start -( Settings -( Control Panel ( Press ENTER key Switch to Classic View (if you are at Category View) and double click Scheduled Tasks icon. Double Click Add Scheduled Task icon then click Next And select Command Prompt from the list then click Next and give suitable name e.g. pgsd (Postgres Scheduled Dump). Click the desired radio button and click Next leave boxes to default values and click Next Enter the password and click Next and place the check on the box to open advanced properties before clicking the Finish. Write C:\WINNT\system32\cmd.exe /c "D:\Program Files\PostgreSQL\8.2\bin\pgsd.bat" in Run: window and C:\WINNT\system32 in Start in: window and click ok. Scheduler is ready now. Its time to enjoy the auto back up and get more time to do other activities instead of backup. The detailed description of the .bat file is available on request. But a professional may optimize it accordingly. Abdul Rehman  HYPERLINK "mailto:abr_ora@yahoo.com" abr_ora@yahoo.com  ./45MNOP% (  Z } zpp`SFSh<7S5CJOJQJ\hV5CJOJQJ\h<7Sh<7S5CJOJQJ\h<7SCJOJQJhCJOJQJhCJOJQJhrCJOJQJhl9PCJOJQJhx].CJOJQJhA!ACJOJQJhCJOJQJh9CJOJQJ)jh9CJOJQJUmHnHu!h<&.h59OJQJ\aJh59OJQJ\aJ/MOP  Z [    U V } $^a$gd" $ & Fa$gd)e $ & Fa$gd K$a$gd K$a$gd K     ;J^q#'ʼ~qgqZgqgqgqhJh[3QCJOJQJhJCJOJQJhJ5CJOJQJ\hJh[3Q5CJOJQJ\ jh[3Qh[3QCJOJQJh[3QCJOJQJh}[CJOJQJhiCJOJQJ *h"h"CJOJQJ *h"CJOJQJh<7SCJOJQJh)eCJOJQJh\U5CJOJQJ\h\UCJOJQJ#} stQy,-QU $ & Fa$gdJ $ & Fa$gd[3Q $^a$gd}[ $ & Fa$gd\U $^a$gd"'PTrw!"5FGehijkŸߨߛwmcmYOhZ!CJOJQJh]( CJOJQJhsMvCJOJQJh}[CJOJQJhCJOJQJhjhjCJOJQJhjhJCJOJQJhg5CJOJQJ\hghg5CJOJQJ\hj5CJOJQJ\hghj5CJOJQJ\hjCJOJQJhgCJOJQJhJ5CJOJQJ\hJCJOJQJ!"ikYZ[\$a$gd K $^a$gd $h^ha$gd]( $ & Fa$gd\U $^a$gd}[ $ & Fa$gd[3Q $8^8a$gdj$^`a$gdj $^a$gdj XYij̹wfWh<7Sh<7SCJOJQJaJ jhPh<CJUaJhCJOJQJaJ hPOh0JCJOJQJaJ+jhPOhCJOJQJUaJhPOhCJOJQJaJ%jhPOhCJOJQJUaJh59CJOJQJ\aJh\UhZ!CJOJQJhZ!CJOJQJhZ!5CJOJQJ\ 6&P 1h:pEy. A!"#$% DyK abr_ora@yahoo.comyK 2mailto:abr_ora@yahoo.comDd\  C *ABD21298_"@@bԝwJ@h nԝwJ@hPNG  IHDR OPLTEf+tRNS0J cmPPJCmp0712Om0IDATcT` 0H)0hF(P0H' b] $IENDB`@@@ NormalCJ_HaJmH sH tH DA@D Default Paragraph FontRi@R  Table Normal4 l4a (k@(No List6U`6  Hyperlink >*B*ph &/MOPZ[ UV} stQy,-Q U ! " i k Y Z [ \ 0000 000000000000 00P00000000000000000000 00 0 0 0 0 0 00000 00 00 000000/MOPZ[ UV} stQy,-Q U ! 0000 000000000000 0000000000000000000000 00 0K 0K 0K 0K 0KZ0-0 @0 0K@0 00C ' }   i X8@(  HB  C DB S  ?M (t_PictureBullets  $,goq|}  26nrtyLPbjlxy-2 \ a  #VYtyQT-2r v " $ [ 3333333333%( " k X Y \ a |IN>'BDJhh^h`B*OJQJo(phhH^`OJQJ^Jo(hHopp^p`OJQJo(hH@ @ ^@ `OJQJo(hH^`OJQJ^Jo(hHo^`OJQJo(hH^`OJQJo(hH^`OJQJ^Jo(hHoPP^P`OJQJo(hHh ^`hH.h L^`LhH.h pLp^p`LhH.h @ @ ^@ `hH.h ^`hH.h L^`LhH.h ^`hH.h ^`hH.h PLP^P`LhH.|I'BDƬb                 ](  ="x].A!Al9P[3Q<7S\UV}[sMv8f~Z!Jr K# igjV)eEy@\ \ ~\ \  p@UnknownGz Times New Roman5Symbol3& z Arial7&  Verdana;WingdingsABook Antiqua?5 z Courier New"1hs˦&   !94  2QHX?2 Abdul Rehman Abdul Rehman  Oh+'0d   , 8DLT\Abdul RehmanNormalAbdul Rehman32Microsoft Office Word@P@r'F@F0xH ՜.+,D՜.+,< hp  BearingPoint, Inc '  Title 8@ _PID_HLINKSAl mailto:abr_ora@yahoo.com   !"#$%&'()*+,./012346789:;<?Root Entry F:PxHAData 1Table!WordDocument8&SummaryInformation(-DocumentSummaryInformation85CompObjq  FMicrosoft Office Word Document MSWordDocWord.Document.89q