Windows Postgresql Auto Backup_Restore from Production To Pre-Production

Copy the below script’s in notepad and save as Batch file.

Backup_Restore.bat script as follows;

———————————————————————————————————————————————————————————————————————

@echo off

set BackupName=pothys_restore
set ProductionDB=pothysdev
set ProductionServerIP=192.168.0.231
set PreProductionDB=pothystest
set ProductionDBAdminPwd=postgres
set PreProductionDBAdminPwd=postgres
set AdempierePwd=123456

cd\
cd C:\Program Files\PostgreSQL\8.4\bin\
SET PGPASSWORD=%ProductionDBAdminPwd%

echo ========================
echo Production Server Backup
echo ========================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

pg_dump.exe –host %ProductionServerIP% –port 5432 –username postgres –format plain –no-owner –verbose –file “C:\%BackupName%.sql” %ProductionDB%

echo ===========================
echo Deleting The DB In Pre-Prod
echo ===========================

SET /P ANSWER=Do you want to continue (Y/N)?
echo You chose: %ANSWER%
if /i {%ANSWER%}=={y} (goto :yes)
if /i {%ANSWER%}=={yes} (goto :yes)
goto :no

:no
echo You pressed no!
exit /b 1

:yes
echo You pressed yes!
rem exit /b 0
echo =========================
echo Terminating Connections..
echo =========================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

SET PGPASSWORD=%PreProductionDBAdminPwd%
psql -U postgres -c “SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE pg_stat_activity.datname = ‘%PreProductionDB%’ AND procpid <> pg_backend_pid();”
psql -U postgres -c “DROP DATABASE %PreProductionDB%;”

echo ===================
echo DB Deletion Done !!
echo ===================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

echo ===========================
echo Creating New DB In Pre-Prod
echo ===========================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

psql -U postgres -c “CREATE DATABASE %PreProductionDB% WITH ENCODING=’UTF8′ OWNER=adempiere CONNECTION LIMIT=-1;”
psql -U postgres -c “\q”

echo ==================
echo DB restore started
echo ==================

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

SET PGPASSWORD=%AdempierePwd%
psql -f c:\%BackupName%.sql -d %PreProductionDB% -U adempiere -h localhost

@ping 127.0.0.1 -n 2 -w 1000 > nul
@ping 127.0.0.1 -n %1% -w 1000> nul

echo ========================
echo DB Successfully Restored
echo ========================

——————————————————————————————————————————————————————————————————————–

DB_Restore.bat script as follows;

——————————————————————————————————————————————————————————————————————–

@echo off
Backup_Restore.bat 2>&1 | wtee -a DB_Restore_log.log
For /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)
For /f “tokens=1-3 delims=/:/ ” %%a in (‘time /t’) do (set mytime=%%a-%%b-%%c)
move C:\DB_Restore_log.log C:\DB_Restore_log-%mydate%_%mytime%.log

———————————————————————————————————————————————————————————————————————-

Use the below link to download the ‘wtee.exe’

http://code.google.com/p/wintee/downloads/detail?name=wtee.exe&can=2&q=

 

Step 1:

Copy the two batch files(Backup_Restore.bat & DB_Restore.bat) and one executable file(wtee.exe) in c: drive.

Step 2:

Open the Backup_Restore.bat file and edit the following variables according to your environment.

• set BackupName=pothys_restore

Here replace the ‘pothys_restore’ with the name that the backup file should be saved.

(Backup file name eg: pothys_restore.sql)

• set ProductionDB=pothysdev

Here replace the ‘pothysdev’ with the name of production database to be backup.

(DB name to be backup from production server eg: pothys)

• set ProductionServerIP=192.168.0.231

Here replace the ‘192.168.0.231’ with the ip of production server.

(IP Address of production server to take backup eg:192.168.3.1)

• set PreProductionDB=pothystest

Here replace the ‘pothystest’ with the name of pre-production database to be restore

(DB name to be restore in pre-production eg: pothys)

• set ProductionDBAdminPwd=postgres

Here replace the ‘postgres’ with the production server database admin password.

(Production DataBase admin password eg: postgres)

• set PreProductionDBAdminPwd=postgres

Here replace the ‘postgres’ with the pre-production server database admin password

(PreProduction DataBase admin password eg: postgres)

• set AdempierePwd=123456

Here replace the ‘123456’ with the database user adempiere password.

(Database user adempiere password eg: 123456)

After changes made, save and close the Backup_Restore.bat file.

Step 3:

Double click the DB_Restore.bat to start the process.

Step 4:

Before restoring, we need to drop the database in pre-production server with the name entered in the script if already exist.

Script will ask your confirmation to drop database in pre-production server, press ‘y’ to confirm the drop DB. If you press ‘n’, process will be stopped without proceeding next.

Step 5:

Log file was created for all activities of script with the name DB_Restore_log in c: drive.

Strictly advised to verify the log.

Finally add the ‘DB_Restore.bat’ to windows Scheduled Task to perform automatically.

Share your thoughts!