Re: Backing up multiple databases

Gavin Love wrote:

>
> Here is the script I use for my daily backups nothing special but it
> works well. Just run it as a user with admin privs on the database. It
> will pull the list of all your databases except templates and dump
> them out.
>

That is pretty neat! Here is Gavin's script slighty modified with some
extra features useful to me and maybe to you, too:

pg_backup.sh:

#!/bin/bash

# Subject:Re: [GENERAL] Backing up multiple databases
# From: Gavin Love <gavin(at)aardvarkmedia(dot)co(dot)uk>
# Date: Fri, 17 Jun 2005 15:52:34 +0100
# To: Jacob Atzen <jaa(at)interflow(dot)dk>
# CC: pgsql-general(at)postgresql(dot)org
#
# Modified by Berend Tober 2005-06-17 to:
# a) include tcp port as command line parameter.
# b) include syntax help.
# c) include Postgresql version information in global.sql output file.
# d) append ".sql" file name suffix to dump output file.
# e) output to current directory.

SYNTAX="Usage: `basename $0` port"

if [ $# -ne 1 ]
then
echo ${SYNTAX}
exit 1
fi

PG_BIN=/usr/bin
OUT_DIR=.
PG_PORT=${1}
TODAY=$(date "+%Y/%m/%d")
BACKUP_DBS=`/usr/bin/psql -p ${PG_PORT} template1 -t -c "SELECT datname
FROM pg_database WHERE datname NOT LIKE 'template_' ORDER BY datname;"`
VERSION_DBS=`/usr/bin/psql -p ${PG_PORT} template1 -t -c "SELECT '--
'||version();"`

mkdir -p $OUT_DIR/$TODAY

echo "Data base backup started at $(date)";

for i in $BACKUP_DBS
do
echo -n "Backing up $i...."
$PG_BIN/pg_dump -p ${PG_PORT} -o -C $i > $OUT_DIR/$TODAY/$i.sql
echo -n "Compressing...."
bzip2 -9 -f $OUT_DIR/$TODAY/$i.sql
echo "Done"
done

echo -n "Backing up globals...."
echo $VERSION_DBS > $OUT_DIR/$TODAY/global.sql
$PG_BIN/pg_dumpall -p ${PG_PORT} -g >> $OUT_DIR/$TODAY/global.sql
echo "Done"

echo "Data base ended at $(date)";