[postgis-users] PostgreSQL 12 and PostGIS

Mon Dec 23 11:26:16 PST 2019

That looks good as it’s not showing the scripts procs need upgrade 

Message any more

Do you by chance have more than one database with PostGIS installed?  You’d need to run it on every database if you do.

That’s the only thing I can think of that might be causing the issue.

It is possible that the update didn’t happen in 2.5.2 and instead in 2.5.3, but I thought it was in 2.5.2.

That said – you could overwrite the views in your 11 cluster with the patched ones.

geometry_columns

https://git.osgeo.org/gitea/postgis/postgis/src/tag/2.5.3/postgis/postgis.sql.in#L5608 (make sure to prefix with the schema you have postgis installed in so doesn’t try to create a new view)

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Bo Guo
Sent: Monday, December 23, 2019 10:54 AM
To: postgis-users at lists.osgeo.org
Subject: Re: [postgis-users] PostgreSQL 12 and PostGIS

Regina,  Since the new cluster was never created so I just upgraded the extension by running your select statement which returned: 

POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" TOPOLOGY RASTER

Not sure if the postgis gets updated though, as my problem persisted with the same error message.

Cheers,

Bo

On 12/22/19 11:03 PM, Regina Obe wrote:

Okay this one is caused by a change in PostgreSQL 12 catalogs.

Fixed in PostGIS 2.5.2 I believe - https://git.osgeo.org/gitea/postgis/postgis/raw/tag/2.5.2/NEWS  (the pg_constraint change)

So to upgrade, what you need to do is

1)    Reinit your new cluster (as I think at this point your new cluster is in an unstable state)

2)     Restart up your old cluster and run the upgrade on it -  SELECT postgis_extensions_upgrade();  - that will bring it up to 2.5.2 and fix the views so they are compatible with 12.

3)    Then do your migration again

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Bo Guo
Sent: Monday, December 23, 2019 12:47 AM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Subject: Re: [postgis-users] PostgreSQL 12 and PostGIS

Hi, Regina,

After sucessful upgrade to the Dev server, I started upgrading the Test server which had the following version profile; 

select version();
PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit

and 

select postgis_full_version();
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="100" (procs need upgrade for use with "110") GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" (core procs from "2.5.1 r17027" need upgrade) TOPOLOGY (topology procs from "2.5.1 r17027" need upgrade) RASTER (raster procs from "2.5.1 r17027" need upgrade)

However the pg_upgradecluster command failed for different reason as shown in the last few lines in the log file.  I thought Dev and Test servers had been identical in terms of OS and Postgres/PostGIS install.  Look like there was some difference...

