[postgis-users] pg_dump -Fc vs pg_dumpall for daily backups
strk at refractions.net
strk at refractions.net
Mon Nov 14 01:09:00 PST 2005
Briefly: you SHOULD NOT need postgis_restore.pl if not upgrading postgis.
Note that if you upgrade postgresql, you *might* need it, as some postgis
scripts are sensitive to database version (consider schema support for
example).
--strk;
On Sun, Nov 13, 2005 at 11:05:10AM -0800, Blair Zajac wrote:
> strk at refractions.net wrote:
> >On Sat, Nov 12, 2005 at 09:30:36PM -0800, Blair Zajac wrote:
> >
> >>Hello,
> >>
> >>Being new to PostGIS and reading the README, it says that one shold use
> >>'pg_dump
> >>-Fc' to do a dump that is given to 'utils/postgis_restore.pl' to upgrade
> >>PostGIS.
> >>
> >>However, for doing just daily backups and recovering a box that crashed
> >>(say you
> >>lost a disk) and you want to do a full restore into a freshy init'ed
> >>PostgreSQL
> >>database, does pg_dumpall work? Or does one always need to use pg_dump
> >>-Fc for
> >>any database with geographic columns?
> >
> >
> >You need -Fc (custom format) for postgis_restore.pl to work.
> >I dunno if there's a way to produce custom format with pg_dumpall.
> >If you find it you can try postgis_restore.pl against it, but
> >I never tested it.
> >
> >--strk;
>
> Yes, there's no way to pass -Fc from pg_dumpall down to the individual
> pg_dump's it runs.
>
> I ended up writing a shell script that parses 'psql -l' for all databases
> and runs 'pg_dump -Fc' on all of them.
>
> However, it seems that doing a full recover of a database with pg_dumpall
> is easier, as it's a single command and it takes into account restoring all
> the databases in the correct order.
>
> I see the need for using postgis_restore.pl for doing an upgrade of your
> PostGIS version, as the README says:
>
> ---
> Following is the "old" procedure description. IT SHOULD BE AVOIDED if
> possible,
> as it will leave in the database many spurious functions. It is kept in
> this document as a "backup" in case postgis_restore.pl won't work for you:
>
> pg_dump -t "*" -f dumpfile.sql yourdatabase
> dropdb yourdatabase
> createdb yourdatabase
> createlang plpgsql yourdatabase
> psql -f lwpostgis.sql -d yourdatabase
> psql -f dumpfile.sql -d yourdatabase
> vacuumdb -z yourdatabase
> ---
>
> However, if you're not upgrading your PostGIS and just need to restore it,
> then there are no spurious functions that you need to remove, right?
>
> I'm asking just to be on the safe side, since I'd like to use pg_dumpall to
> get a single dump of all my databases. Right now I'm dumping with
> pg_dumpall and with my custom script, but would not rather run two separate
> dump scripts to properly dump my databases with PostGIS columns.
>
> So, will using the procedure noted in the README work? Will restoring from
> a pg_dumpall work when pg_dumpall doesn't pass -Fc down to 'pg_dump -Fc'?
> Do you always need postgis_restore.pl to restore a database, even if you're
> not upgrading PostGIS versions?
>
> BTW, I'm attaching my Postgresql backup script, in case anybody wants it.
> It dumps each database with -Fc. On my Ubuntu box, I just drop this into
> my /etc/cron.daily directory.
>
> Regards,
> Blair
>
> --
> Blair Zajac, Ph.D.
> <blair at orcaware.com>
> Subversion and Orca training and consulting
> http://www.orcaware.com/svn/
> #!/bin/sh
>
> # Postgresql username.
> pg_username="postgres"
>
> # Directory where backups go.
> backup_dir="/var/lib/postgresql_backup"
>
> # Flag to indicate if the dump file should be compressed. Set to an
> # empty string to not compress the dump.
> compress_dump=
>
> do_backup()
> {
> database=$1
> shift
>
> filename=$1
> shift
>
> echo " backing up $database"
>
> test -e $filename.tmp && rm -f $filename.tmp
>
> /usr/bin/pg_dump -Fc --oids --compress 9 -f $filename.tmp $database
>
> if test $? -ne 0; then
> echo "$0: pg_dump failed." 2>&1
> return 1
> fi
>
> if test "$compress_dump"; then
> test -e $filename.tmp.bz2 && rm -f $filename.tmp.bz2
> bzip2 -9 $filename.tmp
> if test $? -ne 0; then
> echo "$0: zip2 -9 $filename.tmp failed." 2>&1
> return 1
> fi
>
> if test -e $filename.bz2; then
> if test -e $filename.0.bz2; then
> rm -f $filename.0.bz2
> if test $? -ne 0; then
> echo "$0: rm -f $filename.0.bz2 failed." 2>&1
> return 1
> fi
> fi
> mv $filename.bz2 $filename.0.bz2
> if test $? -ne 0; then
> echo "$0: mv $filename.bz2 $filename.0.bz2 failed." 2>&1
> return 1
> fi
> fi
> mv $filename.tmp.bz2 $filename.bz2
> if test $? -ne 0; then
> echo "$0: mv $filename.tmp.bz2 $filename.bz2 failed." 2>&1
> test -e $filename.0.bz2 && mv $filename.0.bz2 $filename.bz2
> return 1
> fi
> else
> if test -e $filename.0; then
> rm -f $filename.0
> if test $? -ne 0; then
> echo "$0: rm -f $filename.0 failed." 2>&1
> return 1
> fi
> fi
> if test -e $filename; then
> mv $filename $filename.0
> if test $? -ne 0; then
> echo "$0: mv $filename $filename.0 failed." 2>&1
> return 1
> fi
> fi
> mv $filename.tmp $filename
> if test $? -ne 0; then
> echo "$0: mv $filename.tmp $filename failed." 2>&1
> test -e $filename.0 && mv $filename.0 $filename
> return 1
> fi
> fi
>
> return 0
> }
>
> if test ! -d "$backup_dir"; then
> echo "$0: backup directory '$backup_dir' does not exist or is not a dir" 1>&2
> exit 1
> fi
>
> # Su into the postgres user if we're not as all backups will be done
> # as that user.
> pg_uid="`/bin/grep ^$pg_username /etc/passwd | /usr/bin/cut -d: -f3`"
> if test -z "$pg_uid"; then
> echo "$0: unable to determine postgresql user '$pg_username' uid." 1>&2
> exit 1
> fi
>
> my_uid="`/usr/bin/id -u`"
> if test -z "$my_uid"; then
> echo "$0: unable to determine my effective uid." 1>&2
> exit 1
> fi
>
> case "$my_uid" in
> 0)
> exec /bin/su - $pg_username -c "$0"
> ;;
> $pg_uid)
> ;;
> *)
> echo "$0: must run this as root or $pg_username"
> exit 1
> ;;
> esac
>
> # Get the list of databases.
> pg_databases="`/usr/bin/psql -l \
> | awk 'NF == 5 && \
> $1 != "Name" && \
> $1 != "template0" && \
> $2 == "|" && \
> $3 != "Owner" && \
> $4 == "|" && \
> $5 != "Encoding" \
> {print $1}' \
> | sort`"
>
> umask 0077
>
> cd $backup_dir || exit 1
>
> echo "$0: backing up postgresql database"
>
> status=0
> for db in $pg_databases; do
> do_backup $db $db
> if test $? -ne 0; then
> status=1
> fi
> done
>
> if test 0 -eq "$status"; then
> echo "$0: backup of postgresql succeeded"
> exit 0
> else
> echo "$0: backup of postgresql FAILED" 1>&2
> exit 1
> fi
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
--
/"\ ASCII Ribbon Campaign
\ / Respect for open standards
X No HTML/RTF in email
/ \ No M$ Word docs in email
More information about the postgis-users
mailing list