OpenERP: Postgresql database backup and restore; manual and automatic.
Configuration: Ubuntu server 10.04 LTS, OpenERP version 6.x.x
Use a commandline terminal

Manual database backup

Create a backup directory 
mkdir /home/openerp/backup/

Backup postgresql database
su openerp 
pg_dump test1 -Fp -U openerp -h localhost -p 5432 > /home/openerp/dbbackup
#password test1234

Restore postgresql database
Create a new database in which you will place the dump file:
su openerp
createdb -U openerp -T template0 test2
psql -f /home/openerp/dbbackup -d test2 -h localhost -p 5432 -q

Or you can do :
Backup postgresql database
su openerp
pg_dump test1 | gzip > /home/openerp/dbbackup.gz #this results in a much smaller file

Restore postgresql database
Create a new database in which you will place the dump file:
su openerp
createdb test2
gunzip -c /home/openerp/dbbackup.gz | psql test2

Automatic database backup
For an automatic backup of your database you have to create a cronjob that runs daily at a certain time.
You must have root rights to do this.
If you want to use an new external harddisk you have to format and mount this HD first.

Formatting of a harddisk
sudo fdisk /dev/sdc #first create a partition. options to choose: n, p, primaire partitie 1
sudo mkfs.ext4 /dev/sdc1 #create the file system ext4 on the HD

Create a mount directory
sudo mkdir /backup
sudo mount /dev/sdc1 /backup #mount your HD

Automatic mount at reboot
sudo blkid #gives you the UUID of the harddisk
sudo vi /etc/fstab
Paste:
UUID=”5609af05-1cc8-4130-bf35-2d53e1eb7bb6″ /backup ext4 defaults 0 0 #add this line at the end of the file

df #this should give /dev/sdc1 … /backup
touch /backup/test #If it’s correct this file will be written to your HD

It is better to do automatic backup locally and write the backups manually to your external harddisk and keep this harddisk in a save place.

Create a backup directory 
cd /var/backups
sudo mkdir openerp

Create a crontab with a backup schedule
sudo crontab -e
paste:
0 3 * * * su postgres -c /usr/bin/pg_dump -U postgres test3 | gzip -c > /var/backups/openerp/dbbackup-$( date +\%d-\%m-\%Y_\%H:\%M ).gz ; echo “Database backup: $(date)” >> /var/backups/openerp/dbbackup.log
0 4 * * 6 find /var/backups/openerp -xdev -ctime +30 -type f -exec rm \{\} \;

sudo vi /var/log/syslog #See the cronjob logs

Any remarks are welcome.
Let me know if it works for you.
_________________
Ubuntu server 10.04 LTS (64 bit)
OpenERP v.6.0.2 with openerp-web client 

Source : http://goo.gl/S7vyq