<div dir="ltr">Hi Team,<div><br></div><div>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.</div><div><br></div><div><p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">pg_restore: while PROCESSING TOC:</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">pg_restore: from TOC entry 309; 1259 17730 VIEW geometry_columns postgres</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">pg_restore: error: could not execute query: ERROR:<span class="gmail-Apple-converted-space"> </span>column s.consrc does not exist</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">LINE 28: <span class="gmail-Apple-converted-space"> </span>"replace"("split_part"("s"."consrc", ''''::"text...</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>^</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">HINT:<span class="gmail-Apple-converted-space"> </span>Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">Command was:</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">-- For binary upgrade, must preserve pg_type oid</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('17732'::pg_catalog.oid);</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0);min-height:15px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0);min-height:15px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">-- For binary upgrade, must preserve pg_type array oid</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('17731'::pg_catalog.oid);</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0);min-height:15px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0);min-height:15px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">-- For binary upgrade, must preserve pg_class oids</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('17730'::pg_catalog.oid);</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0);min-height:15px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">CREATE VIEW "public"."geometry_columns" AS</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>SELECT ("current_database"())::character varying(256) AS "f_table_catalog",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"n"."nspname" AS "f_table_schema",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"c"."relname" AS "f_table_name",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"a"."attname" AS "f_geometry_column",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>COALESCE("public"."postgis_typmod_dims"("a"."atttypmod"), "sn"."ndims", 2) AS "coord_dimension",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>COALESCE(NULLIF("public"."postgis_typmod_srid"("a"."atttypmod"), 0), "sr"."srid", 0) AS "srid",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>("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"</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>FROM (((((("pg_class" "c"</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>JOIN "pg_attribute" "a" ON ((("a"."attrelid" = "c"."oid") AND (NOT "a"."attisdropped"))))</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>JOIN "pg_namespace" "n" ON (("c"."relnamespace" = "n"."oid")))</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>JOIN "pg_type" "t" ON (("a"."atttypid" = "t"."oid")))</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>LEFT JOIN ( SELECT "s"."connamespace",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"s"."conrelid",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"s"."conkey",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"replace"("split_part"("s"."consrc", ''''::"text", 2), ')'::"text", ''::"text") AS "type"</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>FROM "pg_constraint" "s"</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>WHERE ("s"."consrc" ~~* '%geometrytype(% = %'::"text")) "st" ON ((("st"."connamespace" = "n"."oid") AND ("st"."conrelid" = "c"."oid") AND ("a"."attnum" = ANY ("st"."conkey")))))</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>LEFT JOIN ( SELECT "s"."connamespace",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"s"."conrelid",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"s"."conkey",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>("replace"("split_part"("s"."consrc", ' = '::"text", 2), ')'::"text", ''::"text"))::integer AS "ndims"</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>FROM "pg_constraint" "s"</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>WHERE ("s"."consrc" ~~* '%ndims(% = %'::"text")) "sn" ON ((("sn"."connamespace" = "n"."oid") AND ("sn"."conrelid" = "c"."oid") AND ("a"."attnum" = ANY ("sn"."conkey")))))</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>LEFT JOIN ( SELECT "s"."connamespace",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"s"."conrelid",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>"s"."conkey",</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>("replace"("replace"("split_part"("s"."consrc", ' = '::"text", 2), ')'::"text", ''::"text"), '('::"text", ''::"text"))::integer AS "srid"</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>FROM "pg_constraint" "s"</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>WHERE ("s"."consrc" ~~* '%srid(% = %'::"text")) "sr" ON ((("sr"."connamespace" = "n"."oid") AND ("sr"."conrelid" = "c"."oid") AND ("a"."attnum" = ANY ("sr"."conkey")))))</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space"> </span>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"));</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0);min-height:15px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">-- For binary upgrade, handle extension membership the hard way</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">ALTER EXTENSION "postgis" ADD VIEW "public"."geometry_columns";</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0);min-height:15px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p>
<br class="gmail-Apple-interchange-newline"></div><div>We are creating below links before the upgrade </div><div><br></div><div><p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">>>>>>>>:/usr/pgsql-13/lib $ ls -l | grep postgis</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">lrwxrwxrwx<span class="gmail-Apple-converted-space"> </span>1 root root<span class="gmail-Apple-converted-space"> </span>30 Aug 18 02:35 </span><span style="font-variant-ligatures:no-common-ligatures;color:rgb(180,36,25)"><b>postgis</b></span><span style="font-variant-ligatures:no-common-ligatures">-<a href="http://2.4.so">2.4.so</a> -> /usr/pgsql-13/lib/</span><span style="font-variant-ligatures:no-common-ligatures;color:rgb(180,36,25)"><b>postgis</b></span><span style="font-variant-ligatures:no-common-ligatures">-3.so</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">-rwxr-xr-x<span class="gmail-Apple-converted-space"> </span>1 root root 1076312 May 26<span class="gmail-Apple-converted-space"> </span>2021 </span><span style="font-variant-ligatures:no-common-ligatures;color:rgb(180,36,25)"><b>postgis</b></span><span style="font-variant-ligatures:no-common-ligatures">-3.so</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">-rwxr-xr-x<span class="gmail-Apple-converted-space"> </span>1 root root<span class="gmail-Apple-converted-space"> </span>858608 May 26<span class="gmail-Apple-converted-space"> </span>2021 </span><span style="font-variant-ligatures:no-common-ligatures;color:rgb(180,36,25)"><b>postgis</b></span><span style="font-variant-ligatures:no-common-ligatures">_raster-3.so</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">-rwxr-xr-x<span class="gmail-Apple-converted-space"> </span>1 root root<span class="gmail-Apple-converted-space"> </span>435648 May 26<span class="gmail-Apple-converted-space"> </span>2021 </span><span style="font-variant-ligatures:no-common-ligatures;color:rgb(180,36,25)"><b>postgis</b></span><span style="font-variant-ligatures:no-common-ligatures">_sfcgal-3.so</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">-rwxr-xr-x<span class="gmail-Apple-converted-space"> </span>1 root root<span class="gmail-Apple-converted-space"> </span>564928 May 26<span class="gmail-Apple-converted-space"> </span>2021 </span><span style="font-variant-ligatures:no-common-ligatures;color:rgb(180,36,25)"><b>postgis</b></span><span style="font-variant-ligatures:no-common-ligatures">_topology-3.so</span></p>
<p style="margin:0px;font-stretch:normal;font-size:13px;line-height:normal;font-family:Menlo;color:rgb(0,0,0)"><span style="font-variant-ligatures:no-common-ligatures">lrwxrwxrwx<span class="gmail-Apple-converted-space"> </span>1 root root<span class="gmail-Apple-converted-space"> </span>37 Aug 18 02:35 rt</span><span style="font-variant-ligatures:no-common-ligatures;color:rgb(180,36,25)"><b>postgis</b></span><span style="font-variant-ligatures:no-common-ligatures">-<a href="http://2.4.so">2.4.so</a> -> /usr/pgsql-13/lib/</span><span style="font-variant-ligatures:no-common-ligatures;color:rgb(180,36,25)"><b>postgis</b></span><span style="font-variant-ligatures:no-common-ligatures">_raster-3.so</span></p></div><div><br></div><div>Thanks,</div><div>Nikhil</div></div>