What's the best way to automate backing-up of PostgreSQL databases?

Question

I find it tedious to have to backup databases every week. And I also think weekly backups should be turned into daily backups. If I had to do that, I don't want to do it manually. What's the best way to automate the backing-up of PostgreSQL databases daily?

Answer 1

the same as you do for any other repetitive task that can be automated - you write a script to do the backup, and then set up a cron job to run it.

a script like the following, for instance:

(Note: it has to be run as the postgres user, or any other user with the same privs)

#! /bin/bash

# backup-postgresql.sh
# by Craig Sanders 
# this script is public domain.  feel free to use or modify as you like.

DUMPALL="/usr/bin/pg_dumpall"
PGDUMP="/usr/bin/pg_dump"
PSQL="/usr/bin/psql"

# directory to save backups in, must be rwx by postgres user
BASE_DIR="/var/backups/postgres"
YMD=$(date "+%Y-%m-%d")
DIR="$BASE_DIR/$YMD"
mkdir -p $DIR
cd $DIR

# get list of databases in system , exclude the tempate dbs
DBS=$($PSQL -l -t | egrep -v 'template[01]' | awk '{print $1}')

# first dump entire postgres database, including pg_shadow etc.
$DUMPALL -D | gzip -9 > "$DIR/db.out.gz"

# next dump globals (roles and tablespaces) only
$DUMPALL -g | gzip -9 > "$DIR/globals.gz"

# now loop through each individual database and backup the schema and data separately
for database in $DBS; do
    SCHEMA=$DIR/$database.schema.gz
    DATA=$DIR/$database.data.gz

    # export data from postgres databases to plain text
    $PGDUMP -C -c -s $database | gzip -9 > $SCHEMA

    # dump data
    $PGDUMP -a $database | gzip -9 > $DATA
done

# delete backup files older than 30 days
OLD=$(find $BASE_DIR -type d -mtime +30)
if [ -n "$OLD" ] ; then
        echo deleting old backup files: $OLD
        echo $OLD | xargs rm -rfv
fi

Answer 2

pg_dump dbname | gzip > filename.gz

Reload with

createdb dbname gunzip -c filename.gz | psql dbname

or

cat filename.gz | gunzip | psql dbname

Use split. The split command allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:

pg_dump dbname | split -b 1m - filename

Reload with

createdb dbname cat filename* | psql dbname

Your could toss one of those in /etc/cron.hourly

Sourced from http://www.postgresql.org/docs/8.1/interactive/backup.html#BACKUP-DUMP-ALL

Answer 3

Whatever commands you issue "by hand", - write them to script, and put call to this script in cron or whatever scheduler you use.

You can of course make the script more fancy, but generally, I think that you'll get there - start simple, and later refine.

Simplest possible script:

#!/bin/bash
/usr/local/pgsql/bin/pg_dumpall -U postgres -f /var/backups/backup.dump

Save it as /home/randell/bin/backup.sh, add to cron:

0 0 * * 0 /home/randell/bin/backup.sh

Answer 4

If you want to backup an entire cluster with minimal system load, you can simply tar the root directory of the postgresql cluster. for example:

echo "select pg_start_backup('full backup - `date`');" | psql
/usr/bin/rdiff-backup --force --remove-older-than 7D $BACKUP_TARGET
/usr/bin/rdiff-backup --include '/etc/postgresql' --include $PGDATA --exclude '/*' / $BACKUP_TARGET
/bin/tar -cjf /mnt/tmp/$SERVER_NAME.tbz2 $BACKUP_TARGET 2>&1
echo "select pg_stop_backup();" | psql

that's the bulk of my backup script.