Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uWTwd-00ETrt-HR for pgsql-admin@arkaria.postgresql.org; Tue, 01 Jul 2025 05:54:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uWTwa-003you-4c for pgsql-admin@arkaria.postgresql.org; Tue, 01 Jul 2025 05:54:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uWTwZ-003yom-Na for pgsql-admin@lists.postgresql.org; Tue, 01 Jul 2025 05:54:12 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uWTwY-0052Xq-0j for pgsql-admin@lists.postgresql.org; Tue, 01 Jul 2025 05:54:12 +0000 Received: by mail-ej1-x629.google.com with SMTP id a640c23a62f3a-ae35f36da9dso596028966b.0 for ; Mon, 30 Jun 2025 22:54:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1751349248; x=1751954048; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=nnTtG3vhi5Rg/mchv3vFORQPaookgaMz5qNhBTeqo3s=; b=X7dQe0SyDOCpoReDO2yYk4gMQM0ufsK0G5ps/jZD6sk1PbQNHP89QvIbdOIrob2aSi V80tfm8SkqSkxymD+Cd3A0b6ojh115NQxyeHQa2gCR+/mEZw5fcGwZ6UQNmGdmT8nNQI ifK/PeWuectZQtHiyNroaNgx2krhODdarFmuQ8Bm5mlmktXD9va2TUvQublw8J2jGKPC xOcyGnSrtR3aoKeBWUNNKVoO28Nq50dQGDl0QmEGmcHyy7y+ItezSIaFZ+nLiW2BrDrL Y9+mEyRSbtOyQCs7Z3pqXMi230ExJ8i4zM3E1USNfMr0g7rAppqAi1OaImO44Sb75Vir VKwQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1751349248; x=1751954048; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=nnTtG3vhi5Rg/mchv3vFORQPaookgaMz5qNhBTeqo3s=; b=U85b2YVuy2irg+fq+L7oknY+yRsZ+Fe/5wcsMydTvEvr57vpDTN0q7vZB8UQLrSjL5 OZa9Oq+8sV/lydFWVhebOgTVZHqGL0cjqS69sHi7w8PP22DFQ9RVoAt/IhJ4VbHx4Uao 3ZkuJXDQzFFWp6D7HmqCYVKBwKiCAB9QZPQirIpdO2CWbkLM7475VrBPXsLcnMCLno/D RPzhVO+SM14RJnTP+RwY+AztGD1VsaRFABoFDFifoVZGVPcJ4vOgn8//ChlPcy96mTRL kZHcSX1f2d+8FfXSJ6H23aBVfeZRL6syporZE4+2V15/ybDccPSw7rkUnVHy3RdCdJGy Ssxg== X-Gm-Message-State: AOJu0Yy+jQIKjeEyNp98gu9XVJp0H0WuPuFxdW4Vb9rpEg1dGi91pzfQ rZuRcyFQC8gUXVOG6b9dBi7y3Lt9nYJuv169L9w/uf3F55licquK7HVe9g0BlORqu8HlY3SQ5eI dvhRZEqLdhpwqth8CM7EzRmgAKrW3IDs0RkIQ X-Gm-Gg: ASbGncsONAk39o6RKCoqCI13tioY5WdlRH7LU6mGpUC+jNmQf4NOfFPYvYnQJYf58MQ rNOttpqmPUVr1OSsDoBLoECmqvaWX13efNgPUyzIwp84X32/hec/m0tLyehg/cM/jTSyDx98mqO ko3uJLLk/bOCHArkpQBP9EydCOrbNNol7GMdgQJqX+Fjs= X-Google-Smtp-Source: AGHT+IFG0WopqlaQBtQuIJyrho72vCBHOpJjCw4c8Y/9v1p6NqxVAGBL8b2PR/gQ2gl63EnfSZUZZ+T3O+LD0T+3H3Y= X-Received: by 2002:a17:906:6a1e:b0:ae3:ab68:4d7a with SMTP id a640c23a62f3a-ae3ab68505cmr185440166b.25.1751349248333; Mon, 30 Jun 2025 22:54:08 -0700 (PDT) MIME-Version: 1.0 From: Nikhil Shetty Date: Tue, 1 Jul 2025 11:23:55 +0530 X-Gm-Features: Ac12FXzlpQ4CJURLCCe9z0V1vLHrwbOvEbUevzz39joKKz52q_8TqNkw4tFDcSw Message-ID: Subject: pg_upgrade failure due to dependencies To: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000d31d220638d7c5a0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d31d220638d7c5a0 Content-Type: text/plain; charset="UTF-8" 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 --000000000000d31d220638d7c5a0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Team,

I wa= s trying an upgrade from=C2=A0PostgreSQL 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 ent= ry 551; 1259 39789310 TABLE table1 db1
pg_restore: error: could not execute <= span style=3D"color:rgb(0,0,255);font-size:0.75rem">query: ERROR: relation "public.spatial_ref_sy= s" does not exist
LI= NE 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 =3D 3857 LIMIT 1
Command was:

<= /span>
<= br>
Tabl= e Structure

CREATE TABLE table1 (
"id1" numeric NOT NULL,
"= ;geom" &= quot;public"."geometry"(Geometry,4326),
"geom_3= 857" &qu= ot;public"."geometry"(Geometry,3857) G= ENERATED ALWA= YS AS = ("public"<= /span>."st_tran= sform"(&= quot;public"."st_intersection"("geom", "public"."st_transform"("public"."st_tileenvelope&quo= t;(0, <= span style=3D"color:rgb(9,134,88);font-size:0.75rem">0, 0), 4326)), 3857)) STORED
);

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

I c= hecked with the PostGIS community and they suggested this needs to be fixed= in pg_upgrade because it is not checking the dependencies in this particul= ar case. Even if it restores publi= c.spatial_ref_sys first, another problem = here is that it is checking for a record in the 'public.spatial_ref_sys' table which d= oes not exist and would be populated during the upgrade link.
<= div dir=3D"auto">
I am ad= ding this in the community to check if there is a way to fix this kind of p= roblem during upgrade?

Thanks,
Nikhil



--000000000000d31d220638d7c5a0--