[postgis-users] Correct dump and restore
stefan.schwarzer at grid.unep.ch
Fri Nov 10 06:52:57 PST 2006
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 >
# Check that all restored dump objects really had to be restored from
# 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
# 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;
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.
> 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
>> Thanks for any hints!
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
> /"\ 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
More information about the postgis-users