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

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Upgrade both Postgres/postgis and Debian major releases - how to?

Regina Obe-2
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:[hidden email]] On Behalf Of Pietro Rossin
Sent: Thursday, August 10, 2017 10:00 AM
To: [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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@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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Upgrade both Postgres/postgis and Debian major releases - how to?

Pietro Rossin
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Upgrade both Postgres/postgis and Debian major releases - how to?

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

On Aug 11, 2017 5:05 AM, "Pietro Rossin" <[hidden email]> wrote:
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
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Upgrade both Postgres/postgis and Debian major releases - how to?

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Upgrade both Postgres/postgis and Debian major releases - how to?

Pietro Rossin
This post was updated on .
Hello
we made a hard upgrade and the result seems ok, but a long errors list is 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Upgrade both Postgres/postgis and Debian major releases - how to?

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

A.

Il 16 Ago 2017 09:47, "Pietro Rossin" <[hidden email]> ha scritto:
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
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Upgrade both Postgres/postgis and Debian major releases - how to?

Pietro Rossin
This post was updated on .
Spatial ref sys table is in public schema with grant select to public..
There isn't difference in permission (AFAIK) between the old and new table...
Thanks Andrea
Loading...