[postgis-users] Correct dump and restore

[Stefan Schwarzer]

Under "Hard upgrade" I don't find anything. I guess you mean this  
section (page 5)? Seems still rather complicated....

# 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