odoo backup dababase

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

1. Backup-Script /var/scripts/dump_db.sh

   #!/bin/sh      hostname=`hostname`    ##########################################  ## OpenERP Backup  ## Backup databases: openerpdb1, openerpdb2  ##########################################    # Stop OpenERP Server  /etc/init.d/openerp-server stop    # Dump DBs  for db in openerpdb1 openerpdb2  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/openerp-server start    exit 0  

2. Housekeeping script /var/scripts/housekeeping.sh (deletes backups which are older than 30 days)

   #!/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  

3. Create daily cronjobs in /etc/crontab. The backup runs daily at 1am and the housekeeping job runs daily at 5am.

   # m h dom mon dow user  command  0 1 * * * postgres /var/scripts/dump_db.sh  0 5 * * * postgres /var/scripts/housekeeping.sh

Simples and efective...

The script is executed via cron with

 00 01 * * * nice -19 /home/mbmaster/scripts/backupdb > /dev/null 2>&1  

the "nice -19" is important to lower the priority of the backup

 # Backup script starts here.     #!/bin/bash   # Location of the backup logfile.   logfile="/home/mbmaster/backups/logfile.log"     # Location to place backups.   backup_dir="/home/mbmaster/backups"     touch $logfile    timeslot=`date +%d%m%y%H%M%S`    databases=`psql -U postgres -q -c "\l" | awk '{ print $1}' | grep -vE '^\||^-|^List|^Name|template[0|1]|^\('`      for i in $databases; do      timeinfo=`date '+%T %x'`      echo "Backup and Vacuum started at $timeinfo for time slot $timeslot on database: $i " >>      $logfile      /usr/bin/vacuumdb -z -U postgres $i >/dev/null 2>&1      /usr/bin/pg_dump $i -U postgres | gzip > "$backup_dir/openerp-$i-$timeslot-database.gz"      timeinfo=`date '+%T %x'`      echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile   done     #-------------------------------------------------     # delete files more than 10 days old   find $backup_dir/openerp* -mtime +10 -exec rm {} \;

1 - Backup-Script /var/lib/postgresql/postgres_db_backup.sh

#!/bin/bash    # Location to place backups.  backup_dir="/var/backups/postgres_db/"    #String to append to the name of the backup files  backup_date=`date +%Y-%m-%d_%H-%M`    #Numbers of days you want to keep copie of your databases  number_of_days=3  databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`  for i in $databases; do    if [ "$i" != "template0" ] && [ "$i" != "template1" ] && [ "$i" != "postgres" ]; then      backupfile=$backup_dir$i.$backup_date.sql.gz      echo Dumping $i to $backupfile      pg_dump $i|gzip > $backupfile    fi  done  find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;  

2 - open your terminal

• su

• su postgres

• crontab -e

add

• 45 */4 * * * /var/lib/postgresql/postgres_db_backup.sh