FYI on the witting a batch script to run on Windows 2003 R2 server with
no install of postgresql or pgadmin III
This will create a new file every time the backup is run with the Name
then the date followed with the extension .backup
I would have added it to the postgresqldocs.org webstie but can't create
an account for my self. Tried following theses instructions
<http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows> but
never could get it to work
I hope these instructions help people...
Step One
Go to Server create a Directory called Drive:\PostgresqlBack
then create a sub directory called "bin" in the
Drive:\PostgresqlBack
Step Two
instead of compiling pg_dump.exe used the pgadmin install on the my
client and copied the files to the bin folder on the Server
the files required when using pg_dump.exe from PgAdmin install
are as follows and must be located in the bin folder
comerr32.dll
gssapi32.dll
k5sprt32.dll
krb_32.dll
libeay32.dll
libiconv2.dll
libpq.dll
Microsoft.VC80.CRT.manifest
msvcm80.dll
msvcp80.dll
msvcr80.dll
pg_dump.dll
ssleay32.dll
zlib1.dll
Step Three
Next Create batch file called something i used postgresqlBackup.bat.
The file must be located in PostgresqlBack directory not the bin folder.
Step Four
Open the File then Copy/Paste the following
@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set BACKUP_FILE=<NameOfTheFile>_%datestr%.backup
set logFile=EmproDatabase_%datestr%.log
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=<PassWord>
echo on
bin\pg_dump -i -h <HostName> -p 5432 -U <UserName> -F c -b -v -f
%BACKUP_FILE% <DATABASENAME>
Step Five
Change <NameOfTheFile> to something. i choose to use the name the
name of the database. (make sure the is no spaces after the word
BACKUP_FILE any spaces will cause this setting not to work.) Setting is
the first part of the file name then followed by the date the file was
created with the extension .backup
Change the <PassWord > setting above to the correct password for
the backup users. (make sure the is no spaces after the word
PGPASSWORD any spaces will cause this setting not to work.)
Note severial tries to get PGPASS
<http://www.postgresql.org/docs/8.3/static/libpq-pgpass.html> file to
work on windows never could so decided to use PGPASSWORD
<http://www.postgresql.org/docs/8.3/static/libpq-envars.html> setting in
the script
Change <HostName> either to ip address or dns name of the server
hosting Postgresql.
Change <UserName> to backup user make sure this users has access to
database for backup purposes
Change <DATABASENAME> to the database name being backed up.
Save the File
Step Six
Create Task for th Task Scheduler link to MS instructions
<http://technet.microsoft.com/en-us/library/cc758861.aspx>
Step Seven
Once you have chosen the security context the Task in going to run
in, it is advised to change the directory security where the back is in
run in and the files are stored so only the backup users can get to it
sense it has the high level user name and password stored in plain text.
Step Eight
Go drink beer and something salty. :-)
