Hi Paolo & friends,

> We're probably in the worst of situations, having to upgrade both from
> 0.9.2
> to 1.0 and from 7.4 to 8.0.
Yes we are all in that same situation I think... But it is not that bad,
really! Imagine not being able to use the multicluster/multiversion

> 7.4 to 8.0 (I imagine the automated pg_upgradecluster will not work?)
The upgrade_cluster will not work, because the postgis versions 0.9 and
1.0 are not compatible, that script basically calls pg_dump_all and
restores that again in the new env.
The postgis upgrade script did not work for me so I followed their manual

What would greatly help is this:
A pg_dump_all that dumps only users/schemas/views/keys/etc and data, not
the rest!
But that will be hard considering that all kinds of keys/sequences/etc
need to be preserved, but not the types/functions that are upgraded in the
new setup. And some people might have very elaborate triggers/functions
written that depend on a specific version of the libraries, so these can
never be automatically upgraded.
Perhaps an interactive upgrade (example: Do you whish to restore 600
postgis functions? No...).
And maybe also the same setup for shp2pgsql as for instance createlang
--cluster (or callable with --pgis_version or something).
But since that isn't available (yet) here is the manual way:

What I suggest is this:
Use multicluster and first make sure the original install works again
after installing postgresql-common, then upgrade into a separate cluster
(running on a different port). When that works, change ports of the
clusters. Downtime can be meassured in minutes ;-). And have extra respect
for Martin Pitt for writing the postgresql-common to make this possible
and 'relatively' pain free: thanks Martin!

Little more detailed:

0. Any shapefile that you loaded as a table, you are better of loading
again with shp2psql. Note that you should take extra care to be sure that
you are using the correct binary of shp2pgsql and that you connect to the
correct database! (This is not something you want to do with pgadmin from
a MSwindows machine...)
/usr/lib/postgresql/8.0/bin/shp2pgsql -s 28992 -I -D my.shp
myschema.mytable > myshp.sql
psql -d mynewdb -f myshp.sql --cluster 8.0/main

In a production environment (which is what I deal with) I now did this:
1 a. Keep your original data at the original configuration. In my case
that was pg7.4 and pgis0.9. I 'upgraded' that to pg7.5/pgis0.9 by
installing the postgresql-common package and manually copying postgis to
its new place (see earlier post).
  b. Make sure everything works (also your mapserver installation, etc.)
including all userinterfaces for clients (website?). This install is not
only your backup, all your users will not be aware of all the upgrade
trouble we'll deal with in the separate cluster. For me everything
worked (and there should not be a reason why not, since nothing really
changes, except for the paths where pg and pgis are located).

Now the real upgrading:
I personally do not see a reason to upgrade to pg8.0/pgis0.9 first so I
upgrade straight to pg8/pgis1.0.
2. For this I installed pg8.0 and created a separate 'cluster' on port
5434 (or one of your own favorates). Now I manually installed pgis 1.0 but
Alex is working hard on the packages so hopefully that is ok to do with
the package.
The tricky part is that a simple database dump/restore will not work. The
postgis API and the way the objects are stored, changed. There is a script
that will attempt this for you, but it doesn't work if you use more
elaborate setups (with different languages loaded and other goodies from
contrib, I use for instance dblink) and I haven't tested it at all with an
upgrade between postgresql versions.

3. I used dump_all and dumped to plain/SQL-inserts and manually deleted
all the (postgis) function creation out of this file.
You keep:
o table creation
o user creation
o data insertion
o triggers/keys/sequences etc.
the rest you delete:
In the file the first set of lines create your databse schema
(recognizable by CREATE TABLE etc.). The schema is something you want to
keep... Then you get all the functions (recognizable with CREATE FUNCTION)
and types (CREATE TYPE), those are things you want to delete, loading the
new lwpostgis.sql will have created them for you.
Any users that you probably want to keep are created by CREATE USER. I
moved this to a separate SQL script.
All the inserts in the table you want to keep.

Do not be afraid to delete too much, if it fails you still have the
original database up and running so nobody is going to notice it except

I manually created my template database in pg8.0 with pgis1.0 and dblink
etc. Make sure you follow the correct path to the NEW lwpostgis.sql
(probably /usr/share/postgresql/8.0/contrib/lwpostgis.sql)!!!
Now I create a brandnew database
(createdb -T mygistemplate --cluster 8.0/main mynewdb) and load the
sanitized dump into it (psql -d mynewdb -f theSanitizedDump.sql --cluster
 It SHOULD work error/warning free...
I restored views manually (I did not have that many) by copy/pasting them
in PGadmin from MSwindows (sorry).

Good luck!

I hope this helps people, maybe we can share whatever experience we have
and create a page on debianGIS to address all this.


> What do you suggest:
> - first upgrade to 0.9 to 1.0 (following the advice from Floris), then
> from
> 7.4 to 8.0 (I imagine the automated pg_upgradecluster will not work?)
> or
> - first upgrade from 7.4 to 8.0 (but how?), then from 0.9 to 1.0?
> Your advice will be much appreciated.
> All the best.
> pc
> At 15:34, lunedì 10 ottobre 2005, Floris Sluiter has probably written:
>> Hi,
>> It seems postgis 0.9 gets in the way of postgis 1.0.4:
>> ERROR:  could not find function "LWGEOM_analyze" in file
>> "/usr/lib/postgresql/7.4/lib/liblwgeom.so.1.0"
>> It is not possible to dump/restore databases between postgis 0.9 and 1.0
>> without error's yet.
>> Use:
>> 'Dump to plain' and manually delete creation of old postgis functions
>> from
>> the file 'backup.sql'.
>> Manually insert postgis functions:
>> psql --cluster 8.0/main -f
>> /usr/share/postgresql/8.0/contrib/lwpostgis.sql
>> MySpatialDB
>> Then do
>> psql --cluster 8.0/main -f backup.sql
> --
> Paolo Cavallini
> Piazza Garibaldi 5 - 56025 Pontedera (PI), Italy   Tel: (+39)348-3801953

Pkg-grass-devel mailing list

Reply via email to