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!

 

 

Linux / UNIX: Encrypt Backup Tape Using Tar & OpenSSL

How do I make sure only authorized person access my backups stored on the tape drives (DAT, DLT, LTO-4 etc) under Linux or UNIX operating systems? How do I backup /array22/vol4/home/ to /dev/rmt/5mn or /dev/st0 in encrypted mode?

You can easily encrypt data to tape using combination of tar and openssl commands. The following is software based solution based upon encryption algorithms supported by openssl tool. Encrypted backup should be used when storing sensitive data on removable media or when storing backups on shared NAS / SAN servers or online backup servers. When using encryption the openssl ask for a password before you can create, view, open, or restore the files included in the backup. This is based upon pipes concept.

Backup Data

The following shows an example of writing the contents of “tapetest” to tape:

tar zcvf - /array22/vol4/home | openssl des3 -salt | dd of=/dev/st0

An encryption password would be entered by the administrator or backup operator i.e. the above will encrypt a tape using triple DES in CBC mode using a prompted password. You can put password in script itself:

tar zcvf – /array22/vol4/home | openssl des3 -salt -k “Your-Password-Here” | dd of=/dev/st0

Reading (listing) Files

Type the command as follows:

dd if=/dev/st0 | openssl des3 -d -salt | tar ztvf -

OR

dd if=/dev/st0 | openssl des3 -d -salt -k "Your-Password-Here" | tar ztvf -

Restore The Data

Use the following command to read and restore data back:

dd if=/dev/st0 | openssl des3 -d -salt | tar xzf -

OR

dd if=/dev/st0 | openssl des3 -d -salt -k "Your-Password-Here" | tar xzf -

Where,

* dd : Convert and copy a file.
* /dev/st0 : Tape device name.
* openssl : The OpenSSL toolkit command line utility.
* tar : The tar archiving utility.
* des3 : Triple-DES Cipher (Triple DES is the common name for the Triple Data Encryption Algorithm).
* -salt : The -salt option should ALWAYS be used if the key is being derived from a password unless you want compatibility with previous versions of OpenSSL and SSLeay. Without the -salt option it is possible to perform efficient dictionary attacks on the password and to attack stream cipher encrypted data. The reason for this is that without the salt the same password always generates the same encryption key. When the salt is being used the first eight bytes of the encrypted data are reserved for the salt: it is generated at random when encrypting a file and read from the encrypted file when it is decrypted. (source enc man page)

Hardware vs Software Encryption

The software encryption is different from the hardware encryption. The hadrware based encryption needs additional software+hardware and it use keys (and/or password) to protect data. I suggest you read vendor site such as HP or IBM to get further details on hardware encryption which may or may not be supported by your backup devices.

Share your thoughts by comments!