Re: Backup Postgressql

[Craig Sanders <cas@taz.net.au>]

On Wed, Sep 26, 2007 at 09:17:22PM +0200, Turbo Fredriksson wrote:
> Quoting Tinus Nijmeijers <mlists@deephosting.com>:
> 
> > PGPASSWORD=
> > export PGPASSWORD
> 
> Or just:
> 
> unset PGPASSWORD

yep, to both responses.


i find it's also useful to backup each db individually (with the table
definitions aka "schema" in one file, and the data in another), and
wrote the following script (which runs nightly from user postgres'
crontab)

having the table defs and data backed up separately allows you to easily
re-create just the tables OR re-import the data (or do both, one after
the other) - without having to make temp copies of the backup text
file and edit it in vi or whatever. useful not only for backup/restore
purposes but also for moving/duplicating a database from one server to
another.

the script also uses savelog to keep the last few versions of the backup
dumps.

feel free to use and modify it to suit your needs. e.g. setting
PGPASSWORD and backing up databases from remote hosts (or TO a remote
host via ssh) are all pretty easy.

oh, and i use the "-D" option to pg_dump when dumping the data because i
find the "INSERT INTO..." style more reliable than tab-delimited dumps,
even if it does make for much slower restores and bigger dump files
(almost negligible after gzip compresses them).

---cut here---
#! /bin/bash

# backup-databases.sh
# by Craig Sanders <cas@taz.net.au> 2001
#
# this script is licensed under the terms of the GNU GPL.

DUMPALL='/usr/bin/pg_dumpall'
PGDUMP='/usr/bin/pg_dump'

# get list of databases to backup
DBS=$(echo '\l' | psql -t | grep -v 'template[01]' | awk '{print $1}')

# $DIR should be owned by user postgres, and rwx by postgres.
DIR='/var/backups/postgres'
cd $DIR

# first dump entire postgres database, including pg_shadow, to one big file.
DBOUT=$DIR/db.out.gz
[ -e $DBOUT ] && savelog -m 640 -c 2 -l $DBOUT
$DUMPALL -D | gzip -9 >$DBOUT

# now loop through each individual database and backup the schema and data separately 
for database in $DBS; do
    # rotate old copies of backup databases
    SCHEMA=$DIR/$database.schema.gz
    DATA=$DIR/$database.data.gz
    [ -e $SCHEMA ] && savelog -m 640 -c 2 -l $SCHEMA
    [ -e $DATA ] && savelog -m 640 -c 2 -l $DATA

    # export data from postgres databases to plain text
    $PGDUMP -c -s $database | gzip -9 > $SCHEMA
    $PGDUMP -a -D $database | gzip -9 > $DATA
done
---cut here---

craig

-- 
craig sanders <cas@taz.net.au>

BOFH excuse #72:

Satan did it