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

[Pietro Rossin]

Dear Regina Obe
thanks for your reply and sorry for my unclear explanation.
I try to be more clear:

1) before the debian 9.1 upgrade ve made an upgrade to Postgres 9.4/Postgis
2.3 with jessie backports postgresql-9.4-postgis-2.3 (2.3.1+dfsg-1~bpo8+1).
Then we upgraded debian to 9.1. This automatically install a new postgresql
version 9.6 and a new postgis version (postgresql-9.6-postgis-2.3). This
removes the old 9.4/2.3 libraries and then pg_upgradecluster in 9.6 fails
because during upgrade I think postgres 9.4 looks for postgis-2.3.so in 9.4
path but they are removed by the debian 9.1 upgrade...

2) we made as you suggested without success..
in order:
from debian 8.9 Postgres 9.4 Postgis 2.1 we upgraded to Debian 9.1. Again
auto upgrade to Postgres 9.6 Postgis 2.3. This removes the old libraries.

____________________
The following packages will be *REMOVED*:
  libdapserver7 libgdal1h libgeos-c1 liblwgeom-2.1.4 libsigc++-2.0-0c2a
libspatialite5 perl-modules
*  postgresql-9.4-postgis-2.1*
____________________

Then, we made symlinks 

root at ms11lxarpa-palma:~# ln -s /usr/lib/postgresql/9.6/lib/postgis-2.3.so
/usr/lib/postgresql/9.4/lib/postgis-2.1.so
root at ms11lxarpa-palma:~# ln -s
/usr/lib/postgresql/9.6/lib/postgis_topology-2.3.so
/usr/lib/postgresql/9.4/lib/postgis_topology-2.1.so

root at ms11lxarpa-palma:~# ls -l /usr/lib/postgresql/9.4/lib/postg*
lrwxrwxrwx 1 root root    42 Aug 11 10:51
/usr/lib/postgresql/9.4/lib/postgis-2.1.so ->
/usr/lib/postgresql/9.6/lib/postgis-2.3.so
lrwxrwxrwx 1 root root    51 Aug 11 10:51
/usr/lib/postgresql/9.4/lib/postgis_topology-2.1.so ->
/usr/lib/postgresql/9.6/lib/postgis_topology-2.3.so
-rw-r--r-- 1 root root 67496 Aug 10 14:53
/usr/lib/postgresql/9.4/lib/postgres_fdw.so


We dropped the 9.6 main cluster and tried to upgrade cluster

_____________________________________________
root at ms11lxarpa-palma:~# pg_upgradecluster -v 9.6 9.4 main
Stopping old cluster...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop
operation
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Redirecting start request to systemctl
Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433
Disabling connections to the new cluster during upgrade...
Redirecting start request to systemctl
Roles, databases, schemas, ACLs...
pg_dump: [archiver (db)] query failed: *ERROR:  incompatible library
"/usr/lib/postgresql/9.4/lib/postgis-2.1.so": version mismatch*
DETAIL:  Server is version 9.4, library is version 9.6.
pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod,
a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef,
a.attisdropped, a.attlen, a.attalign, a.attislocal,
pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname,
array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation
<> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation,
pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name)
|| ' ' || pg_catalog.quote_literal(option_value) FROM
pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
    ') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN
pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid =
'17945'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY
a.attrelid, a.attnum
pg_dumpall: pg_dump failed on database "sitarpa", exiting
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
*Error during cluster dumping, removing new cluster*
Redirecting stop request to systemctl
_________________________________________

  

Same error in Qgis if I try to connect to Postgis 9.4
__________________________________
Error:
*incompatible library "/usr/lib/postgresql/9.4/lib/postgis-2.1.so": version
mismatch
DETAIL: Server is version 9.4, library is version 9.6.*


So, is there something wrong in our process??

Do we need to use a dump/restore?

If so, which is the best way to do it so Postgis will be upgraded correctly?

Thanks again
Pietro



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