Re: Backing up multiple databases - Mailing list pgsql-general

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@aardvarkmedia.co.uk>
# Date:   Fri, 17 Jun 2005 15:52:34 +0100
# To:     Jacob Atzen <jaa@interflow.dk>
# CC:     pgsql-general@postgresql.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)";