Provide a clear upgrade path to 2.0

[strk]

We need a way to upgrade an existing spatial database to 2.0 w/out having to ignore errors here and there.

Ideally a script could take care of it all, but first thing is defining the process steps.

[strk]

My current idea is:

1. pg_dump -Fc > old.dump
2. createdb newdb && spatially enable it
3. utils/new_postgis_restore.pl old.dump | psql newdb

I've been working on making this model work. Can you see conceptual issues with it ?

[strk]

By the end of the process above I end up with some tables using geometry column constraints and some tables using typmod. We may need a step 4. to perform the cleanup/conversion or embed that part directly into step 3. (postgis_restore phase). Splitting would help debugging and be probably safer.

[darkblueb]

I have now tried this once on a non-trivial database. It appears to succeed.

source database => POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.7" USE_STATS

destination database, trunk rev 8447 => POSTGIS="2.0.0SVN" GEOS="3.4.0dev-CAPI-1.8.0" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS

[strk]

darkblueb: which PostgreSQL versions for the two above upgrades ?

[darkblueb]

destination postgresl 9.1.1 source postgresql 9.1b3

[strk]

With r8468 the postgis_restore.pl script also takes care of spatial_ref_sys by restoring all entries in dump with SRIDs not found in pre-existing spatial_ref_sys table, thus allowing you to decide which version of the core srid you want (you probably want the new ones).

This means that step 2 in the first comment (spatially-enable new database) could now include sourcing spatial_ref_sys.sql w/out worrying about getting a zilion "duplicate primary key" errors at restore time.

I guess all is left here is documenting the procedure and calling for test.

[strk]

r8469 documents the procedure in the existing HARD UPGRADE section of the PostGIS manual. Any left over I would call a bug, thus worth a separate ticket. Thanks for testing!