×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PostgreSQL Inc.: PostgreSQL FAQ

Backup Postgres

How can I backup several Postgres databases at once? by roycrom
Posted: 17 Dec 02


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. :)

Back to PostgreSQL Inc.: PostgreSQL FAQ Index
Back to PostgreSQL Inc.: PostgreSQL Forum


My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close