[postgis-users] Correct dump and restore

[strk at refractions.net]

On Fri, Nov 10, 2006 at 03:52:57PM +0100, Stefan Schwarzer wrote:
> Under "Hard upgrade" I don't find anything. I guess you mean this  
> section (page 5)? Seems still rather complicated....

Yes, this is the one (isn't called Hard Upgrade?).
And, yes, seems complicated, but might also work :)

--strk;


> 
> # Create a "custom-format" dump of the database you want
> # to upgrade (let’s call it "olddb")
> $ pg_dump -Fc olddb olddb.dump
> # Restore the dump contextually upgrading postgis into
> # a new database. The new database doesn’t have to exist.
> # Let’s call it "newdb"
> $ sh utils/postgis_restore.pl lwpostgis.sql newdb olddb.dump >  
> restore.log
> # Check that all restored dump objects really had to be restored from  
> dump
> # and do not conflict with the ones defined in lwpostgis.sql
> $ grep ^KEEPING restore.log | less
> # If upgrading from PostgreSQL < 7.5 to >= 7.5 you might want to
> # drop the attrelid, varattnum and stats columns in the geometry_columns
> # table, which are no-more needed. Keeping them won’t hurt.
> # !!! DROPPING THEM WHEN REALLY NEEDED WILL DO HURT !!!!
> $ psql newdb -c "ALTER TABLE geometry_columns DROP attrelid"
> $ psql newdb -c "ALTER TABLE geometry_columns DROP varattnum"
> $ psql newdb -c "ALTER TABLE geometry_columns DROP stats"
> # spatial_ref_sys table is restore from the dump, to ensure your custom
> # additions are kept, but the distributed one might contain modification
> # so you should backup your entries, drop the table and source the  
> new one.
> # If you did make additions we assume you know how to backup them before
> # upgrading the table. Replace of it with the new one is done like this:
> $ psql newdb
> newdb=> drop table spatial_ref_sys;
> DROP
> newdb=> \i spatial_ref_sys.sql
> 
> 
> 
> On Nov 10, 2006, at 3:26 PM, strk at refractions.net wrote:
> 
> >See 'Hard Upgrade' section in postgis manual.
> >--strk;
> >
> >On Fri, Nov 10, 2006 at 02:53:52PM +0100, Stefan Schwarzer wrote:
> >>Hi there,
> >>
> >>I've seen the subject coming up already a couple of times. But it
> >>didn't really help me and I can't imagine that there isn't a simple
> >>strategy/method for that:
> >>
> >>I need to make a copy of a database which resides on one computer and
> >>install that copy on another.
> >>
> >>Computer 1 (Linux) has (PostgreSQL) 8.1.0
> >>Computer 2 (Mac) has (PostgreSQL) 8.1.2
> >>
> >>Don't know about the version of PostGIS.
> >>
> >>I tried various pg_dump and pg_restore parameters. But the restore
> >>never worked correctly. I tried it as well with a simple psql but
> >>there too I was faced with problems...
> >>
> >>Can anyone perhaps give me an advice how to correctly export-import a
> >>database?
> >>
> >>Thanks for any hints!
> >>
> >>Stefan
> >>
> >>
> >>_______________________________________________
> >>postgis-users mailing list
> >>postgis-users at postgis.refractions.net
> >>http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >-- 
> >
> > /"\    ASCII Ribbon Campaign
> > \ /    Respect for low technology.
> >  X     Keep e-mail messages readable by any computer system.
> > / \    Keep it ASCII.
> >
> >_______________________________________________
> >postgis-users mailing list
> >postgis-users at postgis.refractions.net
> >http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 

 /"\    ASCII Ribbon Campaign
 \ /    Respect for low technology.
  X     Keep e-mail messages readable by any computer system.
 / \    Keep it ASCII.