[postgis-devel] PostGIS upgrade error

[Nikhil Shetty]

Hi Team,

We are upgrading PostgreSQL (11.7 to 13.6) and PostGIS (2.4.4 to 3.2.1) but
we are getting below error during pg_upgrade.

pg_restore: while PROCESSING TOC:

pg_restore: from TOC entry 309; 1259 17730 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('17732'::pg_catalog.oid);



-- For binary upgrade, must preserve pg_type array oid

SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('17731'::pg_catalog.oid);



-- For binary upgrade, must preserve pg_class oids

SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('17730'::pg_catalog.oid);


CREATE VIEW "public"."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("public"."postgis_typmod_dims"("a"."atttypmod"), "sn"."ndims",
2) AS "coord_dimension",

    COALESCE(NULLIF("public"."postgis_typmod_srid"("a"."atttypmod"), 0),
"sr"."srid", 0) AS "srid",

    ("replace"("replace"(COALESCE(NULLIF("upper"("public"."postgis_typmod_type"("a"."atttypmod")),
'GEOMETRY'::"text"), "st"."type", 'GEOMETRY'::"text"), 'ZM'::"text",
''::"text"), 'Z'::"text", ''::"text"))::character varying(30) AS "type"

   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"."conrelid" = "c"."oid") AND
("a"."attnum" = ANY ("st"."conkey")))))

     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" = "c"."oid") AND
("a"."attnum" = ANY ("sn"."conkey")))))

     LEFT JOIN ( SELECT "s"."connamespace",

            "s"."conrelid",

            "s"."conkey",

            ("replace"("replace"("split_part"("s"."consrc", ' = '::"text",
2), ')'::"text", ''::"text"), '('::"text", ''::"text"))::integer AS "srid"

           FROM "pg_constraint" "s"

          WHERE ("s"."consrc" ~~* '%srid(% = %'::"text")) "sr" ON
((("sr"."connamespace" = "n"."oid") AND ("sr"."conrelid" = "c"."oid") AND
("a"."attnum" = ANY ("sr"."conkey")))))

  WHERE (("c"."relkind" = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char",
'f'::"char", 'p'::"char"])) AND (NOT ("c"."relname" =
'raster_columns'::"name")) AND ("t"."typname" = 'geometry'::"name") AND
(NOT "pg_is_other_temp_schema"("c"."relnamespace")) AND
"has_table_privilege"("c"."oid", 'SELECT'::"text"));


-- For binary upgrade, handle extension membership the hard way

ALTER EXTENSION "postgis" ADD VIEW "public"."geometry_columns";



We are creating below links before the upgrade

>>>>>>>:/usr/pgsql-13/lib $ ls -l | grep postgis

lrwxrwxrwx  1 root root      30 Aug 18 02:35 *postgis*-2.4.so ->
/usr/pgsql-13/lib/*postgis*-3.so

-rwxr-xr-x  1 root root 1076312 May 26  2021 *postgis*-3.so

-rwxr-xr-x  1 root root  858608 May 26  2021 *postgis*_raster-3.so

-rwxr-xr-x  1 root root  435648 May 26  2021 *postgis*_sfcgal-3.so

-rwxr-xr-x  1 root root  564928 May 26  2021 *postgis*_topology-3.so

lrwxrwxrwx  1 root root      37 Aug 18 02:35 rt*postgis*-2.4.so ->
/usr/pgsql-13/lib/*postgis*_raster-3.so

Thanks,
Nikhil
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220818/d66e67db/attachment-0001.htm>