v1-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patch

From 1cc254b3416840d6c49773d4d309b876b2b5a8df Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Mon, 13 Apr 2026 17:49:44 +0530
Subject: [PATCH v1 1/3] Support EXCEPT clause for schema-level publications

Extend table exclusion support in publications to allow specific
tables to be excluded from schema-level publications using an
EXCEPT clause in CREATE PUBLICATION.

Supported syntax:
CREATE PUBLICATION <pub> FOR TABLES IN SCHEMA s EXCEPT (s.t1,...);
---
 doc/src/sgml/logical-replication.sgml       |   7 +-
 doc/src/sgml/ref/create_publication.sgml    |  19 ++-
 src/backend/catalog/pg_publication.c        |  79 +++++++++--
 src/backend/commands/publicationcmds.c      |  73 ++++++++++
 src/backend/parser/gram.y                   |  42 +++++-
 src/backend/replication/pgoutput/pgoutput.c |  34 ++++-
 src/bin/pg_dump/pg_dump.c                   |  30 ++++-
 src/bin/pg_dump/t/002_pg_dump.pl            |  38 +++++-
 src/bin/psql/describe.c                     |  18 +++
 src/bin/psql/tab-complete.in.c              |  12 +-
 src/include/nodes/parsenodes.h              |   1 +
 src/test/regress/expected/publication.out   |  42 +++++-
 src/test/regress/sql/publication.sql        |  27 +++-
 src/test/subscription/t/037_except.pl       | 139 +++++++++++++++++++-
 14 files changed, 527 insertions(+), 34 deletions(-)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 23b268273b9..a9ecf483a57 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -117,10 +117,13 @@
    or <literal>FOR ALL SEQUENCES</literal>. Unlike tables, sequences can be
    synchronized at any time. For more information, see
    <xref linkend="logical-replication-sequences"/>. When a publication is
-   created with <literal>FOR ALL TABLES</literal>, a table or set of tables can
+   created with <literal>FOR ALL TABLES</literal> or
+   <literal>FOR TABLES IN SCHEMA</literal>, a table or set of tables can
    be explicitly excluded from publication using the
    <link linkend="sql-createpublication-params-for-except-table"><literal>EXCEPT</literal></link>
-   clause.
+   clause.  When used with <literal>FOR TABLES IN SCHEMA</literal>, each
+   excluded table must belong to the schema named in the same
+   <literal>TABLES IN SCHEMA</literal> clause.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 0ac576d3f10..8715404f0cb 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -28,7 +28,7 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
 <phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
 
     TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
-    TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+    TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [ EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ] [, ... ]
 
 <phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
 
@@ -142,6 +142,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
      <para>
       Marks the publication as one that replicates changes for all tables in
       the specified list of schemas, including tables created in the future.
+      Tables listed in <literal>EXCEPT</literal> for a given schema are
+      excluded from the publication.
      </para>
 
      <para>
@@ -198,7 +200,11 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
     <listitem>
      <para>
       This clause specifies a list of tables to be excluded from the
-      publication.
+      publication. It can be used with both <literal>FOR ALL TABLES</literal>
+      and <literal>FOR TABLES IN SCHEMA</literal>. When used with
+      <literal>FOR TABLES IN SCHEMA</literal>, the excluded tables must belong
+      to the schema specified in the same <literal>TABLES IN SCHEMA</literal>
+      clause.
      </para>
      <para>
       For inherited tables, if <literal>ONLY</literal> is specified before the
@@ -515,6 +521,15 @@ CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES I
 CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;
 </programlisting></para>
 
