pg_upgrade failure due to dependencies

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Nikhil Shetty <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: pg_upgrade failure due to dependencies
Date: Tue, 1 Jul 2025 11:23:55 +0530
Message-ID: <CAFpL5Vw+EK4UN6mHXRqZU+WLyFiSLqPiDAZnA3VX65tNFpyPyA@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pg_upgrade failure due to dependencies
  In-Reply-To: <CAFpL5Vw+EK4UN6mHXRqZU+WLyFiSLqPiDAZnA3VX65tNFpyPyA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox