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

[Regina Obe]

Pietro,

> 1) in Debian 8.9 we tried to upgrade to Postgres 9.4/postgis 2.3 before the debian 9.1 upgrade, no success
Can you elaborate on what you did here to upgrade and what error you got.

No success is too vague

> 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
This definitely will not work.  The postgis-2.3.so is bound to the version of postgres so trying to use a postgis-2.3.so in 9.4 meant for 9.6 will result in errors
The .so will not be loadable
What you should do is create a symlink from the 9.6 postgis-2.3.so   to ->  postgis-2.1.so  (so you end up with a postgis-2.1.so that is really a postgis-2.3 library)
Then doing a pg_upgradecluster should work and once you are on 9.6

Do a

ALTER EXTENSION postgis UPDATE;

To have a clean  postgis-2.3

> 3) in debian 9.1 we tried ALTER EXTENSION postgis UPDATE TO '2.3.1';, no way
I'm guessing this is because you did not install postgis 2.3.1 for your 9.4 (and as mentioned in comment on 2, that approach does not work.)

> 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..

Are all your other tables present on your 9.6.  I'm puzzled how pg_upgradecluster even works if you were unsuccessful installing postgis version that is same as your old cluster.  It should have not found the library and bowed out with an error.


-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Pietro Rossin
Sent: Thursday, August 10, 2017 10:00 AM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Upgrade both Postgres/postgis and Debian major releases - how to?

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.
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users