+  <para>
+   Create a publication that publishes all changes for all the tables present in
+   the schema <structname>sales</structname>, except
+   <structname>sales.internal</structname> and <structname>sales.drafts</structname>:
+<programlisting>
+CREATE PUBLICATION sales_filtered FOR TABLES IN SCHEMA sales EXCEPT (sales.internal, sales.drafts);
+</programlisting>
+  </para>
+
   <para>
    Create a publication that publishes all changes for table <structname>users</structname>,
    but replicates only columns <structname>user_id</structname> and
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index a43d385c605..8e6c4015d86 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -620,19 +620,24 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
 	/*
 	 * Determine whether EXCEPT tables require explicit relcache invalidation.
 	 *
-	 * For CREATE PUBLICATION with EXCEPT tables, invalidation is skipped
-	 * here, as CreatePublication() function invalidates all relations as part
-	 * of defining a FOR ALL TABLES publication.
+	 * For CREATE PUBLICATION with EXCEPT tables, invalidation is skipped here
+	 * because the excluded tables were never included in the publication, so
+	 * no subscriber cache entry needs to be cleared.
 	 *
-	 * For ALTER PUBLICATION, invalidation is needed only when adding an
-	 * EXCEPT table to a publication already marked as ALL TABLES. For
-	 * publications that were originally empty or defined as ALL SEQUENCES and
-	 * are being converted to ALL TABLES, invalidation is skipped here, as
-	 * AlterPublicationAllFlags() function invalidates all relations while
-	 * marking the publication as ALL TABLES publication.
+	 * For ALTER PUBLICATION, invalidation is needed when adding an EXCEPT
+	 * table to either: - a FOR ALL TABLES publication (pub->alltables is
+	 * true), or - a FOR TABLES IN SCHEMA publication (is_schema_publication
+	 * is true).
+	 *
+	 * The exception: when a publication is being converted to FOR ALL TABLES
+	 * (pub->alltables is still false at this point),
+	 * AlterPublicationAllFlags() will perform a full invalidation, so we skip
+	 * it here.
 	 */
