pg_upgrade fails for PostGIS custom SRIDs - Mailing list pgsql-hackers

Hi pg-hackers and postgis-hackers, sorry for cross-posting but we
think it really affects how those two products work together. This is
about pg_upgrade failure for tables with custom SRIDs.

PostGIS is an extension that uses a special table called
public.spatial_ref_sys for handling available spatial reference
systems. It holds the numeric SRIDs and textual descriptions of the
coordinate systems. Assume someone has done this on older PG version
(assume PostGIS is already installed):

testgis=# SELECT * FROM spatial_ref_sys WHERE srid = 4283;
 4283 | EPSG      |      4283 |
GEOGCS["GDA94",DATUM["Geocentric_Datum_of_Australia_1994 ... |
+proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,...

-- note the non-standard SRID #4283:
testgis=# CREATE TABLE table4_4283 (
testgis(#     "id" integer NOT NULL,
testgis(#     "name" "text" NOT NULL,
testgis(#     "location" "public"."geography"(Point,4283) NOT NULL
testgis(# );

testgis=# INSERT INTO table4_4283 VALUES (1, 'empty',
ST_GeomFromText('POINT(-71.064544 42.28787)', 4283));

testgis=# select ST_AsText(location) from table4_4283 ;
         st_astext
 POINT(-71.064544 42.28787)
(1 row)
testgis=#

and then one tries to upgrade such an installation (let's say PG13 ->
16, but also any other) using pg_upgrade. What happens is that
pg_upgrade (binary mode) will try to:

1) "/usr/edb/as15/bin/pg_dump" [..] --binary-upgrade --format=custom
--file="[..]/dump/pg_upgrade_dump_16435.custom" 'dbname=testgis'

2) "/usr/edb/as15/bin/pg_restore"[..] --create --exit-on-error
--verbose --dbname template1
"/tmp/as15/pg_upgrade_output.d/20250219T092709.317/dump/pg_upgrade_dump_16435.custom"

and this is going to fail with "Cannot find SRID in spatial_ref_sys"

$ grep -e EXTENSION -e spatial_ref_sys -e table4 pg_upgrade_dump_16435.log
pg_restore: read TOC entry 9 (ID 6) for EXTENSION postgis
pg_restore: read TOC entry 10 (ID 6661) for COMMENT EXTENSION "postgis"
pg_restore: read TOC entry 1240 (ID 715) for TABLE spatial_ref_sys
pg_restore: read TOC entry 1241 (ID 6991) for ACL TABLE "spatial_ref_sys"
pg_restore: read TOC entry 1242 (ID 719) for TABLE table4_4283
pg_restore: read TOC entry 1243 (ID 6404) for CONSTRAINT
spatial_ref_sys spatial_ref_sys_pkey
pg_restore: creating EXTENSION "postgis"
pg_restore: creating COMMENT "EXTENSION "postgis""
pg_restore: creating TABLE "public.spatial_ref_sys"
pg_restore: creating TABLE "public.table4_4283"
pg_restore: from TOC entry 719; 1259 17528 TABLE table4_4283 postgres
pg_restore: error: could not execute query: ERROR:  Cannot find SRID
(4283) in spatial_ref_sys
CREATE TABLE "public"."table4_4283" (
WHERE oid = '"public"."table4_4283"'::pg_catalog.regclass;

The reason is that spatial_ref_sys is empty at that time (while
PostGIS runtime needs to use this data for data projection, it has
hard-coded default SRID 4326 / WGS84 for default geom/geography DDLs
so it works). The table data exists, but it is empty because EXTENSION
upgrade code-path uses PG's binary_upgrade_create_empty_extension()
which seems to hacks around to force-register the EXTENSION, without
proper data initialization (like CREATE EXTENSION would do). See
src/bin/pgdump's dumpExtension() which calls this.

Thoughts on this? Search engines say nothing for "pg_upgrade" "Cannot
find SRID", but apparently PostGIS earlier had a bug report for
something close [1] and I could not locate mentioned there -hackers
discussion. Part of me believes that if the run-time of extensions in
DDL depends on raw table data, then pg_upgrade is to blame, but maybe
it is an anti-pattern and it should be reported to PostGIS instead?

The problem is that from user or extension perspective there's no
place (or option) today to alter anything in the custom dump that
pg_dump (1) does that ends up being used by pg_restore(2), so that
additional script/data/sequence-of-actions would be used DURING the
pg_upgrade.  Potential solution could also be just to somehow hack
pg_upgrade/pg_restore to have chance to interrupt after all
DB/extension initialization is done, but before real user tables were
created and then allow generic command like e.g. psql testgis -f
contrib/postgis-3.4/spatial_ref_sys.sql which would properly load the
data, but that was not tested)

Workaround: easy, just use normal pg_dump/pg_restore (but that might
be slow / without pg_upgrade). Workaround via placing spatial_ref_sys
in template1 does not seem to work.

-J.