Problems migrating Postgis 2.1 to 2.4

[davidperez]

We are using currntly Postgres 9.3 and Postgis 2.1.8, under CentOS 7. we would like to migrate to Postgres 11 and Postgis 2.5.2.

Steps we follow:

• Compile Postgis 2.4.7 to Posgres 9.3
• Install it
• Migrate Postgis from 2.1.8 to 2.4.7 with ALTER EXTENSION postgis UPDATE
  • It works ok but we receive this warning: WARNING: 'postgis.backend' is already set and cannot be changed until you reconnect CONTEXT: sentencia SQL: «SELECT postgis_lib_version()»

function PL/pgSQL postgis_major_version_check() in line 21 of SQL sentence {code}

• Migrate with pg_upgrade from Postgres 9.3 with pg_upgrade. It works ok

Wiht the command:

psql \dx+

We see some functions are missing:

clase de operadores «brin_geography_inclusion_ops» para el método de acceso «brin» clase de operadores «brin_geometry_inclusion_ops_2d» para el método de acceso «brin» clase de operadores «brin_geometry_inclusion_ops_3d» para el método de acceso «brin» clase de operadores «brin_geometry_inclusion_ops_4d» para el método de acceso «brin» familia de operadores brin_geography_inclusion_ops para el método de acceso brin familia de operadores brin_geometry_inclusion_ops_2d para el método de acceso brin familia de operadores brin_geometry_inclusion_ops_3d para el método de acceso brin familia de operadores brin_geometry_inclusion_ops_4d para el método de acceso brin función contains_2d(box2df,box2df) función contains_2d(box2df,geometry) función contains_2d(geometry,box2df) función geog_brin_inclusion_add_value(internal,internal,internal,internal) función geom2d_brin_inclusion_add_value(internal,internal,internal,internal) función geom3d_brin_inclusion_add_value(internal,internal,internal,internal) función geom4d_brin_inclusion_add_value(internal,internal,internal,internal) función is_contained_2d(box2df,box2df) función is_contained_2d(box2df,geometry) función is_contained_2d(geometry,box2df) función overlaps_2d(box2df,box2df) función overlaps_2d(box2df,geometry) función overlaps_2d(geometry,box2df) función overlaps_geog(geography,gidx) función overlaps_geog(gidx,geography) función overlaps_geog(gidx,gidx) función overlaps_nd(geometry,gidx) función overlaps_nd(gidx,geometry) función overlaps_nd(gidx,gidx) operador ~(box2df,box2df) operador @(box2df,box2df) operador &&(box2df,box2df) operador ~(box2df,geometry) operador @(box2df,geometry) operador &&(box2df,geometry) operador &&(geography,gidx) operador ~(geometry,box2df) operador @(geometry,box2df) operador &&(geometry,box2df) operador &&&(geometry,gidx) operador &&(gidx,geography) operador &&&(gidx,geometry) operador &&(gidx,gidx)

Another sympton is this: select postgis_full_version() returns

2.4.7 r17330" PGSQL="93" (procs need upgrade for use with "110") GEOS="3.7.1-CAP I-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/2 5" LIBXML="2.9.1" LIBJSON="0.11" RASTER

If we do:

DROP EXTENSION POSTGIS CREATE EXTENSION POSTGIS

then: POSTGIS="2.4.7 r17330" PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15

August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER

But we cannot do it in databases with geometric data.

Migration to 2.5.2 also fails: ​https://gis.stackexchange.com/questions/327509/migration-in-postgresql-11-from-postgis-2-4-to-2-5-fails

I think this is a bug in Postgis migration.

[davidperez]

This makes me thing that this is a known problem: ​https://gist.github.com/genslein/e9ed84c3698dfff2aaef54b393efd8de

[robe]

The warning about postgis.backend is harmless -it happens because both postgis-2.1 and postgis-2.4.so are live at same time and conflicting. It can be ignored.

My suggestion is not try to compile 2.4 for PostgreSQL 9.3.

I've done a similar migration on CentOS going from 9.3 2.1 -> 11 2.5 and 9.2->9.6

Details of my steps here: ​https://www.bostongis.com/blog/index.php?/archives/273-Using-pg_upgrade-to-upgrade-PostgreSQL-9.3-PostGIS-2.1-to-PostgreSQL-11-2.5-on-Yum.html

The basic idea is you

1) Install the new cluster PostgreSQL 11 with new PostGIS 2.5

2) you symlink the postgis-2.5 -> postgis-2.1 in new cluster - this is so when pg_upgrade goes looking for 2.1, it will find it. It won't care it's 2.5 as long as the 2.5 has all the functions that it's SQL functions of 2.1 had.

3) Since you are running 2.1, you need to first drop the functions in your old cluster since the backing functions were removed in 2.2 and these present would cause pg_upgrade to fail.

ALTER EXTENSION postgis DROP FUNCTION GeomFromEWKB(bytea);
DROP FUNCTION GeomFromEWKB(bytea);
ALTER EXTENSION postgis DROP FUNCTION ST_GeomFromEWKB(bytea);
DROP FUNCTION ST_GeomFromEWKB(bytea);

4) Do your pg_upgrade as usual 5) Then on new cluster after pg_upgrade do the

ALTER EXTENSION postgis UPDATE;

The reason why installing the newer PostGIS in your old cluster and then pg_upgrading didn't work is because brin was introduced in 9.4, and the 2.5 you installed therefore lackaged those when installed in 9.3 and since you were already at 2.5 by the time you pg upgraded, it didn't do anything.

Anyway I think we had fixed this issue already in 2.5 and we can't really backport it to 2.4 because as I recall it was a bit involved.