-	inval_except_table = (alter_stmt != NULL) && pub->alltables &&
-		(alter_stmt->for_all_tables && pri->except);
+	inval_except_table = (alter_stmt != NULL) && pri->except &&
+		(pub->alltables
+		 ? alter_stmt->for_all_tables
+		 : is_schema_publication(pubid));
 
 	if (!pri->except || inval_except_table)
 	{
@@ -975,7 +980,8 @@ GetIncludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
  * Gets list of table oids that were specified in the EXCEPT clause for a
  * publication.
  *
- * This should only be used FOR ALL TABLES publications.
+ * This is used for FOR ALL TABLES and FOR TABLES IN SCHEMA publications,
+ * both of which support EXCEPT TABLE.
  */
 List *
 GetExcludedPublicationTables(Oid pubid, PublicationPartOpt pub_partopt)
@@ -1225,22 +1231,67 @@ GetSchemaPublicationRelations(Oid schemaid, PublicationPartOpt pub_partopt)
 
 /*
  * Gets the list of all relations published by FOR TABLES IN SCHEMA
- * publication.
+ * publication, excluding any tables listed in the EXCEPT clause.
  */
 List *
 GetAllSchemaPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
 {
 	List	   *result = NIL;
 	List	   *pubschemalist = GetPublicationSchemas(pubid);
+	List	   *exceptlist = NIL;
 	ListCell   *cell;
 
+	/* get the list of tables excluded via EXCEPT TABLE for this publication */
+	if (pubschemalist != NIL)
+		exceptlist = get_publication_relations(pubid, pub_partopt, true);
+
 	foreach(cell, pubschemalist)
 	{
 		Oid			schemaid = lfirst_oid(cell);
 		List	   *schemaRels = NIL;
 
 		schemaRels = GetSchemaPublicationRelations(schemaid, pub_partopt);
-		result = list_concat(result, schemaRels);
+
+		if (exceptlist != NIL)
+		{
+			/* filter out any tables that appear in the EXCEPT list */
+			ListCell   *rlc;
+
+			foreach(rlc, schemaRels)
+			{
+				Oid			relid = lfirst_oid(rlc);
+				bool		excluded = list_member_oid(exceptlist, relid);
+
+				/*
+				 * Also exclude any relation whose partition ancestor is in
+				 * the EXCEPT list.  This matters when pub_partopt is
+				 * PUBLICATION_PART_ROOT: the except list holds only the root
+				 * OID, but the schema scan may also return individual
+				 * partition relations that live in the same schema.
+				 */
+				if (!excluded && get_rel_relispartition(relid))
+				{
+					List	   *ancestors = get_partition_ancestors(relid);
+					ListCell   *alc;
+
+					foreach(alc, ancestors)
+					{
+						if (list_member_oid(exceptlist, lfirst_oid(alc)))
+						{
+							excluded = true;
+							break;
+						}
+					}
+					list_free(ancestors);
+				}
+
+				if (!excluded)
+					result = lappend_oid(result, relid);
+			}
+			list_free(schemaRels);
+		}
+		else
+			result = list_concat(result, schemaRels);
 	}
 
 	return result;
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 440adb356ad..8beb806f507 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -959,6 +959,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 	}
 	else if (!stmt->for_all_sequences)
 	{
+		List	   *explicitrelids = NIL;
+
 		/* FOR TABLES IN SCHEMA requires superuser */
 		if (schemaidlist != NIL && !superuser())
 			ereport(ERROR,
@@ -968,6 +970,7 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 		if (relations != NIL)
 		{
 			List	   *rels;
+			ListCell   *lc;
 
 			rels = OpenTableList(relations);
 			TransformPubWhereClauses(rels, pstate->p_sourcetext,
@@ -977,6 +980,20 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 									   schemaidlist != NIL,
 									   publish_via_partition_root);
 
+			/*
+			 * Collect explicit table OIDs now, before we close the relation
+			 * list, so that except-table validation below can check for
+			 * contradictions without relying on a catalog scan that might not
+			 * yet see the just-inserted rows.
+			 */
+			foreach(lc, rels)
+			{
+				PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
+
+				explicitrelids = lappend_oid(explicitrelids,
+											 RelationGetRelid(pri->relation));
+			}
+
 			PublicationAddTables(puboid, rels, true, NULL);
 			CloseTableList(rels);
 		}
@@ -989,6 +1006,62 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 			 */
 			LockSchemaList(schemaidlist);
 			PublicationAddSchemas(puboid, schemaidlist, true, NULL);
+
+			if (exceptrelations != NIL)
+			{
+				List	   *rels;
+				ListCell   *lc;
+
+				/*
+				 * For TABLES IN SCHEMA publications, require schema-qualified
+				 * names to avoid ambiguity when multiple schemas in the
+				 * publication have identically-named tables.
+				 */
+				foreach(lc, exceptrelations)
+				{
+					PublicationTable *t = (PublicationTable *) lfirst(lc);
+
+					if (t->relation->schemaname == NULL)
+						ereport(ERROR,
+								errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+								errmsg("table \"%s\" in EXCEPT clause must be schema-qualified",
+									   t->relation->relname));
+				}
+
+				rels = OpenTableList(exceptrelations);
+
+				/*
+				 * Validate that each excluded table belongs to one of the
+				 * schemas listed in this publication, and is not also in the
+				 * explicit table list (which would be contradictory). Use the
+				 * in-memory explicitrelids collected above rather than
+				 * re-reading the catalog, which may not yet see the
+				 * just-inserted rows.
+				 */
+				foreach(lc, rels)
+				{
+					PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
+					Oid			relid = RelationGetRelid(pri->relation);
+					Oid			relns = RelationGetNamespace(pri->relation);
+
+					if (!list_member_oid(schemaidlist, relns))
+						ereport(ERROR,
+								errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+								errmsg("table \"%s.%s\" in EXCEPT clause does not belong to any schema listed in the publication",
+									   get_namespace_name(relns),
+									   RelationGetRelationName(pri->relation)));
+
+					if (list_member_oid(explicitrelids, relid))
+						ereport(ERROR,
+								errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+								errmsg("table \"%s.%s\" cannot appear in both the table list and the EXCEPT clause",
+									   get_namespace_name(relns),
+									   RelationGetRelationName(pri->relation)));
+				}
+
+				PublicationAddTables(puboid, rels, true, NULL);
+				CloseTableList(rels);
+			}
 		}
 	}
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 2d1b19d1f53..e1adff0f131 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -457,7 +457,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				TriggerTransitions TriggerReferencing
 				vacuum_relation_list opt_vacuum_relation_list
 				drop_option_list pub_obj_list pub_all_obj_type_list
-				pub_except_obj_list opt_pub_except_clause
+				pub_except_obj_list pub_schema_except_obj_list
+			opt_pub_except_clause opt_pub_schema_except_clause
 
 %type <retclause> returning_clause
 %type <node>	returning_option
@@ -11272,7 +11273,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec
  * pub_obj is one of:
  *
  *		TABLE table [, ...]
- *		TABLES IN SCHEMA schema [, ...]
+ *		TABLES IN SCHEMA schema [EXCEPT ( table [, ...] )] [, ...]
  *
  *****************************************************************************/
 
@@ -11332,23 +11333,26 @@ PublicationObjSpec:
 					$$->pubtable->columns = $3;
 					$$->pubtable->whereClause = $4;
 				}
-			| TABLES IN_P SCHEMA ColId
+			| TABLES IN_P SCHEMA ColId opt_pub_schema_except_clause
 				{
 					$$ = makeNode(PublicationObjSpec);
 					$$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_SCHEMA;
 					$$->name = $4;
+					$$->except_tables = $5;
 					$$->location = @4;
 				}
