[postgis-users] Upgrade both Postgres/postgis and Debian major releases - how to?

[Pietro Rossin]

Hello list
We have a Debian 8.9 server with Postgres 9.4.12/postgis 2.1
We are trying to upgrade debian to 9.1, Postgres to 9.6 and postgis to
2.3.1.

The problem is similar to what is described here
http://blog.cleverelephant.ca/2016/08/postgis-upgrade.html
<http://blog.cleverelephant.ca/2016/08/postgis-upgrade.html>  

During Debian upgrade to 9.1

Postgrersql is upgraded to 9.6 and postgis to 2.3

The postgis-2.1.so file is removed
The postgis-2.3.so file is added

So we have two clusters (9.4 main and 9.6 main) but 9.4 is broken..

We try to migrate our clusters with pg_upgradecluster and we get an error
about a library not available.
These are libraries in the 9.4 path (postgis-2.1.so)

We tried many options

1) in Debian 8.9 we tried to upgrade to Postgres 9.4/postgis 2.3 before the
debian 9.1 upgrade, no success
2) in debian 9.1 we tried to make a symlink (in postgres 9.4 path) to
postgis-2.3.so (in 9.6 path), no success
3) in debian 9.1 we tried ALTER EXTENSION postgis UPDATE TO '2.3.1';, no way
4) we tried to backup the old libraries in debian 8.9, then upgrade, then
restore these in 9.4 path. With pg_upgradecluster we don't get error message
and the 9.4 cluster is migrated to the 9.6 main one, but some strange thing
happens.
GRANT SELECT ON TABLE geometry_columns TO public;
and
GRANT SELECT ON TABLE spatial_ref_sys TO public;
is not present in the 9.6 spatial database, so Qgis don't understand what
kind of geometry my tables are (no entry in geometry table) and which srid
they have..

We can grant select rights to public by hand, but the migration made this
way could have some other troubles???


Is there any other solution to migrate my spatial database from a version to
another

Best way to deal with this problem??

Thanks for any help!
Pietro



--
View this message in context: http://postgis.17.x6.nabble.com/Upgrade-both-Postgres-postgis-and-Debian-major-releases-how-to-tp5011462.html
Sent from the PostGIS - User mailing list archive at Nabble.com.