How to setup a regular PostgreSQL database backup

I'm not familiar with PostgreSQL database administration , so I figured I might ask for help from someone more knowledgeable on the subject.

What would you recommend in order to setup a regular (daily) OpenERP database backup?

Ideally, this would be done without stopping the database (hot backup). Is it possible to setup incremental backups, e.g., one weekly reference backup and daily incremental backups?

Are there any automation scripts available I might use?

(I'm using Ubuntu Server.)

I use the following setup:

  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
    

Need more info?

This documentation page has been extracted from the Q&A section where you can discuss it and get feedback.
Related question