-			| TABLES IN_P SCHEMA CURRENT_SCHEMA
+			| TABLES IN_P SCHEMA CURRENT_SCHEMA opt_pub_schema_except_clause
 				{
 					$$ = makeNode(PublicationObjSpec);
 					$$->pubobjtype = PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA;
+					$$->except_tables = $5;
 					$$->location = @4;
 				}
-			| ColId opt_column_list OptWhereClause
+			| ColId opt_column_list OptWhereClause opt_pub_schema_except_clause
 				{
 					$$ = makeNode(PublicationObjSpec);
 					$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+					$$->except_tables = $4;
 					/*
 					 * If either a row filter or column list is specified, create
 					 * a PublicationTable object.
@@ -11392,10 +11396,11 @@ PublicationObjSpec:
 					$$->pubtable->columns = $2;
 					$$->pubtable->whereClause = $3;
 				}
-			| CURRENT_SCHEMA
+			| CURRENT_SCHEMA opt_pub_schema_except_clause
 				{
 					$$ = makeNode(PublicationObjSpec);
 					$$->pubobjtype = PUBLICATIONOBJ_CONTINUATION;
+					$$->except_tables = $2;
 					$$->location = @1;
 				}
 				;
@@ -11411,6 +11416,11 @@ opt_pub_except_clause:
 			| /*EMPTY*/									{ $$ = NIL; }
 		;
 
+opt_pub_schema_except_clause:
+			EXCEPT '(' pub_schema_except_obj_list ')'	{ $$ = $3; }
+			| /*EMPTY*/									{ $$ = NIL; }
+		;
+
 PublicationAllObjSpec:
 				ALL TABLES opt_pub_except_clause
 					{
@@ -11451,6 +11461,13 @@ pub_except_obj_list: PublicationExceptObjSpec
 					{ $$ = lappend($1, $4); }
 	;
 
+/* Like pub_except_obj_list but without opt_table; used for TABLES IN SCHEMA EXCEPT. */
+pub_schema_except_obj_list: PublicationExceptObjSpec
+					{ $$ = list_make1($1); }
+			| pub_schema_except_obj_list ',' PublicationExceptObjSpec
+					{ $$ = lappend($1, $3); }
+	;
+
 /*****************************************************************************
  *
  * ALTER PUBLICATION name SET ( options )
@@ -20784,6 +20801,8 @@ preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects,
 /*
  * Process pubobjspec_list to check for errors in any of the objects and
  * convert PUBLICATIONOBJ_CONTINUATION into appropriate PublicationObjSpecType.
+ * Also flattens except_tables from TABLES IN SCHEMA nodes into the list so
+ * that ObjectsInPublicationToOids() sees them as top-level EXCEPT_TABLE entries.
  */
 static void
 preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
@@ -20812,6 +20831,13 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
 
 		if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE)
 		{
+			/* EXCEPT is not valid for table objects */
+			if (pubobj->except_tables != NIL)
+				ereport(ERROR,
+						errcode(ERRCODE_SYNTAX_ERROR),
+						errmsg("EXCEPT is not allowed for TABLE publication objects"),
+						parser_errposition(pubobj->location));
+
 			/* relation name or pubtable must be set for this type of object */
 			if (!pubobj->name && !pubobj->pubtable)
 				ereport(ERROR,
@@ -20860,6 +20886,10 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
 						errcode(ERRCODE_SYNTAX_ERROR),
 						errmsg("invalid schema name"),
 						parser_errposition(pubobj->location));
+
+			/* Flatten EXCEPT entries into the top-level list */
+			pubobjspec_list = list_concat(pubobjspec_list, pubobj->except_tables);
+			pubobj->except_tables = NIL;
 		}
 
 		prevobjtype = pubobj->pubobjtype;
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 4ecfcbff7ab..942ddb51d3a 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2097,6 +2097,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
 		 * are absorbed while decoding WAL.
 		 */
 		List	   *schemaPubids = GetSchemaPublications(schemaId);
+		List	   *schemaExceptPubids;
 		ListCell   *lc;
 		Oid			publish_as_relid = relid;
 		int			publish_ancestor_level = 0;
@@ -2104,6 +2105,25 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
 		char		relkind = get_rel_relkind(relid);
 		List	   *rel_publications = NIL;
 
+		/*
+		 * For the schema EXCEPT check, we must look up the top-most ancestor
+		 * rather than the relation itself.  check_publication_add_relation()
+		 * prevents individual partitions from appearing in the EXCEPT clause,
+		 * so only a root (non-partition) table can have prexcept = true.
+		 * Using the partition's own OID would always return NIL and miss the
+		 * exclusion.
+		 */
+		if (am_partition)
+		{
+			List	   *pancestore = get_partition_ancestors(relid);
+
+			schemaExceptPubids =
+				GetRelationExcludedPublications(llast_oid(pancestore));
+			list_free(pancestore);
+		}
+		else
+			schemaExceptPubids = GetRelationExcludedPublications(relid);
+
 		/* Reload publications if needed before use. */
 		if (!publications_valid)
 		{
@@ -2269,7 +2289,15 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
 															   ancestors,
 															   &level);
 
-					if (ancestor != InvalidOid)
+					/*
+					 * The ancestor is only considered published if it is not
+					 * in the EXCEPT clause of this schema publication.
+					 * GetTopMostAncestorInPublication checks schema
+					 * membership but does not account for the EXCEPT list, so
+					 * we must filter that out here.
+					 */
+					if (ancestor != InvalidOid &&
+						!list_member_oid(schemaExceptPubids, pub->oid))
 					{
 						ancestor_published = true;
 						if (pub->pubviaroot)
@@ -2281,7 +2309,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
 				}
 
 				if (list_member_oid(pubids, pub->oid) ||
-					list_member_oid(schemaPubids, pub->oid) ||
+					(list_member_oid(schemaPubids, pub->oid) &&
+					 !list_member_oid(schemaExceptPubids, pub->oid)) ||
 					ancestor_published)
 					publish = true;
 			}
@@ -2360,6 +2389,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
 
 		list_free(pubids);
 		list_free(schemaPubids);
+		list_free(schemaExceptPubids);
 		list_free(rel_publications);
 
 		entry->replicate_valid = true;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 1ca03d6b278..a1ca6edd80d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5019,6 +5019,7 @@ dumpPublicationNamespace(Archive *fout, const PublicationSchemaInfo *pubsinfo)
 	PublicationInfo *pubinfo = pubsinfo->publication;
 	PQExpBuffer query;
 	char	   *tag;
+	bool		first_except = true;
 
 	/* Do nothing if not dumping schema */
 	if (!dopt->dumpSchema)
@@ -5029,7 +5030,34 @@ dumpPublicationNamespace(Archive *fout, const PublicationSchemaInfo *pubsinfo)
 	query = createPQExpBuffer();
 
 	appendPQExpBuffer(query, "ALTER PUBLICATION %s ", fmtId(pubinfo->dobj.name));
-	appendPQExpBuffer(query, "ADD TABLES IN SCHEMA %s;\n", fmtId(schemainfo->dobj.name));
+	appendPQExpBuffer(query, "ADD TABLES IN SCHEMA %s", fmtId(schemainfo->dobj.name));
+
+	/*
+	 * Append EXCEPT clause for any tables that belong to this schema
+	 * and are excluded from the publication.
+	 */
+	for (SimplePtrListCell *cell = pubinfo->except_tables.head; cell; cell = cell->next)
+	{
+		TableInfo  *tbinfo = (TableInfo *) cell->ptr;
+
+		if (strcmp(tbinfo->dobj.namespace->dobj.name, schemainfo->dobj.name) == 0)
+		{
+			if (first_except)
+			{
+				appendPQExpBufferStr(query, " EXCEPT (");
+				first_except = false;
+			}
+			else
+				appendPQExpBufferStr(query, ", ");
+
+			appendPQExpBuffer(query, "ONLY %s", fmtQualifiedDumpable(tbinfo));
+		}
+	}
+
+	if (!first_except)
+		appendPQExpBufferStr(query, ")");
+
+	appendPQExpBufferStr(query, ";\n");
 
 	/*
 	 * There is no point in creating drop query as the drop is done by schema
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 3bc8e51561d..2a0546b9dc9 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3242,6 +3242,42 @@ my %tests = (
 		like => { %full_runs, section_post_data => 1, },
 	},
 
+	'CREATE PUBLICATION pub11' => {
+		create_order => 50,
+		create_sql =>
+		  'CREATE PUBLICATION pub11 FOR TABLES IN SCHEMA dump_test EXCEPT (dump_test.test_table);',
+		regexp => qr/^
+			\QCREATE PUBLICATION pub11 WITH (publish = 'insert, update, delete, truncate');\E
+			/xm,
+		like => { %full_runs, section_post_data => 1, },
+	},
+
+	'ALTER PUBLICATION pub11 ADD TABLES IN SCHEMA dump_test EXCEPT (dump_test.test_table)'
+	  => {
+		regexp => qr/^
+			\QALTER PUBLICATION pub11 ADD TABLES IN SCHEMA dump_test EXCEPT (ONLY dump_test.test_table);\E
+			/xm,
+		like => { %full_runs, section_post_data => 1, },
+	  },
+
+	'CREATE PUBLICATION pub12' => {
+		create_order => 50,
+		create_sql =>
+		  'CREATE PUBLICATION pub12 FOR TABLES IN SCHEMA dump_test EXCEPT (dump_test.test_table, dump_test.test_second_table);',
+		regexp => qr/^
+			\QCREATE PUBLICATION pub12 WITH (publish = 'insert, update, delete, truncate');\E
+			/xm,
+		like => { %full_runs, section_post_data => 1, },
+	},
+
+	'ALTER PUBLICATION pub12 ADD TABLES IN SCHEMA dump_test EXCEPT (dump_test.test_table, dump_test.test_second_table)'
+	  => {
+		regexp => qr/^
+			\QALTER PUBLICATION pub12 ADD TABLES IN SCHEMA dump_test EXCEPT (ONLY dump_test.test_table, ONLY dump_test.test_second_table);\E
+			/xm,
+		like => { %full_runs, section_post_data => 1, },
+	  },
+
 	'CREATE SUBSCRIPTION sub1' => {
 		create_order => 50,
 		create_sql => 'CREATE SUBSCRIPTION sub1
@@ -5279,7 +5315,7 @@ foreach my $run (sort keys %pgdump_runs)
 		#
 		# Either "all_runs" should be set or there should be a "like" list,
 		# even if it is empty.  (This makes the test more self-documenting.)
-		if (!defined($tests{$test}->{all_runs})
+		if (   !defined($tests{$test}->{all_runs})
 			&& !defined($tests{$test}->{like}))
 		{
 			die "missing \"like\" in test \"$test\"";
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index dd1179ef927..b751206e5a4 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -7038,6 +7038,24 @@ describePublications(const char *pattern)
 				if (!addFooterToPublicationDesc(&buf, _("Tables from schemas:"),
 												true, &cont))
 					goto error_return;
+
+				if (pset.sversion >= 190000)
+				{
+					/*
+					 * Get tables in the EXCEPT clause for this schema
+					 * publication.
+					 */
+					printfPQExpBuffer(&buf,
+									  "SELECT concat(c.relnamespace::regnamespace, '.', c.relname)\n"
+									  "FROM pg_catalog.pg_class c\n"
+									  "     JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n"
+									  "WHERE pr.prpubid = '%s'\n"
+									  "  AND pr.prexcept\n"
+									  "ORDER BY 1", pubid);
+					if (!addFooterToPublicationDesc(&buf, _("Except tables:"),
+													true, &cont))
+						goto error_return;
+				}
 			}
 		}
 		else
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 9990f818942..166d03e3a1f 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3784,8 +3784,16 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_schemas
 								 " AND nspname NOT LIKE E'pg\\\\_%%'",
 								 "CURRENT_SCHEMA");
-	else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny) && (!ends_with(prev_wd, ',')))
-		COMPLETE_WITH("WITH (");
+	else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny) && !ends_with(prev_wd, ','))
+		COMPLETE_WITH("EXCEPT (", "WITH (");
+	else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT"))
+		COMPLETE_WITH("(");
+	else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "("))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+	else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", MatchAnyN) && ends_with(prev_wd, ','))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+	else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", MatchAnyN) && !ends_with(prev_wd, ','))
+		COMPLETE_WITH(")");
 	/* Complete "CREATE PUBLICATION <name> [...] WITH" */
 	else if (Matches("CREATE", "PUBLICATION", MatchAnyN, "WITH", "("))
 		COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 91377a6cde3..a9177de0d43 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4493,6 +4493,7 @@ typedef struct PublicationObjSpec
 	PublicationObjSpecType pubobjtype;	/* type of this publication object */
 	char	   *name;
 	PublicationTable *pubtable;
+	List	   *except_tables;	/* tables to exclude (for TABLES IN SCHEMA) */
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } PublicationObjSpec;
 
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index d028e9be866..f6aadc1e835 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -461,7 +461,47 @@ HINT:  Change the publication's EXCEPT clause using ALTER PUBLICATION ... SET AL
 RESET client_min_messages;
 DROP TABLE testpub_root, testpub_part1, tab_main;
 DROP PUBLICATION testpub8;
---- Tests for publications with SEQUENCES
+---------------------------------------------
+-- EXCEPT tests for TABLES IN SCHEMA
+---------------------------------------------
+SET client_min_messages = 'ERROR';
+-- Create tables in pub_test for these tests
+CREATE TABLE pub_test.testpub_tbl_s1 (a int primary key, b text);
+CREATE TABLE pub_test.testpub_tbl_s2 (x int primary key, y text);
+-- Basic: exclude one table from a schema publication
+CREATE PUBLICATION testpub_schema_except1
+    FOR TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_tbl_s1);
+\dRp+ testpub_schema_except1
+                                                      Publication testpub_schema_except1
+          Owner           | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description 
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f          | f             | t       | t       | t       | t         | none              | f        | 
+Tables from schemas:
+    "pub_test"
+Except tables:
+    "pub_test.testpub_tbl_s1"
+
+-- Exclude multiple tables from a schema publication
+CREATE PUBLICATION testpub_schema_except2
+    FOR TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_nopk, pub_test.testpub_tbl_s1);
+\dRp+ testpub_schema_except2
+                                                      Publication testpub_schema_except2
+          Owner           | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description 
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f          | f             | t       | t       | t       | t         | none              | f        | 
+Tables from schemas:
+    "pub_test"
+Except tables:
+    "pub_test.testpub_nopk"
+    "pub_test.testpub_tbl_s1"
+
+-- fail: EXCEPT is not allowed for FOR TABLE publications
+CREATE PUBLICATION testpub_except_err
+    FOR TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2 EXCEPT (pub_test.testpub_nopk);
+ERROR:  EXCEPT is not allowed for TABLE publication objects
+RESET client_min_messages;
+DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2;
 CREATE SEQUENCE regress_pub_seq0;
 CREATE SEQUENCE pub_test.regress_pub_seq1;
 -- FOR ALL SEQUENCES
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 642e32fa098..3f46e8c5e03 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -218,7 +218,32 @@ RESET client_min_messages;
 DROP TABLE testpub_root, testpub_part1, tab_main;
 DROP PUBLICATION testpub8;
 
