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: Backing up multiple databases
# From: Gavin Love <gavin@xxxxxxxxxxxxxxxxxxx>
# Date: Fri, 17 Jun 2005 15:52:34 +0100
# To: Jacob Atzen <jaa@xxxxxxxxxxxx>
# CC: pgsql-general@xxxxxxxxxxxxxx
#
# 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)";
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
