migrating/backup script

From: Justin Georgeson <jgeorgeson(at)unboundtech(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: migrating/backup script
Date: 2002-12-11 19:25:36
Message-ID: 3DF79130.9020105@unboundtech.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello all. I recently came into the position of maintaining several SQL
database servers. Having no prior experience other then FileMaker, I'm a
little daunted. :) So one of things I'm doing is moving them to new
servers. In the process I thought it would be a good idea to come up
with a backup script I can turn into a cron job. I took a look at the
scetion on backup/restore in Practical PostGreSQL, and the pg_dump
manpage. I wanted to split out dumping schema/data/oid/blob into
separate dump files. Seems the pg_dump I have doesn't support -C or -b,
or -F. So here's what I have for what's left. In order to automate, I
have a text file, readable only by root, with the username/pass to
connect to the database as. That's what the cat file | pg_dump is for.
I'm very open to improvement on that part of things. Using this, should
I restore data then oids? Will I have any issues going from 7.0.3 to
7.2.1? I'm moving to a RH 7.3 server running the RH 7.2.1 packages. Any
fatal flaws/gotchas I should be aware of? Thanks in advance.

DBS="db1 db2 db3"

BACKUP="/my/backup/folder"
DATE=`date +%Y-%m-%d--%H-%M-%S`

check_err() {
if [ $1 -ne 0 ]; then
echo "There was an error creating $2"
fi
}

cd $BACKUP
mkdir $DATE && cd $DATE

for DB in $DBS; do
echo -n "$DB : ... "
mkdir $DB && cd $DB

# schema
cat $BACKUP/pass | pg_dump -u -s -f ./$DB.schema $DB > $DB.log 2>&1
check_err $? $DB.schema
# data
cat $BACKUP/pass | pg_dump -u -a -D -f ./$DB.data $DB > $DB.log 2>&1
check_err $? $DB.data
# oids
cat $BACKUP/pass | pg_dump -u -o -f ./$DB.oids $DB > $DB.log 2>&1
check_err $? $DB.oids

echo "done."
cd ..
done

--
Justin Georgeson
UnBound Technologies, Inc.
http://www.unboundtech.com
Main 713.329.9330
Fax 713.460.4051
Mobile 512.789.1962

5295 Hollister Road
Houston, TX 77040
Real Applications using Real Wireless Intelligence(tm)

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Christian Brink 2002-12-11 19:30:09 Delta Tool
Previous Message DA-Luis Lorenzo 2002-12-11 16:13:17 Return multiple record