Hi Michael,
Concerning ltree: The situation with pg has not changed in this respect over the last year: if one wants to install a dump with required extensions, one has to care about his. We seldomly use ltree, bit more and more hstore, which has an integration with ::xowiki::FormPages, the same situation. One has to option to drop eg. the ltree index before producing the dump. Rebuilding an index is easy. Btw, ltree and hstore are available via popular distributions such as debian/ubuntu in the postgres-contrib package.
Concerning upgrades: if one is planing to upgrade from pg8 to pg9, one should first upgrade with pg8 to the current OpenACS version (for now, oacs-core in CVS head), then produce a dump load the dump into pg9.
Actually, i am afraid, one has to be more precise on the versions here. When updating from pg 8.3, there is a potential problem with recursive query processing. While the .xql files can differentiate between pg versions, the upgrade scripts do not. So, if one is loading the latests upgrade script for recursive query processing from victor (acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d3-5.8.0d4.sql) into pg8.3 there might be a problem, since recursive queries were introduced in pg8.4.
So, for upgrading from pg8.3 (or earlier), I would recommend to upgrade first the database to pg8.4 (i.e. load the dump from the pg8.3 installation into pg8.4), then run all upgrade scripts, dump again and load the new dump into pg9.2
Running the old upgrade scripts in pg9 might run into problem with quoting and backslashing, but it might depend from which version one is starting. It might be possible to change the syntax in offending old upgrade scripts to be pg9 friendly, but one has to have a pg8.3 installation to figure this out an to test it.
For the kernel and CR, Victor has added last year update scripts to bring the function_args and the necessary functions to the same state as in new installations (see e.g. acs-kernel/sql/postgresql/upgrade/upgrade-5.8.0d1-5.8.0d2.sql).
best regards
-gustaf neumann
