How can I backup several Postgres databases at once?

These scripts were just written using vi and uses the bourne shell.

It relies on oid2name that comes with the distribution of postgresql and you'll obviously need to change the paths so I've just used YOURPATH so you can modify it easily yourself. I use the first script to get a snapshot of the database cluster just before a backup of the server is done so that I know exactly what date and time it was done and so i know that it has not been changed during the backup time. I call it like this from the cron:

min hr * * * /YOURPATH/dumpdb.sh >> /logs/cron.log 2>&1

#!/bin/sh
#
# Purpose: This script creates a dump of the full database cluster
# into one file and then proceed to dump individual databases
# into one directory - /PATH/dumpdir
#
# create variables

USER="postgres"
NAMECMD="/YOURPATH/oid2name -U $USER"
TMPDUMP="/YOURPATH/dump.tmp"
DUMPDAT="/YOURPATH/dump.dat"
DUMPDIR="/YOURPATH/dumpdir"
PGPASSWORD="*****" # You'll need to change this
PATH=$PATH:/YOURPATH/bin
export PGPASSWORD PATH

echo "Script $0 starting at `date`"

# Remove any old files

[ -f $TMPDUMP ] && rm -f $TMPDUMP
[ -f $DUMPDAT ] && rm -f $DUMPDAT

# Get database names and put into .tmp file

$NAMECMD | cut -b10- > $TMPDUMP


# Get rid of unwanted stuff in .dat file by filtering out.

while read LineIn; do
CHAR=`echo $LineIn | cut -c1`
if [ "$CHAR" != "" ] && [ "$CHAR" != "-" ]; then
if [ "$LineIn" != "template0" ] && [ "$LineIn" != "template1" ] && [ "$LineIn" != "ases:" ]; then
echo $LineIn >> $DUMPDAT
fi
fi
done<$TMPDUMP

# May as well do a complete dump as well.

[ -f $DUMPDIR/alldata.dmp ] && rm -f $DUMPDIR/alldata.dmp
echo "Commencing full dump into $DUMPDIR/alldata.dmp..."
pg_dumpall -U $USER > $DUMPDIR/alldata.dmp


# Dump individual databases separately so can restore just one if we need.

if [ -f $DUMPDAT ]; then
echo ""
echo "Dumping individual databases..."
while read LineIn; do
echo "dumping $LineIn to $DUMPDIR/$LineIn.dmp"
[ -f $DUMPDIR/$LineIn.dmp ] && rm -f $DUMPDIR/$LineIn.dmp
pg_dump -U postgres -f $DUMPDIR/$LineIn.dmp $LineIn
done<$DUMPDAT
else echo "No individual databases to dump, exiting..."
exit 0
fi

# Tidy up and change owner of files.

chown $USER:$USER $DUMPDIR/*

[ -f $TMPDUMP ] && rm -f $TMPDUMP
[ -f $DUMPDAT ] && rm -f $DUMPDAT

echo "Script completed at `date`"

-----------------------------------------------------------------------------

This next script would only need to be used if you're databases became corrupted and you needed to get rid of them all before using the output file from pg_dumpall to do a full restore. You may find another use.

-----------------------------------------------------------------------------
#!/bin/sh
#
# create variables

USER="postgres"
NAMECMD="/YOURPATH/oid2name -U $USER"
TMPDUMP="/YOURPATH/dump.tmp"
DUMPDAT="/YOURPATH/dump.dat"
PGPASSWORD="******"
PATH=$PATH:/YOURPATH/bin
export PGPASSWORD PATH

echo "Script $0 starting at `date`"

# Remove any old files

[ -f $TMPDUMP ] && rm -f $TMPDUMP
[ -f $DUMPDAT ] && rm -f $DUMPDAT

# Confirm to proceed

echo "Are you sure you want to drop all databases? [y/n]: \c"
read ANSWER

if [ $ANSWER != "y" ] && [ $ANSWER != "ye" ] && [ $ANSWER != "yes" ] && [ $ANSWER != "Y" ] && [ $ANSWER != "YE" ] && [ $ANSWER != "YES" ]; then
echo ""
echo "Exiting, no databases have been affected."
exit 0
fi

# Get database names and put into .tmp file

$NAMECMD | cut -b10- > $TMPDUMP


# Get rid of unwanted stuff in .dat file by filtering out.

while read LineIn; do
CH=`echo $LineIn | cut -c1`
if [ "$CH" != "" ] && [ "$CH" != "-" ]; then
if [ "$LineIn" != "template0" ] && [ "$LineIn" != "template1" ]
&& [ "$LineIn" != "ases:" ]; then
echo $LineIn >> $DUMPDAT
fi
fi
done<$TMPDUMP


# Drop all the databases

if [ -f $DUMPDAT ]; then
echo ""
echo "Continuing to drop all databases..."
while read LineIn; do
echo "Dropping database - $LineIn..."
dropdb -U postgres $LineIn
done<$DUMPDAT
else
echo ""
echo "No databases exist, exiting..."
exit 0
fi

# Tidy up after myself!

[ -f $TMPDUMP ] && rm -f $TMPDUMP
[ -f $DUMPDAT ] && rm -f $DUMPDAT

echo "Script completed at `date`"

-------------------------------------------------------------------------

So there you have it. I hope you find these helpful. I don't really talk to other Postgres users so I'm not too sure how useful they would be.

As you can see you'll have to edit it slightly but only the variables at the top of each script. Thinking about it I should have seperated it up into functions or procs... that'lll be my next task.