Обсуждение: migrating/backup script
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)
Anyone? Bueller?
What's the usefulness of dumping data (-d/D) and oids (-o) separately?
Should I scrap the extra step for oids? If not what order should I
restore them in? Other than asking people who were around when the stuff
was created, how would I know if I need to dump oids separately?
Justin Georgeson wrote:
> 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)
Hi Justin,
I have this scrawled inside the back cover of my copy of PostgreSQL "a
developer's handbook'.
It must have worked or I wouldn't have written it. <g>
create a file for backup e.g.
G:\HCCISA.SQL (obviously a samba mapped drive and file)
as ROOT
on linux red hat 7.3, postgresql 7.2.?
cd /mnt/samba
chmod 777 HCCISQ.SQL
su ( to other postgres user other than postgres) e.g. "su mick"
pg_dump -a -f HCCISA.SQL hccisa -Ft
more scrawl follows that says (for tar gnu zip)
pg_restore -a -Ft -d hccisa HCCISA.SQL
It would appear on further examinaition that these commands came from
Practical PostgreSQL page 353 onwards.
I remember testing both commands and having success with both backup and
restore.
Good Luck
Mark Carew
Brisbane Australia
"Justin Georgeson" <jgeorgeson@unboundtech.com> wrote in message
news:3DF79130.9020105@unboundtech.com...
> 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)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly