v2-0001-pg_dump-Restore-extension-config-table-data-befor.patch

From 46ab846425e8ae62f3cc4dec6b88799c217a20f8 Mon Sep 17 00:00:00 2001
From: Jimmy Angelakos <jimmy@pgedge.com>
Date: Fri, 20 Mar 2026 16:04:45 +0000
Subject: [PATCH v2] pg_dump: Restore extension config table data before user
 objects during binary upgrade

pg_upgrade uses pg_dump --schema-only --binary-upgrade, which excludes
all table data including extension configuration tables registered via
pg_extension_config_dump(). Since binary_upgrade_create_empty_extension()
does not populate these tables, any user table whose CREATE TABLE
triggers validation against config data will fail.

For example, PostGIS tables with SRID-constrained geometry/geography
columns fail because spatial_ref_sys is empty during schema restore.

Fix by introducing a new dump object type DO_EXTENSION_DATA that dumps
extension config table data into SECTION_PRE_DATA during binary upgrade.
This puts the data restore between extension creation and user object
creation, allowing DDL-time validation to succeed. The data is
scaffolding: it is overwritten when pg_upgrade transfers the old
cluster's data files to the new cluster.

This is not PostGIS-specific and applies to any extension that registers
config tables via pg_extension_config_dump() where that data is needed
for DDL-time validation.
---
 src/bin/pg_dump/pg_backup_archiver.c          |  2 +
 src/bin/pg_dump/pg_dump.c                     | 82 ++++++++++++++++++-
 src/bin/pg_dump/pg_dump.h                     |  1 +
 src/bin/pg_dump/pg_dump_sort.c                |  7 ++
 src/test/modules/test_pg_dump/t/001_base.pl   |  1 -
 .../test_pg_dump/test_pg_dump--1.0.sql        |  1 +
 6 files changed, 89 insertions(+), 5 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index fecf6f2d1ce..8b2acbe4936 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3310,6 +3310,7 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
 		 */
 		if (strcmp(te->desc, "SEQUENCE SET") == 0 ||
 			strcmp(te->desc, "BLOB") == 0 ||
+			strcmp(te->desc, "EXTENSION DATA") == 0 ||
 			strcmp(te->desc, "BLOB METADATA") == 0 ||
 			(strcmp(te->desc, "ACL") == 0 &&
 			 strncmp(te->tag, "LARGE OBJECT", 12) == 0) ||
@@ -3351,6 +3352,7 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
 		if (!(ropt->sequence_data && strcmp(te->desc, "SEQUENCE SET") == 0) &&
 			!(ropt->binary_upgrade &&
 			  (strcmp(te->desc, "BLOB") == 0 ||
+			   strcmp(te->desc, "EXTENSION DATA") == 0 ||
 			   strcmp(te->desc, "BLOB METADATA") == 0 ||
 			   (strcmp(te->desc, "ACL") == 0 &&
 				strncmp(te->tag, "LARGE OBJECT", 12) == 0) ||
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d34240073bb..e3cd4c8e8d4 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -352,6 +352,7 @@ static void addConstrChildIdxDeps(DumpableObject *dobj, const IndxInfo *refidx);
 static void getDomainConstraints(Archive *fout, TypeInfo *tyinfo);
 static void getTableData(DumpOptions *dopt, TableInfo *tblinfo, int numTables, char relkind);
 static void makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo);
+static void makeExtensionDataInfo(DumpOptions *dopt, TableInfo *tbinfo);
 static void buildMatViewRefreshDependencies(Archive *fout);
 static void getTableDataFKConstraints(void);
 static void determineNotNullFlags(Archive *fout, PGresult *res, int r,
@@ -2864,6 +2865,8 @@ dumpTableData(Archive *fout, const TableDataInfo *tdinfo)
 	char	   *tdDefn = NULL;
 	char	   *copyStmt;
 	const char *copyFrom;
+	const char *description = "TABLE DATA";
+	teSection	section = SECTION_DATA;
 
 	/* We had better have loaded per-column details about this table */
 	Assert(tbinfo->interesting);
@@ -2910,6 +2913,16 @@ dumpTableData(Archive *fout, const TableDataInfo *tdinfo)
 		copyStmt = NULL;
 	}
 
+	/*
+	 * Extension config table data goes into SECTION_PRE_DATA so it is
+	 * available before user tables that may need it for validation.
+	 */
+	if (tdinfo->dobj.objType == DO_EXTENSION_DATA)
+	{
+		description = "EXTENSION DATA";
+		section = SECTION_PRE_DATA;
+	}
+
 	/*
 	 * Note: although the TableDataInfo is a full DumpableObject, we treat its
 	 * dependency on its table as "special" and pass it to ArchiveEntry now.
@@ -2923,8 +2936,8 @@ dumpTableData(Archive *fout, const TableDataInfo *tdinfo)
 						  ARCHIVE_OPTS(.tag = tbinfo->dobj.name,
 									   .namespace = tbinfo->dobj.namespace->dobj.name,
 									   .owner = tbinfo->rolname,
-									   .description = "TABLE DATA",
-									   .section = SECTION_DATA,
+									   .description = description,
+									   .section = section,
 									   .createStmt = tdDefn,
 									   .copyStmt = copyStmt,
 									   .deps = &(tbinfo->dobj.dumpId),
@@ -3105,6 +3118,48 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo)
 	tbinfo->interesting = true;
 }
 
+/*
+ * makeExtensionDataInfo --- create TableDataInfo for extension config table
+ *
+ * This is used during binary upgrades to ensure extension configuration
+ * table data is dumped early (before user tables that may depend on it).
+ * For example, PostGIS's spatial_ref_sys must be populated before any
+ * table with geometry(Point, 27700) can be created due to SRID validation.
+ */
+static void
+makeExtensionDataInfo(DumpOptions *dopt, TableInfo *tbinfo)
+{
+	TableDataInfo *tdinfo;
+
+	/* Already have a data object? */
+	if (tbinfo->dataObj != NULL)
+		return;
+
+	/*
+	 * Caller ensures that this is only called for RELKIND_RELATION.
+	 */
+
+	/* OK, create the data object */
+	tdinfo = (TableDataInfo *) pg_malloc(sizeof(TableDataInfo));
+
+	tdinfo->dobj.objType = DO_EXTENSION_DATA;
+
+	tdinfo->dobj.catId.tableoid = 0;
+	tdinfo->dobj.catId.oid = tbinfo->dobj.catId.oid;
+	AssignDumpId(&tdinfo->dobj);
+	tdinfo->dobj.name = tbinfo->dobj.name;
+	tdinfo->dobj.namespace = tbinfo->dobj.namespace;
+	tdinfo->tdtable = tbinfo;
+	tdinfo->filtercond = NULL;
+	addObjectDependency(&tdinfo->dobj, tbinfo->dobj.dumpId);
+
+	/* Mark that this object contains data */
+	tdinfo->dobj.components |= DUMP_COMPONENT_DATA;
+
+	tbinfo->dataObj = tdinfo;
+	tbinfo->interesting = true;
+}
+
 /*
  * The refresh for a materialized view must be dependent on the refresh for
  * any materialized view that this one is dependent on.
@@ -11838,6 +11893,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
 		case DO_EXTENSION:
 			dumpExtension(fout, (const ExtensionInfo *) dobj);
 			break;
+		case DO_EXTENSION_DATA:
+			dumpTableData(fout, (const TableDataInfo *) dobj);
+			break;
 		case DO_TYPE:
 			dumpType(fout, (const TypeInfo *) dobj);
 			break;
@@ -20393,10 +20451,25 @@ processExtensionTables(Archive *fout, ExtensionInfo extinfo[],
 
 				if (dumpobj)
 				{
-					makeTableDataInfo(dopt, configtbl);
+					/*
+					 * For binary upgrades, dump extension config table data
+					 * before user tables are created so it's available for
+					 * validation (e.g. PostGIS SRIDs).
+					 */
+					if (dopt->binary_upgrade &&
+						configtbl->relkind == RELKIND_RELATION)
+						makeExtensionDataInfo(dopt, configtbl);
+					else
+						makeTableDataInfo(dopt, configtbl);
 					if (configtbl->dataObj != NULL)
 					{
-						if (strlen(extconditionarray[j]) > 0)
+						/*
+						 * For binary upgrade (DO_EXTENSION_DATA), don't apply
+						 * the filter condition - we need ALL data since the
+						 * extension won't populate built-in data in binary
+						 * upgrade mode.
+						 */
+						if (strlen(extconditionarray[j]) > 0 && !dopt->binary_upgrade)
 							configtbl->dataObj->filtercond = pg_strdup(extconditionarray[j]);
 					}
 				}
@@ -20674,6 +20747,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
 		{
 			case DO_NAMESPACE:
 			case DO_EXTENSION:
+			case DO_EXTENSION_DATA:
 			case DO_TYPE:
 			case DO_SHELL_TYPE:
 			case DO_FUNC:
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 5a6726d8b12..1e8bb961e8d 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -40,6 +40,7 @@ typedef enum
 	/* When modifying this enum, update priority tables in pg_dump_sort.c! */
 	DO_NAMESPACE,
 	DO_EXTENSION,
+	DO_EXTENSION_DATA,			/* extension config table data for binary upgrade */
 	DO_TYPE,
 	DO_SHELL_TYPE,
 	DO_FUNC,
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 03e5c1c1116..3cced9c27be 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -58,6 +58,7 @@ enum dbObjectTypePriorities
 	PRIO_COLLATION,
 	PRIO_TRANSFORM,
 	PRIO_EXTENSION,
+	PRIO_EXTENSION_DATA,		/* ext config data: used for binary upgrade */
 	PRIO_TYPE,					/* used for DO_TYPE and DO_SHELL_TYPE */
 	PRIO_CAST,
 	PRIO_FUNC,
@@ -106,6 +107,7 @@ static const int dbObjectTypePriority[] =
 {
 	[DO_NAMESPACE] = PRIO_NAMESPACE,
 	[DO_EXTENSION] = PRIO_EXTENSION,
+	[DO_EXTENSION_DATA] = PRIO_EXTENSION_DATA,
 	[DO_TYPE] = PRIO_TYPE,
 	[DO_SHELL_TYPE] = PRIO_TYPE,
 	[DO_FUNC] = PRIO_FUNC,
@@ -1525,6 +1527,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
 					 "EXTENSION %s  (ID %d OID %u)",
 					 obj->name, obj->dumpId, obj->catId.oid);
 			return;
+		case DO_EXTENSION_DATA:
+			snprintf(buf, bufsize,
+					 "EXTENSION DATA %s  (ID %d OID %u)",
+					 obj->name, obj->dumpId, obj->catId.oid);
+			return;
 		case DO_TYPE:
 			snprintf(buf, bufsize,
 					 "TYPE %s  (ID %d OID %u)",
diff --git a/src/test/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index 3d65ce4497a..db1cb22a13b 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -515,7 +515,6 @@ my %tests = (
 			extension_schema => 1,
 		},
 		unlike => {
-			binary_upgrade => 1,
 			exclude_table => 1,
 			exclude_extension => 1,
 			exclude_extension_filter => 1,
diff --git a/src/test/modules/test_pg_dump/test_pg_dump--1.0.sql b/src/test/modules/test_pg_dump/test_pg_dump--1.0.sql
index 1c68e146d91..134743e3943 100644
--- a/src/test/modules/test_pg_dump/test_pg_dump--1.0.sql
+++ b/src/test/modules/test_pg_dump/test_pg_dump--1.0.sql
@@ -18,6 +18,7 @@ CREATE TABLE regress_table_dumpable (
 	col1 int check (col1 > 0)
 );
 SELECT pg_catalog.pg_extension_config_dump('regress_table_dumpable', '');
+INSERT INTO regress_table_dumpable VALUES (27700);
 GRANT SELECT ON regress_table_dumpable TO public;
 
 CREATE SCHEMA regress_pg_dump_schema;
-- 
2.51.0