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

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

[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@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@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@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@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

[Pietro Rossin]

I guess this is the best way to go...

https://postgis.net/docs/postgis_installation.html#hard_upgrade

--
View this message in context: http://postgis.17.x6.nabble.com/Upgrade-both-Postgres-postgis-and-Debian-major-releases-how-to-tp5011462p5011465.html

[James Klassen]

I ended up doing a pg_dump on Debian 8.9 and a pg_restore on Debian 9 when I upgraded.

Debian makes an effort to keep the old PosrgreSQL version installed after the OS upgrade so people can switch to the new DB version later.  Unfortunately, this time it appears they forgot about keeping the old PostGIS version along with it.  (And it doesn't seem trivial to just copy the old postgis library back because of dependancy issues).

[Pietro Rossin]

Yes we are trying this way with what described in
https://postgis.net/docs/postgis_installation.html#hard_upgrade

But it's a strange thing that the postgres9.4/postgis2.3 extension is
removed too...

So for example a server with 4 different postgres services (9.4 and 9.6)
with the same postgis extension cannot coexist.

Thanks

--
View this message in context: http://postgis.17.x6.nabble.com/Upgrade-both-Postgres-postgis-and-Debian-major-releases-how-to-tp5011462p5011467.html

[Pietro Rossin]

Hello
we made a hard upgrade and the result seems ok, but a long errors list
reported by the process..
In order, in debian 8.9/postgresql 9.4.12/postgis 2.3 we made a custom dump

pg_dump -Fc -b -v -f "/somepath/olddb.backup" olddb

then we upgraded debian to 9, postgresql to 9.6/postgis 2.3

and restore
perl some_path_to/postgis_restore.pl "/somepath/olddb.backup" | psql newdb
2> /tmp/errors.txt

The restore process came to the end and in errors.txt we find many errors
like:

ERROR: GetProj4StringSPI: Cannot find SRID (3045) in spatial_ref_sys

or other srid..

So I tried to compare what is in both db (old/new) and
public.spatial_ref_sys has these srid, the public.geometry_columns content
is the same in old/new view and the number of tables/records is the same
(except some difference in count due to vacuum execution)

So for me the dump/restore process seems good this way, but what about these
srid errors?

Thanks again
Pietro

--
View this message in context: http://postgis.17.x6.nabble.com/Upgrade-both-Postgres-postgis-and-Debian-major-releases-how-to-tp5011462p5011474.html

[Andrea Peri]

Perhaps some grant rule change between 9.4 and 9.6 that give unseeable the spatialrefsys to other tables ?

A.

[Pietro Rossin]

Spatial ref sys table is in public with grant select to public..
The isn't difference in permission (AFAIK) between the old and new table...
Thanks Andrea

--
View this message in context: http://postgis.17.x6.nabble.com/Upgrade-both-Postgres-postgis-and-Debian-major-releases-how-to-tp5011462p5011476.html