Monday, July 11, 2011

Postgresql database backup and file transfer script in Windows XP

0 comments
I had to plan a backup system like that it would take a backup for postgresql databases running on Windows XP and then transfer the backup files in remote backup storage server which is running on linux.
I planned as the followings :-
  • dbbackup.conf - Parameter file
  • dbbackup.bat - This batch file is used to take the database backup
  • sftp.bat - This batch file is used send the backed up file in the remote linux server
  • scheduler.bat - This batch file calls the above two batch files
--- dbbackup.bat ---

@ECHO off
ECHO Database Backup Started ...
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==BACKUPDIR @SET BACKUPDIR=%%H
@ECHO %BACKUPDIR%

FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==POSTGRESQL_BIN @SET POSTGRESQL_BIN=%%H
@ECHO %POSTGRESQL_BIN%
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==PGDATA @SET PGDATA=%%H
@ECHO %PGDATA%
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==PGUSER @SET PGUSER=%%H
@ECHO %PGUSER%
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==PGPORT @SET PGPORT=%%H
@ECHO %PGPORT%
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==PGPASSWORD @SET PGPASSWORD=%%H
@ECHO %PGPASSWORD%

@SET LAST_DBBACKUP_FILE_EXT=%date:~4,2%.%date:~7,2%.%date:~10,4%_%time:~0,2%.%time:~3,2%.sqlc
@ECHO %LAST_DBBACKUP_FILE_EXT% > %BACKUPDIR%\last_dbbackup_file_ext
@%POSTGRESQL_BIN%\pg_dump -Fc dbname > %BACKUPDIR%\dbname_%LAST_DBBACKUP_FILE_EXT%
ECHO Database Backup Completed


--- sftp.bat ---

@ECHO off
ECHO File transfer started ...
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==BACKUPDIR @SET BACKUPDIR=%%H
@ECHO %BACKUPDIR%
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==WINSCPDIR @SET WINSCPDIR=%%H
@ECHO %WINSCPDIR%
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==SFTP_HOST @SET SFTP_HOST=%%H
@ECHO %SFTP_HOST%

FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==SFTP_USER @SET SFTP_USER=%%H
@ECHO %SFTP_USER%

FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==SFTP_PASSWORD_FILE @SET SFTP_PASSWORD_FILE=%%H
@ECHO %SFTP_PASSWORD_FILE%
FOR /F "tokens=1,2 delims==" %%G IN (dbbackup.conf) DO IF %%G==PATH_TO_COPY @SET PATH_TO_COPY=%%H
@ECHO %PATH_TO_COPY%

SET /p last_backup_time=< %BACKUPDIR%/last_dbbackup_file_ext
ECHO %last_backup_time%.sqlc
%WINSCPDIR%\winscp.com /command "option batch on" "option confirm off" "open %SFTP_USER%@%SFTP_HOST%" "put %BACKUPDIR%\dbname_%last_backup_time% %PATH_TO_COPY%" "exit"  < %SFTP_PASSWORD_FILE%

ECHO File Transfer Completed

--- dbbackup.conf ---

##This is configuration script for our database backup and file transfer.
##Do not use space before or after the equal sign between the parameter name and value.
##Do not chnage the sample copy, make copy of the file dbbackup.conf
##Backup related configuration
BACKUPDIR=C:\dbbackup
##Postgresql related configuration
POSTGRESQL_BIN="C:\Program Files\PostgreSQL\9.0\bin"
PGDATA="C:\Program Files\PostgreSQL\9.0\data"
PGUSER=postgres
PGPORT=5432
PGPASSWORD=postgres

##SFTP related configuration
WINSCPDIR="C:\Program Files\WinSCP"
#Remote server host
SFTP_HOST=10.0.0.58
#Remote server user
SFTP_USER=root
#Path of a file where unencypted password is given to connect to the remote server
SFTP_PASSWORD_FILE="C:\Documents and Settings\Vantage\My Documents\My Dropbox\consultancy\jim\password"
#Path to put the backup files in remote server
PATH_TO_COPY=/tmp/

---scheduler.bat----
@ECHO off
CALL dbbackup.bat
CALL sftp.bat
Continue reading →

Labels