pg_upgrade failure due to dependencies

From: Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: pg_upgrade failure due to dependencies
Date: 2025-07-01 05:53:55
Message-ID: CAFpL5Vw+EK4UN6mHXRqZU+WLyFiSLqPiDAZnA3VX65tNFpyPyA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi Team,

I was trying an upgrade from PostgreSQL 13 and PostGIS 3.1.2 TO PostgreSQL
15 and PostGIS 3.4.2 and got below error

pg_restore: creating TABLE "table1"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 551; 1259 39789310 TABLE table1 db1
pg_restore: error: could not execute query: ERROR: relation
"public.spatial_ref_sys" does not exist
LINE 1: ...LECT proj4text, auth_name, auth_srid, srtext FROM public.spa...
^
QUERY: SELECT proj4text, auth_name, auth_srid, srtext FROM
public.spatial_ref_sys
WHERE srid = 3857 LIMIT 1
Command was:

Table Structure

CREATE TABLE table1 (
"id1" numeric NOT NULL,
"geom" "public"."geometry"(Geometry,4326),
"geom_3857" "public"."geometry"(Geometry,3857) GENERATED ALWAYS AS ("public"
."st_transform"("public"."st_intersection"("geom", "public"."st_transform"(
"public"."st_tileenvelope"(0, 0, 0), 4326)), 3857)) STORED
);

The st_transform function used in above table definition uses
public.spatial_ref_sys
to check the SRID but since public.spatial_ref_sys is not restored yet, the
table creation for table1 fails

I checked with the PostGIS community and they suggested this needs to be
fixed in pg_upgrade because it is not checking the dependencies in this
particular case. Even if it restores public.spatial_ref_sys first,
another problem
here is that it is checking for a record in the 'public.spatial_ref_sys'
table which does not exist and would be populated during the upgrade link.

I am adding this in the community to check if there is a way to fix this
kind of problem during upgrade?

Thanks,
Nikhil

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2025-07-01 09:37:06 Re: pg_upgrade failure due to dependencies
Previous Message vrms 2025-06-30 18:31:09 Re: Replication and Switchover