[PostGIS] #5899: pg_upgrade fails restore geography because spatial_ref_sys does not exist

[PostGIS]

#5899: pg_upgrade fails restore geography because spatial_ref_sys does not exist
---------------------+---------------------------
 Reporter:  robe     |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS 3.2.9
Component:  postgis  |    Version:  3.4.x
 Keywords:           |
---------------------+---------------------------
 As mentioned in https://lists.osgeo.org/pipermail/postgis-
 devel/2025-May/030532.html  if a user uses a spatial ref sys other than
 4326 for geography type, restore might fail.

 Example from mail list thread


 {{{
 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)
 );
 [..]

 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5899>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.