---- Tests for publications with SEQUENCES
+---------------------------------------------
+-- EXCEPT tests for TABLES IN SCHEMA
+---------------------------------------------
+SET client_min_messages = 'ERROR';
+-- Create tables in pub_test for these tests
+CREATE TABLE pub_test.testpub_tbl_s1 (a int primary key, b text);
+CREATE TABLE pub_test.testpub_tbl_s2 (x int primary key, y text);
+
+-- Basic: exclude one table from a schema publication
+CREATE PUBLICATION testpub_schema_except1
+    FOR TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_tbl_s1);
+\dRp+ testpub_schema_except1
+
+-- Exclude multiple tables from a schema publication
+CREATE PUBLICATION testpub_schema_except2
+    FOR TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_nopk, pub_test.testpub_tbl_s1);
+\dRp+ testpub_schema_except2
+
+-- fail: EXCEPT is not allowed for FOR TABLE publications
+CREATE PUBLICATION testpub_except_err
+    FOR TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2 EXCEPT (pub_test.testpub_nopk);
+
+RESET client_min_messages;
+DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2;
+
 CREATE SEQUENCE regress_pub_seq0;
 CREATE SEQUENCE pub_test.regress_pub_seq1;
 
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 8c58d282eee..0801d3a77c7 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -24,14 +24,16 @@ my $result;
 
 sub test_except_root_partition
 {
-	my ($pubviaroot) = @_;
+	my ($pubviaroot, $pubsql) = @_;
+	$pubsql //=
+	  "CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1) WITH (publish_via_partition_root = $pubviaroot)";
 
 	# If the root partitioned table is in the EXCEPT clause, all its
 	# partitions are excluded from publication, regardless of the
 	# publish_via_partition_root setting.
 	$node_publisher->safe_psql(
 		'postgres', qq(
-		CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT (TABLE root1) WITH (publish_via_partition_root = $pubviaroot);
+		$pubsql;
 		INSERT INTO root1 VALUES (1), (101);
 	));
 	$node_subscriber->safe_psql('postgres',
@@ -223,6 +225,138 @@ $node_subscriber->safe_psql(
 test_except_root_partition('false');
 test_except_root_partition('true');
 
+# Same validation using TABLES IN SCHEMA instead of FOR ALL TABLES.
+my $schema_pub =
+  "CREATE PUBLICATION tap_pub_part FOR TABLES IN SCHEMA public EXCEPT (public.root1)";
+test_except_root_partition('false',
+	"$schema_pub WITH (publish_via_partition_root = false)");
+test_except_root_partition('true',
+	"$schema_pub WITH (publish_via_partition_root = true)");
+
+# ============================================
+# EXCEPT test cases for TABLES IN SCHEMA
+# ============================================
+
+# Create a dedicated schema with two tables: one to be published and one to be
+# excluded.  Also create inherited tables to verify ONLY semantics.
+$node_publisher->safe_psql(
+	'postgres', qq(
+	CREATE SCHEMA sch1;
+	CREATE TABLE sch1.tab_pub AS SELECT generate_series(1,5) AS a;
+	CREATE TABLE sch1.tab_exc AS SELECT generate_series(1,5) AS a;
+	CREATE TABLE sch1.par (a int);
+	CREATE TABLE sch1.chi (b int) INHERITS (sch1.par);
+));
+
+$node_subscriber->safe_psql(
+	'postgres', qq(
+	CREATE SCHEMA sch1;
+	CREATE TABLE sch1.tab_pub (a int);
+	CREATE TABLE sch1.tab_exc (a int);
+	CREATE TABLE sch1.par (a int);
+	CREATE TABLE sch1.chi (b int) INHERITS (sch1.par);
+));
+
+# Basic test: initial sync respects EXCEPT.
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (sch1.tab_exc)"
+);
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_pub");
+is($result, qq(5),
+	'TABLES IN SCHEMA EXCEPT: initial sync copies included table');
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_exc");
+is($result, qq(0),
+	'TABLES IN SCHEMA EXCEPT: initial sync skips excluded table');
+
+# DML: only the included table should be replicated.
+$node_publisher->safe_psql(
+	'postgres', qq(
+	INSERT INTO sch1.tab_pub VALUES (6);
+	INSERT INTO sch1.tab_exc VALUES (6);
+));
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_pub");
+is($result, qq(6),
+	'TABLES IN SCHEMA EXCEPT: DML on included table is replicated');
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_exc");
+is($result, qq(0),
+	'TABLES IN SCHEMA EXCEPT: DML on excluded table is not replicated');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
+# Inherited tables: excluding the parent (without ONLY) also excludes the child.
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (sch1.par)"
+);
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO sch1.chi VALUES (generate_series(1,5), generate_series(1,5))"
+);
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.chi");
+is($result, qq(0),
+	'TABLES IN SCHEMA EXCEPT: excluding parent (without ONLY) also excludes child'
+);
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
+# Truncate chi on the publisher so the next test starts with a clean slate.
+# (The previous test inserted rows into chi that would otherwise be copied by
+# the initial table sync of the next subscription.)
+$node_publisher->safe_psql('postgres', 'TRUNCATE sch1.chi');
+$node_subscriber->safe_psql('postgres', 'TRUNCATE sch1.chi');
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION sch_pub FOR TABLES IN SCHEMA sch1 EXCEPT (ONLY sch1.par)"
+);
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO sch1.chi VALUES (generate_series(1,5), generate_series(1,5))"
+);
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.chi");
+is($result, qq(5),
+	'TABLES IN SCHEMA EXCEPT: ONLY parent in EXCEPT does not exclude child'
+);
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+$node_publisher->safe_psql('postgres',
+	'TRUNCATE sch1.par, sch1.chi, sch1.tab_exc');
+$node_subscriber->safe_psql('postgres',
+	'TRUNCATE sch1.par, sch1.chi, sch1.tab_pub, sch1.tab_exc');
+
+# Cleanup schema tables before the multi-publication section.
+$node_publisher->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
+$node_subscriber->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
+
 # ============================================
 # Test when a subscription is subscribing to multiple publications
 # ============================================
@@ -254,6 +388,7 @@ $node_publisher->safe_psql(
 	DROP PUBLICATION tap_pub2;
 	TRUNCATE tab1;
 ));
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
 $node_subscriber->safe_psql('postgres', qq(TRUNCATE tab1));
 
 # OK when a table is excluded by pub1 EXCEPT clause, but it is included by pub2
-- 
2.50.1 (Apple Git-155)