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 a nano text editor on the terminal. Here is the command.

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.

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:

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 the 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