OpenERP Auto Backups - SmartMode Way - onsite+dropbox

At SmartMode we believe in smart backups and that is by eliminating any chances of data loss and providing 100% redundancy in case of fire/flood/user error and any other act of God.

Obviously you can use the OpenERP auto_backup module for this, yet that relies on OpenERP server cron and having this run independently is a better choice.

Lets establish the backup policy first:

• Each and every morning at 3.30 we want a backup for each database we have running.

• We would like to keep copies of the last 7 days and a copy for each and every 2nd and 17th of the month.
• We want all backup copies to be available on the server as well as on an offsite location such as Dropbox.

This will mean that in a year we will have 24+7 backups that provide a very generous roll-back piece of mind, yet keep the backup space reasonably low.

So how do we do that?

1 - Create a script that will backup all our databases to the OpenERP home store. You can do that by individually specifying which database you want backed up, yet we will just create a smart script to find and backup all PostgreSQL databases for us, so we never have to remember of this when we add a new database. (in PostgreSQL v9 that is much simplified, yet we still use the 8.3 version and I guess most of OpenERP installations are running the same.

 #!/bin/bash
DIR=/home/openerp/oerpbackups
[ !$DIR ] && mkdir -p $DIR || :
LIST=$(su - postgres -c "psql -lt" |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]')
for d in $LIST
do
 mkdir -p $DIR/$d
 chown postgres:postgres $DIR/$d
 su - postgres -c "/usr/bin/pg_dump --format=c $d | gzip -c > $DIR/$d/$d.`date +\%a`.sql.gz"
done 

We save the above as weeklybackupscript.sh in the home/openerp directory and give it execution permissions. This script will create separate folders for each database if not existent and it will then backup and compress the databases appending the weekday name to them.

2 - Create another similar script that will do the same, yet append the year, month and the date to the backups. Save it as monthlybackupscript.sh and give it execute permissions:

#!/bin/bash
 DIR=/home/openerp/oerpbackups
 [ !$DIR ] && mkdir -p $DIR || :
 LIST=$(su - postgres -c "psql -lt" |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]')
 for d in $LIST
 do
  mkdir -p $DIR/$d
  chown postgres:postgres $DIR/$d
  su - postgres -c "/usr/bin/pg_dump --format=c $d | gzip -c > $DIR/$d/$d.`date +\%Y_%b_%d`.sql.gz"
 done

3 - Now we create a new cron in /etc/cront.d and save it as oerpbackup with executable permissions:

 # m h dom mon dow user command
30 3 * * * root /home/openerp/weeklybackupscript.sh
35 3 2 * * root /home/openerp/monthlybackupscript.sh
35 3 17 * * root /home/openerp/monthlybackupscript.sh 

This will just trigger the backups script: first line will run every morning at 3:30 and will overwrite the one week old backups, the second line will run each 2nd of the month at 3:30 and will not be overwritten and lastly the third line will do the same on the 17th of each month.

4 - Lastly, we need to automate the process, so we have a copy of these folders on Dropbox.
Install dropbox onto the same machine as the openERP installation. You can create a new account with Dropbox just for these backups, keeping it all clean and organised. To install Dropbox on linux follow these instructions . Once you have Dropbox installed and running, you can create a symbolic link:

 ln -s /home/openerp/oerpbackups ~/Dropbox 

So now we have the script that complies with our backup policy. Surelly this can be modified to add additional options - backup to ftp/rsync/webdav etc, yet the chances of our server being destroyed at the same time as dropbox servers and the PCs that dropbox is linked to are very slim, so at any moment in time we have a fresh snapshot of data.