pg_upgrade fails due to lack of dependencies.
Nikhil Shetty
nikhil.dba04 at gmail.com
Mon Jun 30 10:18:54 PDT 2025
Hi Regina,
Is it possible to add dependency that Jeevan mentioned to postgis so that
pg_dump can verify the dependency during upgrade and proceed with restore
accordingly?
Thanks,
Nikhil
On Mon, Jun 30, 2025 at 7:05 PM Regina Obe <lr at pcorp.us> wrote:
> Okay so this is just related to a pg_upgrade custom restore, so the same
> issue as Jeevan raised.
>
>
>
> Pg_upgrade restores don’t build extensions from scripts like user
> initiated restores do, they build naked extensions, and then reload what
> the db has in them from the backup.
>
>
>
> And I think the order is does these is out of order.
>
>
>
> So still a pg_upgrade issue here.
>
>
>
> *From:* Nikhil Shetty <nikhil.dba04 at gmail.com>
> *Sent:* Monday, June 30, 2025 9:30 AM
> *To:* Regina Obe <lr at pcorp.us>
> *Cc:* Jeevan Chalke <jeevan.chalke at enterprisedb.com>;
> postgis-devel at lists.osgeo.org
> *Subject:* Re: pg_upgrade fails due to lack of dependencies.
>
>
>
> Hi Regina,
>
>
>
> Below is the command that was run by pg_upgrade
>
>
>
> "/usr/pgsql-15/bin/pg_restore" --host /tmp --port 5302 --username postgres
> --create --exit-on-error --verbose --dbname template1
> "/data/pg/13/pg_upgrade_dump_16403.custom"
>
>
>
> Thanks,
>
> Nikhil
>
>
>
>
>
> On Mon, Jun 30, 2025 at 6:27 PM Regina Obe <lr at pcorp.us> wrote:
>
> Yes ST_Transform refers to spatial_ref_sys too.
>
>
>
> This one is a bit odd though I thought during regular pg_resstore that
> CREATE EXTENSION postgis is one of the first steps that happens, so this
> should never happen.
>
>
>
> What commands are you using for pg_restore?
>
>
>
> *From:* Nikhil Shetty <nikhil.dba04 at gmail.com>
> *Sent:* Monday, June 30, 2025 6:14 AM
> *To:* lr at pcorp.us
> *Cc:* Jeevan Chalke <jeevan.chalke at enterprisedb.com>;
> postgis-devel at lists.osgeo.org
> *Subject:* Re: pg_upgrade fails due to lack of dependencies.
>
>
>
> Hi All,
>
>
>
> We also faced a similar issue during pg_upgrade. Table spatial_ref_sys is
> not created and when pg_restore creates a table referring to
> spatial_ref_sys, it will error out.
>
>
>
> I am upgrading PostgreSQL 13 and PostGIS 3.1.2 TO PostgreSQL 15 and
> PostGIS 3.4.2
>
>
>
> 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:
>
>
>
> 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
>
> );
>
>
>
> IS st_transform function is referring to 'spatial_ref_sys'?.
>
>
>
> Thanks,
>
> Nikhil
>
>
>
>
>
>
>
> On Sat, May 17, 2025 at 12:35 AM <lr at pcorp.us> wrote:
>
> Okay seems like an issue with pg_upgrade frankly that it doesn’t also load
> data in an extension it is restoring from the migrated cluster and how the
> heck it doesn’t even load the structure before any user data seems like
> real bug.
>
>
>
> As mentioned I think the reason we never see this issue is because most
> people use 4326 for geography and that I suspect is hard-coded in our
> system to be never checked against spatial_ref_sys cause it’s the default.
>
>
>
> I’ve ticketed issue here - https://trac.osgeo.org/postgis/ticket/5899
>
>
>
> Paul,
>
>
>
> You think there is some way we could just hard-code longlat srids in
> PostGIS code (or check proj sqlite db now that we require newer proj
> anyway) to address, so there is no dependency with spatial_ref_sys when the
> code is confirming the srid is a longlat projection.
>
> I’m assuming that’s why it’s checking spatial_ref_sys in the first place
>
> Only if it can’t find it in our list would it then resort to interrogating
> spatial_ref_sys?
>
>
>
> Thanks,
>
> Regina
>
>
>
> *From:* Jeevan Chalke <jeevan.chalke at enterprisedb.com>
> *Sent:* Friday, May 16, 2025 6:58 AM
> *To:* lr at pcorp.us
> *Cc:* postgis-devel at lists.osgeo.org
> *Subject:* Re: pg_upgrade fails due to lack of dependencies.
>
>
>
> Thank you, Regina
>
>
>
> You’re right -- this dependency alone doesn't fully resolve the issue.
>
> We did receive a complaint about missing data in the spatial_ref_sys
> table, with the error:
> ERROR: Cannot find SRID (4283) in spatial_ref_sys.
>
> By tweaking pg_dump, we managed to dump data for this table *before* any
> user tables accessed it. However, we later encountered a new issue:
> ERROR: relation "public.spatial_ref_sys" does not exist.
>
> As a PostgreSQL developer, this new error came as a surprise. The table in
> question is part of an extension, and that extension does create it. Upon
> further investigation, I discovered that the issue lies in the sort order
> used by pg_dump when dumping extension and user tables, the order isn't
> always correct. Although pg_dump and pg_restore are generally smart
> enough to account for object dependencies, based on entries in the
> pg_depend catalog, this case seems to expose a gap in that logic.
>
> Specifically, since both spatial_ref_sys and geography are part of the
> same extension and have a dependency relationship, this relationship is
> *not* reflected in the pg_depend table. If it were, pg_dump would ensure
> the table is dumped before the dependent type, avoiding this issue. This is
> what I’ve suggested as a fix.
>
> We encountered this problem while using the latest server versions for
> PostgreSQL v11, v13, and v16, along with PostGIS versions 3.2.4 and 3.4.3.
>
> Thanks
>
>
>
> On Thu, May 15, 2025 at 10:03 PM <lr at pcorp.us> wrote:
>
> I think we have a tickets somewhere complaining about issues with
> pg_upgrade and geography, but they have always involved just data missing
> in spatial_ref_sys.
>
> I found this one - https://trac.osgeo.org/postgis/ticket/4405 but these
> cases have always been issues with data not present in spatial_ref_sys
> rather than the table being missing.
>
>
>
> What puzzles me is how this dependency even helps, cause that still
> wouldn’t guarantee data in the spatial_ref_sys table is loaded and
> ultimately I would assume it is checking spatial_ref_sys to make sure 4283
> is a valid longlat projection and will fail if it can’t find it.
>
>
>
> I thought extensions are always created before user tables so should only
> be an issue with the data in them and why spatial_ref_sys doesn’t even
> exist when you load the user table makes no sense.
>
>
>
> I don’t see how putting dependency between geography type and
> spatial_ref_sys would help here and I’ve never seen this particular error
> before.
>
> I suppose it’s possibly most people just use 4326 which geography doesn’t
> need to verify cause that’s the default srid used for geography when none
> is specified so is probably hard-coded in our system and that might explain
> why we’ve never seen this issue.
>
>
>
>
>
>
>
> That said, can you give us the output of your postgis versions from your
> PostgreSQL 11 and PostgreSQL 13
>
>
>
> And also what versions of 11 and 13 you are running.
>
>
>
> Thanks,
>
> Regina
>
>
>
> *From:* Jeevan Chalke <jeevan.chalke at enterprisedb.com>
> *Sent:* Thursday, May 15, 2025 9:42 AM
> *To:* postgis-devel at lists.osgeo.org
> *Subject:* Re: pg_upgrade fails due to lack of dependencies.
>
>
>
> PostGIS hackers/developers,
>
>
>
> Does this addition make sense?
>
>
>
>
>
> On Wed, May 14, 2025 at 11:07 AM Jeevan Chalke <
> jeevan.chalke at enterprisedb.com> wrote:
>
> Hello,
>
>
>
> While working with one of our customers, we observed that pg_upgrade is
> failing with PostGIS.
>
> What is happening is (SQL attached below), the user table "prosch"."MyTab"
> has a column of type "public"."geography", which, in turn, references a
> row in public.spatial_ref_sys. While there is a recorded dependency
> between "procsch"."MyTab" and "public"."geography", no such dependency
> exists between "public"."geography" and public.spatial_ref_sys.
>
> As a result, while the type "public"."geography" is created before the
> user table, spatial_ref_sys is not. Due to sorting (in pg_dump), the user
> table is dumped before spatial_ref_sys, leading to an error during
> restoration.
>
> Since PostgreSQL does not maintain dependencies at the row level (and thus
> not for tables containing such rows), the extension itself should
> explicitly define this dependency. This would ensure that
> public.spatial_ref_sys is dumped before the type "public"."geography",
> preventing issues during dump and restore.
>
> If we manually add the dependency in the source database, pg_upgrade will
> work well. The query to achieve this is:
>
> INSERT INTO pg_depend VALUES (
> 'pg_catalog.pg_type'::regclass::oid, 'public.geography'::regtype::oid, 0,
> 'pg_catalog.pg_class'::regclass::oid,
> 'public.spatial_ref_sys'::regclass::oid, 0,
> 'n');
>
> Can this be considered a valid request and added to the extension SQL file
> itself?
>
> ---
>
> Here is the SQL sequence that yields an error (shared by one of our
> engineers)
>
> CREATE SCHEMA procsch;
> CREATE TABLE "procsch"."MyTab" (
> "id" bigint,
> "loc" "public"."geography"(Point,4283)
> );
>
> INSERT INTO procsch.MyTab VALUES (1, ST_GeomFromText('POINT(152.138672
> -30.689888)', 4283));
>
> $ strings /PATH/bin/pg_dump | grep POSTGIS_spatial
> POSTGIS_spatial_ref_sys
> $
>
> /PATH/bin/initdb -D /srv/13/data/pg_data
> /PATH11/bin/pg_ctl -D /srv/11/data/pg_data -l logfile stop
> /PATH/bin/pg_upgrade --check --link --old-datadir=/srv/11/data/pg_data
> --new-datadir=/srv/13/data/pg_data --old-bindir=/PATH11/bin
> --new-bindir=/PATH/bin --old-port=5432 --new-port=5434
> # for real, no --check:
> /PATH/bin/pg_upgrade --link --old-datadir=/srv/11/data/pg_data
> --new-datadir=/srv/13/data/pg_data --old-bindir=/PATH11/bin
> --new-bindir=/PATH/bin --old-port=5432 --new-port=5434
>
>
> And it did blow up with :
>
>
> [..]
> pg_restore: creating TABLE "procsch.MyTab"
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 371; 1259 17425 TABLE MyTab u1
> pg_restore: error: could not execute query: ERROR: relation
> "public.spatial_ref_sys" does not exist
> LINE 21: "loc" "public"."geography"(Point,4283)
> ^
> QUERY: SELECT proj4text, auth_name, auth_srid, srtext FROM
> public.spatial_ref_sys WHERE srid = 4283 LIMIT 1
> [..]
> CREATE TABLE "procsch"."MyTab" (
> "id" bigint,
> "loc" "public"."geography"(Point,4283)
> );
> [..]
>
>
>
>
>
> Thanks
>
> --
>
>
>
>
>
> *Jeevan Chalke*
>
> *Principal Engineer, Engineering ManagerProduct Development*
>
> *enterprisedb.com <https://www.enterprisedb.com>*
>
>
>
>
> --
>
>
>
>
>
> *Jeevan Chalke*
>
> *Principal Engineer, Engineering ManagerProduct Development*
>
> *enterprisedb.com <https://www.enterprisedb.com>*
>
>
>
>
> --
>
>
>
>
>
> *Jeevan Chalke*
>
> *Principal Engineer, Engineering ManagerProduct Development*
>
> *enterprisedb.com <https://www.enterprisedb.com>*
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20250630/e1f5b50f/attachment-0001.htm>
More information about the postgis-devel
mailing list