[postgis-users] how to backup/restore

[Ben Madin]

Nicholas,

On 12/04/2010, at 12:33 , Nicholas Bower wrote:

> On Mon, Apr 12, 2010 at 12:21 PM, Ben Madin <lists at remoteinformation.com.au> wrote:
> OK
> 
> On 12/04/2010, at 10:03 , Nicholas Bower wrote:
> 
> > 2. Dump just this separate data schema using pg_dump -Fc -N <schema>
> 
> I think here you mean -n?, but it's six of one and half a dozen of the other. I routinely use different schema's for different aspects of the database, hence easier to just exclude one.
> 
> Yes -n you're right.
>  
> 
> > I note your solution of the separate schema using default path hack - interesting that this works (assume you change the search path for all db updater roles).
> 
> I'm not sure what you mean by this question, sorry. I do change the search_path for the database -
> 
>        ALTER database SET search_path TO data, reference, users, gis;
> 
> if that is what you are referring to?
> 
> Yep that's it - you're changing the search path not just of the restore, but all roles using that database ongoing so they can find the postgis functions.  When I started experimenting with Postgis back in 2003, I couldn't get it to work so ever since I've used public schema for postgis.  I should have tried harder ;)

Yeah, I'm a relative newcomer to this, which may have saved me some grief, although I have once deleted the geometry type from a big database.

> Btw have you restored your backups from scratch before and found them to work?

Yes - pretty routinely when I dump out the database to make a local copy, but also for upgrades to PostgreSQL (most recently to 8.4). I'm actually a bit of a tech luddite, and haven't ever mastered the more technical aspects of pg_dump and pg_restore, I just dump as a script (also using the -O flag normally), tar, rsync and then run the file into psql - since much of it is on remote servers I never really learnt how to use pgadmin.

I've also done it when I have deleted the Geometry type from a big database... 

cheers

Ben