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

Pietro Rossin pietro.rossin at arpa.fvg.it
Fri Aug 11 02:16:27 PDT 2017

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
root at ms11lxarpa-palma:~# ln -s

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 ->
lrwxrwxrwx 1 root root    51 Aug 11 10:51
/usr/lib/postgresql/9.4/lib/postgis_topology-2.1.so ->
-rw-r--r-- 1 root root 67496 Aug 10 14:53

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
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
*incompatible library "/usr/lib/postgresql/9.4/lib/postgis-2.1.so": version
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

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.

More information about the postgis-users mailing list