[postgis-users] pg_dump -Fc vs pg_dumpall for daily backups

[Blair Zajac]

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/
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: postgresql-server-dump-pg_dump-Fc
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20051113/7f07939b/attachment.ksh>