pg_restore: creating VIEW "postgis.geography_columns"
pg_restore: creating VIEW "postgis.geometry_columns"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 496; 1259 54951 VIEW geometry_columns postgres
pg_restore: error: could not execute query: ERROR:  column s.consrc does not exist
LINE 28:             "replace"("split_part"("s"."consrc", ''''::"text...
                                            ^
HINT:  Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('54953'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('54952'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('54951'::pg_catalog.oid);

CREATE VIEW "postgis"."geometry_columns" AS
 SELECT ("current_database"())::character varying(256) AS "f_table_catalog",
    "n"."nspname" AS "f_table_schema",
    "c"."relname" AS "f_table_name",
    "a"."attname" AS "f_geometry_column",
    COALESCE("postgis"."postgis_typmod_dims"("a"."atttypmod"), "sn"."ndims", 2) AS "coord_dimension",
    COALESCE(NULLIF("postgis"."postgis_typmod_srid"("a"."atttypmod"), 0), "sr"."srid", 0) AS "srid",
    ("replace"("replace"(COALESCE(NULLIF("upper"("postgis"."postgis_typmod_type"("a"."atttypmod")), 'GEOMETRY'::"text"), "st"."$
   FROM (((((("pg_class" "c"
     JOIN "pg_attribute" "a" ON ((("a"."attrelid" = "c"."oid") AND (NOT "a"."attisdropped"))))
     JOIN "pg_namespace" "n" ON (("c"."relnamespace" = "n"."oid")))
     JOIN "pg_type" "t" ON (("a"."atttypid" = "t"."oid")))
     LEFT JOIN ( SELECT "s"."connamespace",
            "s"."conrelid",
            "s"."conkey",
            "replace"("split_part"("s"."consrc", ''''::"text", 2), ')'::"text", ''::"text") AS "type"
           FROM "pg_constraint" "s"
          WHERE ("s"."consrc" ~~* '%geometrytype(% = %'::"text")) "st" ON ((("st"."connamespace" = "n"."oid") AND ("st"."conrel$
     LEFT JOIN ( SELECT "s"."connamespace",
            "s"."conrelid",
            "s"."conkey",
            ("replace"("split_part"("s"."consrc", ' = '::"text", 2), ')'::"text", ''::"text"))::integer AS "ndims"
           FROM "pg_constraint" "s"
          WHERE ("s"."consrc" ~~* '%ndims(% = %'::"text")) "sn" ON ((("sn"."connamespace" = "n"."oid") AND ("sn"."conrelid" = "$
     LEFT JOIN ( SELECT "s"."connamespace",
            "s"."conrelid",
            "s"."conkey",
            ("replace"("replace"("split_part"("s"."consrc", ' = '::"text", 2), ')'::"text", ''::"text"), '('::"text", ''::"text$
           FROM "pg_constraint" "s"
          WHERE ("s"."consrc" ~~* '%srid(% = %'::"text")) "sr" ON ((("sr"."connamespace" = "n"."oid") AND ("sr"."conrelid" = "c$
  WHERE (("c"."relkind" = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"])) AND (NOT ("c"."relname"$

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "postgis" ADD VIEW "postgis"."geometry_columns";

On 12/22/19 9:11 PM, Bo Guo wrote:

Yeh!  Thanks you, Regina!

On 12/22/19 8:54 PM, Regina Obe wrote:

Okay I ran into that issue too.  

It happens if you have PostGIS not installed in public.

I have the issue ticketed here – I thought I had fixed this already in 3.0.1 (not yet released yet), but evidentally I haven’t or forgot to close this ticket.

https://trac.osgeo.org/postgis/ticket/4555

The workaround is to do this:

CREATE EXTENSION postgis_raster FROM unpackaged VERSION "3.0.0" SCHEMA <schema where you installed postgis>;

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Bo Guo
Sent: Sunday, December 22, 2019 10:36 PM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Subject: Re: [postgis-users] PostgreSQL 12 and PostGIS

Thanks, Regina!  I am running PostgeSQL 12 now!

However, when I ran SELECT PostGIS_Extensions_Upgrade(); the second time, I have the following msg. 

NOTICE: Extension postgis_sfcgal is not available or not packagable for some reason 

NOTICE: Packaging extension postgis_raster

WARNING: 'postgis.gdal_datapath' is already set and cannot be changed until you reconnect 

WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect 

WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect 

ERROR: function public.st_srid(geometry) does not exist 

LINE 1: ..._makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0, public.ST_... ^ 

HINT: No function matches the given name and argument types. You might need to add explicit type casts. 

QUERY: SELECT public.ST_makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0, public.ST_SRID('POINT(0 0)'::geometry)) 

CONTEXT: SQL statement "CREATE EXTENSION postgis_raster FROM unpackaged" 

PL/pgSQL function postgis_extensions_upgrade() line 48 at EXECUTE 

SQL state: 42883

On 12/22/19 8:09 PM, Regina Obe wrote:

NO NO  don’t do that.  Those set of libraries aren’t compatible with each other.

The PostGIS 3.0 and 2.5 from 12 are compatible with each other however.

So 

What you want to do is

cp /usr/lib/postgresql/12/lib/postgis-3.so /usr/lib/postgresql/12/lib/postgis-2.5.so

cp /usr/lib/postgresql/12/lib/postgis_raster-3.so /usr/lib/postgresql/12/lib/rtpostgis-2.5.so

cp /usr/lib/postgresql/12/lib/postgis_topology-3.so /usr/lib/postgresql/12/lib/postgis_topology-2.5.so

Then after the upgrade in each of your databases run:

SELECT PostGIS_Extensions_Upgrade();

And then run it again to rebundle the raster into it’s own extension

SELECT PostGIS_Extensions_Upgrade();

Then if you don’t need raster, you can 

DROP EXTENSION postgis_raster;

Once you are done upgrading your databases, you can delete thr *-2.5.so files you created in the 12 cluster.

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Bo Guo
Sent: Sunday, December 22, 2019 10:01 PM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Subject: Re: [postgis-users] PostgreSQL 12 and PostGIS

Thanks, Regina,

I copied the three 2.5 lib files from /usr/lib/postgresql/11/lib to /usr/lib/postgresql/12/lib:

guob at yuma:/usr/lib/postgresql/12/lib$ ls -al *post*
-rw-r--r-- 1 root root  456832 Dec 22 19:49 postgis-2.5.so
-rw-r--r-- 1 root root 1351536 Oct 28 21:24 postgis-3.so
-rw-r--r-- 1 root root  847424 Oct 28 21:24 postgis_raster-3.so
-rw-r--r-- 1 root root  364224 Dec 22 19:49 postgis_topology-2.5.so
-rw-r--r-- 1 root root  569152 Oct 28 21:24 postgis_topology-3.so
-rw-r--r-- 1 root root  100264 Nov 12 03:33 postgres_fdw.so
-rw-r--r-- 1 root root  399136 Dec 22 19:50 rtpostgis-2.5.so

However, the three lib files are still in the loadable_libraries.txt file.

Bo

On 12/22/19 7:42 PM, Regina Obe wrote:

Bo,

Do you have the postgis-2.5.so, rtpostgis-2.5.so, postgis_topology-2.5.so
files in

/usr/lib/postgresql/11/lib

And

/usr/lib/postgresql/12/lib

Those are the files it's complaining about. 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Bo Guo
Sent: Sunday, December 22, 2019 9:16 PM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Subject: Re: [postgis-users] PostgreSQL 12 and PostGIS

Regina and Stefan,
I might have run into a similar issues today as I was  trying to upgrade
PostgreSQL 11 w/ PostGIS 2.5 to PostgreSQL 12 w/ PostGIS 3.0.
The pg_upgradecluster command (sudo pg_upgradecluster -m upgrade 11 main)
failed its check for the following required libraries:
    could not load library "$libdir/postgis-2.5": ERROR:  could not access
file "$libdir/postgis-2.5": No such file or directory

    could not load library "$libdir/rtpostgis-2.5": ERROR:  could not access
file "$libdir/rtpostgis-2.5": No such file or directory

    could not load library "$libdir/postgis_topology-2.5": ERROR:  could not
access file "$libdir/postgis_topology-2.5": No such file or directory

I have postgis-2.5 in the /usr/share/postgresql/11/contrib/ folder AND
/usr/share/postgresql/12/contrib/ folder.  I also have postgis-3.0 under
/usr/share/postgresql/12/contrib/.  
Thanks for your help!
Bo
Gistic Research, Inc.

On 10/4/19 8:16 AM, Regina Obe wrote:
Stefan,

Which distro do you get your PostgreSQL 12 from.  I’m assuming
apt.postgresql.org

If so you should report on their mailing list, as that would be a packaging
bug,not a PostGIS one and no one on the PostGIS project is on the apt team.
https://www.postgresql.org/list/pgsql-pkg-debian/

Thanks,
Regina

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Stefan Wolf
Sent: Friday, October 4, 2019 6:19 AM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Subject: [postgis-users] PostgreSQL 12 and PostGIS

Today I’ve installed the brand new PostgreSQL 12  on Ubuntu 18.04

“apt-get install postgis”  will install PostGIS 2.5.2, but the  PATH is
wrong:  /usr/share/postgresql/11/…

So CREATE EXTENSION postgis will fail.

Greetings from Berlin
-Stefan Wolf-

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Regards,

Bo Guo, PhD, PE
President
Gistic Research, Inc.
2033 E Warner Rd Ste 105
Tempe, AZ 85284
www.gisticinc.com <http://www.gisticinc.com> 
www.youtube.com/linearbench <http://www.youtube.com/linearbench> 
Office: 480-656-9962
Cell: 602-570-4697

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Regards,

Bo Guo, PhD, PE
President
Gistic Research, Inc.
2033 E Warner Rd Ste 105
Tempe, AZ 85284
www.gisticinc.com <http://www.gisticinc.com> 
www.youtube.com/linearbench <http://www.youtube.com/linearbench> 
Office: 480-656-9962
Cell: 602-570-4697

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Regards,

Bo Guo, PhD, PE
President
Gistic Research, Inc.
2033 E Warner Rd Ste 105
Tempe, AZ 85284
www.gisticinc.com <http://www.gisticinc.com> 
www.youtube.com/linearbench <http://www.youtube.com/linearbench> 
Office: 480-656-9962
Cell: 602-570-4697

-- 
Regards,

Bo Guo, PhD, PE
President
Gistic Research, Inc.
2033 E Warner Rd Ste 105
Tempe, AZ 85284
www.gisticinc.com <http://www.gisticinc.com> 
www.youtube.com/linearbench <http://www.youtube.com/linearbench> 
Office: 480-656-9962
Cell: 602-570-4697

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20191223/97e5b46f/attachment.html>