https://www.odoo.com/forum/help-1/question/how-to-setup-a-regular-postgresql-database-backup-4728
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
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
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
No comments:
Post a Comment