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 environment...
> 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 steps. 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 you... 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 8.0/main). 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. Ciao, Floris > 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 > [EMAIL PROTECTED] [EMAIL PROTECTED] www.faunalia.it > Piazza Garibaldi 5 - 56025 Pontedera (PI), Italy Tel: (+39)348-3801953 > _______________________________________________ Pkg-grass-devel mailing list Pkg-grass-devel@lists.alioth.debian.org http://lists.alioth.debian.org/mailman/listinfo/pkg-grass-devel
