Imagine the magnitude of lost revenue if the production database of a large company became unavailable, even for just 5 or 10 minutes; or, if you lose database due to media failure and cannot restore or recover them because you do not have a backup. From your enterprise’s perspective, the results may be quite harsh. The key to your success in this situation is a well-defined backup and recovery strategy.

In large Enterprise it is must to tailor your backup strategy. Backup strategy can be Hourly, Daily, Weekly, or Monthly depending on your enterprise.

Here we are sharing Odoo database auto backup using default linux crontab very effectively without consuming much space for the database backup.

Create PostgreSQL Backup Script for Odoo

create a script called odoo_backup.sh on /var/scripts/ path. you can create the file using nano text editor on terminal. Here is the command.

sudo nano /var/scripts/odoo_backup.sh

Then copy and paste below code

#!/bin/sh
hostname=localhost
####################################
## OpenERP Backup
## Backup databases:odoo_live_db1,odoo_live_db2
##########################################
# Stop OpenERP Server
/etc/init.d/odoo-server stop
# name of the database to be backed up
for db in odoo_live_db1 odoo_live_db2
do
date=
date +"%Y%m%d_%H%M%N"
filename="/var/pgdump/${hostname}_${db}_${date}.sql"
pg_dump -E UTF-8 -p 5433 -F p -b -f $filename $db
gzip $filename
done
# Start OpenERP Server
/etc/init.d/odoo-server start
exit 0

Remove Old Odoo Database backup

Remove old database backup files which is older than 30 days.
create a file named clean_backup.sh on /var/scripts/ path. Here is the command.

sudo nano /var/scripts/clean_backup.sh

Then copy and paste below code

#!/bin/sh
path=/var/pgdump
logfile=/var/log/$0
rm -f $logfile
for file in
find /var/pgdump/ -mtime +30 -type f -name *.sql.gz
do
echo "deleting: " $file >> $logfile
rm $file
done
exit 0

After creating both the file we are ready to create cron(schedule) jobs using crontab. In below example backup runs daily at 2am and the old database cleanup job runs daily at 5am.

To edit or create your crontab file, type the following command at the Linux shell prompt:

crontab -e

Select default editor if you are using command first time. and paste the below line. for more information on linux crontab you can visit this website.

# m h dom mon dow user command
0 2 * * * postgres /var/scripts/odoo_backup.sh
0 5 * * * postgres /var/scripts/clean_backup.sh

Now the crontab will take backup of your odoo database using the odoo_backup.sh script everyday 2am in the morning and store the odoo database backup under /var/pgdump/.

It will work for all odoo database version.

Reference Links: https://www.odoo.com/forum/help-1/question/how-to-setup-a-regular-postgresql-database-backup-4728