v24-0002-fixup-Support-existing-publications-in-pg_create.patch

From f6cd0469b62b091cc62997201ec3fb2934331f72 Mon Sep 17 00:00:00 2001
From: Euler Taveira <euler@eulerto.com>
Date: Mon, 8 Dec 2025 13:22:55 -0300
Subject: [PATCH v24 2/2] fixup! Support existing publications in
 pg_createsubscriber

---
 doc/src/sgml/ref/pg_createsubscriber.sgml     |  15 +-
 src/bin/pg_basebackup/pg_createsubscriber.c   |  67 ++++----
 .../t/040_pg_createsubscriber.pl              | 160 +++---------------
 3 files changed, 70 insertions(+), 172 deletions(-)

diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 75e2edc83fd..5a62187b189 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -286,15 +286,12 @@ PostgreSQL documentation
        be used together with <option>--all</option>.
       </para>
       <para>
-       If a publication with the specified name already exists on the publisher,
-       it will be reused with its current configuration, including its table
-       list, row filters, and column filters.
-       If a publication does not exist, it will be created automatically with
-       <literal>FOR ALL TABLES</literal>.
-      </para>
-      <para>
-       Use <option>--dry-run</option> to safely preview which publications will
-       be reused and which will be created.
+       If a specified publication already exists on the publisher, it is reused.
+       It is useful to partially replicate the database if the specified
+       publication includes a list of tables. If the publication does not exist,
+       it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+       <option>--dry-run</option> option to preview which publications will be
+       reused and which will be created.
       </para>
      </listitem>
     </varlistentry>
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index a6f9e21ccd4..41a649297c7 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -116,7 +116,7 @@ static void stop_standby_server(const char *datadir);
 static void wait_for_end_recovery(const char *conninfo,
 								  const struct CreateSubscriberOptions *opt);
 static void create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo);
-static bool check_publication_exists(PGconn *conn, const char *pubname, const char *dbname);
+static bool find_publication(PGconn *conn, const char *pubname, const char *dbname);
 static void drop_publication(PGconn *conn, const char *pubname,
 							 const char *dbname, bool *made_publication);
 static void check_and_drop_publications(PGconn *conn, struct LogicalRepInfo *dbinfo);
@@ -765,30 +765,36 @@ generate_object_name(PGconn *conn)
 }
 
 /*
- * Return whether a specified publication exists in the specified database.
+ * Does the publication exist in the specified database?
  */
 static bool
-check_publication_exists(PGconn *conn, const char *pubname, const char *dbname)
+find_publication(PGconn *conn, const char *pubname, const char *dbname)
 {
+	PQExpBuffer str = createPQExpBuffer();
 	PGresult   *res;
-	bool		exists;
-	char	   *query;
+	bool		found = false;
 	char	   *pubname_esc = PQescapeLiteral(conn, pubname, strlen(pubname));
 
-	query = psprintf("SELECT 1 FROM pg_publication WHERE pubname = %s",
-					 pubname_esc);
-	res = PQexec(conn, query);
-
+	appendPQExpBuffer(str,
+					  "SELECT 1 FROM pg_catalog.pg_publication "
+					  "WHERE pubname = %s",
+					  pubname_esc);
+	res = PQexec(conn, str->data);
 	if (PQresultStatus(res) != PGRES_TUPLES_OK)
-		pg_fatal("could not check for publication \"%s\" in database \"%s\": %s",
-				 pubname, dbname, PQerrorMessage(conn));
+	{
+		pg_log_error("could not find publication \"%s\" in database \"%s\": %s",
+					 pubname, dbname, PQerrorMessage(conn));
+		disconnect_database(conn, true);
+	}
 
-	exists = (PQntuples(res) == 1);
+	if (PQntuples(res) == 1)
+		found = true;
 
-	PQfreemem(pubname_esc);
 	PQclear(res);
-	pg_free(query);
-	return exists;
+	PQfreemem(pubname_esc);
+	destroyPQExpBuffer(str);
+
+	return found;
 }
 
 /*
@@ -827,11 +833,12 @@ setup_publisher(struct LogicalRepInfo *dbinfo)
 		if (num_replslots == 0)
 			dbinfo[i].replslotname = pg_strdup(dbinfo[i].subname);
 
-		if (check_publication_exists(conn, dbinfo[i].pubname, dbinfo[i].dbname))
+		if (find_publication(conn, dbinfo[i].pubname, dbinfo[i].dbname))
 		{
 			/* Reuse existing publication on publisher. */
 			pg_log_info("use existing publication \"%s\" in database \"%s\"",
 						dbinfo[i].pubname, dbinfo[i].dbname);
+			/* Don't remove pre-existing publication if an error occurs. */
 			dbinfo[i].made_publication = false;
 		}
 		else
@@ -1823,25 +1830,23 @@ check_and_drop_publications(PGconn *conn, struct LogicalRepInfo *dbinfo)
 
 		PQclear(res);
 	}
-
-	/*
-	 * Handle publication created by pg_createsubscriber.
-	 *
-	 * In dry-run mode, create_publication() and drop_publication() only log
-	 * actions without modifying the database. Importantly, since no
-	 * publication is actually created in dry-run, the above query for
-	 * existing publications won't find the "would-be" made publication. Thus,
-	 * we must call drop_publication() regardless of drop_all_pubs to ensure
-	 * the user sees the intended log message.
-	 */
-	if (!drop_all_pubs || dry_run)
+	else
 	{
+		/* Drop publication only if it was created by this tool */
 		if (dbinfo->made_publication)
+		{
 			drop_publication(conn, dbinfo->pubname, dbinfo->dbname,
 							 &dbinfo->made_publication);
-		else if (!drop_all_pubs)
-			pg_log_info("dry-run: would preserve existing publication \"%s\" in database \"%s\"",
-						dbinfo->pubname, dbinfo->dbname);
+		}
+		else
+		{
+			if (dry_run)
+				pg_log_info("dry-run: would preserve existing publication \"%s\" in database \"%s\"",
+							dbinfo->pubname, dbinfo->dbname);
+			else
+				pg_log_info("preserve existing publication \"%s\" in database \"%s\"",
+							dbinfo->pubname, dbinfo->dbname);
+		}
 	}
 }
 
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index c07bde2728d..62789e94fd2 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -443,17 +443,15 @@ is(scalar(() = $stderr =~ /would create the replication slot/g),
 is(scalar(() = $stderr =~ /would create subscription/g),
 	3, "verify subscriptions are created for all databases");
 
-# Create a user-defined publication.
+# Create a user-defined publication
 $node_p->safe_psql($db1,
-	"CREATE PUBLICATION test_pub_existing FOR TABLE tbl1");
+	"CREATE PUBLICATION test_pub3 FOR TABLE tbl1");
+$node_p->safe_psql($db1, 'CREATE TABLE not_replicated (a int)');
 
 # Run pg_createsubscriber on node S.  --verbose is used twice
 # to show more information.
 #
-# In passing, also test the --enable-two-phase option.
-#
-# In passing, also test that --clean=publications will dropp all
-# publications (both pre-existing and pg_createsubscriber-created).
+# Test two phase and clean options. Use pre-existing publication.
 command_ok(
 	[
 		'pg_createsubscriber',
@@ -463,7 +461,7 @@ command_ok(
 		'--publisher-server' => $node_p->connstr($db1),
 		'--socketdir' => $node_s->host,
 		'--subscriber-port' => $node_s->port,
-		'--publication' => 'test_pub_existing',
+		'--publication' => 'test_pub3',
 		'--publication' => 'pub2',
 		'--replication-slot' => 'replslot1',
 		'--replication-slot' => 'replslot2',
@@ -485,19 +483,16 @@ is($result, qq(0),
 # Insert rows on P
 $node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('third row')");
 $node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
+$node_p->safe_psql($db1, "INSERT INTO not_replicated VALUES(0)");
 
 # Start subscriber
 $node_s->start;
 
 # Confirm publications are removed from the subscriber node
-$result = $node_s->safe_psql(
-	$db1, "SELECT COUNT(*) FROM pg_publication;");
-is($result, '0',
-	'all publications were removed from db1 by --clean=publications');
-$result = $node_s->safe_psql(
-	$db2, "SELECT COUNT(*) FROM pg_publication;");
-is($result, '0',
-	'all publications were removed from db2 by --clean=publications');
+is($node_s->safe_psql($db1, 'SELECT COUNT(*) FROM pg_publication'),
+	'0', 'all publications were removed from db1');
+is($node_s->safe_psql($db2, 'SELECT COUNT(*) FROM pg_publication'),
+	'0', 'all publications were removed from db2');
 
 # Verify that all subtwophase states are pending or enabled,
 # e.g. there are no subscriptions where subtwophase is disabled ('d')
@@ -538,6 +533,9 @@ is( $result, qq(first row
 second row
 third row),
 	"logical replication works in database $db1");
+$result = $node_s->safe_psql($db1, 'SELECT * FROM not_replicated');
+is($result, qq(),
+	"table is not replicated in database $db1");
 
 # Check result in database $db2
 $result = $node_s->safe_psql($db2, 'SELECT * FROM tbl2');
@@ -550,142 +548,40 @@ my $sysid_s = $node_s->safe_psql('postgres',
 	'SELECT system_identifier FROM pg_control_system()');
 isnt($sysid_p, $sysid_s, 'system identifier was changed');
 
-# Initialize node_s2 and node_s3 as a fresh standby of node_p for existing/new
-# publication test.
-$node_p->backup('backup_tablepub');
-my $node_s2 = PostgreSQL::Test::Cluster->new('node_s2');
-$node_s2->init_from_backup($node_p, 'backup_tablepub', has_streaming => 1);
-$node_s2->start;
-$node_s2->stop;
+# Verify that pub2 was created in $db2
+is($node_p->safe_psql($db2, "SELECT COUNT(*) FROM pg_publication WHERE pubname = 'pub2'"),
+	'1', "publication pub2 was created in $db2");
 
-my $node_s3 = PostgreSQL::Test::Cluster->new('node_s3');
-$node_s3->init_from_backup($node_p, 'backup_tablepub', has_streaming => 1);
-$node_s3->start;
-$node_s3->stop;
-
-# Remember the database publications. Later we will compare against these to
-# see if the dry-run changed anything.
-my $db1_pubnames = $node_p->safe_psql($db1,
-    "SELECT pubname FROM pg_publication ORDER BY pubname");
-my $db2_pubnames = $node_p->safe_psql($db2,
-    "SELECT pubname FROM pg_publication ORDER BY pubname");
-
-# Test publication reuse and creation behavior with --dry-run.
-# This should reuse existing 'test_pub_existing' and create new 'test_pub_new',
-# demonstrating mixed publication handling without actual changes.
-($stdout, $stderr) = run_command(
-	[
-		'pg_createsubscriber',
-		'--verbose',
-		'--dry-run',
-		'--pgdata' => $node_s2->data_dir,
-		'--publisher-server' => $node_p->connstr($db1),
-		'--socketdir' => $node_s2->host,
-		'--subscriber-port' => $node_s2->port,
-		'--database' => $db1,
-		'--database' => $db2,
-		'--publication' => 'test_pub_existing',
-		'--publication' => 'test_pub_new',
-	],
-	'run pg_createsubscriber --dry-run on node S2');
-
-like(
-	$stderr,
-	qr/use existing publication "test_pub_existing"/,
-	'dry-run logs reuse of existing publication');
-like(
-	$stderr,
-	qr/dry-run: would create publication "test_pub_new"/,
-	'dry-run logs creation of new publication');
-
-# Verify dry-run did not modify publisher state
-$result = $node_p->safe_psql($db1,
-	"SELECT pubname FROM pg_publication ORDER BY pubname");
-is($result, $db1_pubnames,
-	"dry-run does not remove or create publications in db1");
-$result = $node_p->safe_psql($db2,
-	"SELECT pubname FROM pg_publication ORDER BY pubname");
-is($result, $db2_pubnames,
-	"dry-run does not remove or create publications in db2");
-
-# Run pg_createsubscriber to test publication reuse and creation behavior.
-# This should reuse the existing 'test_pub_existing' publication in db1 and
-# create a new 'test_pub_new' publication in db2, demonstrating how the tool
-# handles mixed existing/new publication scenarios.
-command_ok(
-	[
-		'pg_createsubscriber',
-		'--verbose', '--verbose',
-		'--recovery-timeout' => $PostgreSQL::Test::Utils::timeout_default,
-		'--pgdata' => $node_s2->data_dir,
-		'--publisher-server' => $node_p->connstr($db1),
-		'--socketdir' => $node_s2->host,
-		'--subscriber-port' => $node_s2->port,
-		'--database' => $db1,
-		'--database' => $db2,
-		'--publication' => 'test_pub_existing',
-		'--publication' => 'test_pub_new',
-	],
-	'run pg_createsubscriber on node S2');
-
-# Start subscriber
-$node_s2->start;
-
-# Verify that test_pub_new was created in db2
-$result = $node_p->safe_psql($db2,
-	"SELECT COUNT(*) FROM pg_publication WHERE pubname = 'test_pub_new'");
-is($result, '1', 'test_pub_new publication was created in db2');
-
-# Insert rows on P
-$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('fourth row')");
-$node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 2')");
-
-# Get subscription names and publications
-$result = $node_s2->safe_psql(
+# Get subscription and publication names
+$result = $node_s->safe_psql(
 	'postgres', qq(
     SELECT subname, subpublications FROM pg_subscription WHERE subname ~ '^pg_createsubscriber_'
 	ORDER BY subpublications;
 ));
 like(
 	$result,
-	qr/^pg_createsubscriber_\d+_[0-9a-f]+ \|\{test_pub_existing\}\n
-        pg_createsubscriber_\d+_[0-9a-f]+ \|\{test_pub_new\}$/x,
-	"Subscription and publication name are ok");
-
-# Check result in database $db1
-$result = $node_s2->safe_psql($db1, 'SELECT * FROM tbl1');
-is( $result, qq(first row
-second row
-third row
-fourth row),
-	"logical replication works in database $db1");
-
-# Check result in database $db2
-$result = $node_s2->safe_psql($db2, 'SELECT * FROM tbl2');
-is( $result, qq(row 1
-row 2),
-	"logical replication works in database $db2");
+	qr/^pg_createsubscriber_\d+_[0-9a-f]+ \|\{pub2\}\n
+        pg_createsubscriber_\d+_[0-9a-f]+ \|\{test_pub3\}$/x,
+	'subscription and publication names are ok');
 
 # Verify that the correct publications are being used
-$result = $node_s2->safe_psql(
+$result = $node_s->safe_psql(
 	'postgres', qq(
-        SELECT d.datname, s.subpublications
-        FROM pg_subscription s
+		SELECT d.datname, s.subpublications
+		FROM pg_subscription s
 		JOIN pg_database d ON d.oid = s.subdbid
-        WHERE subname ~ '^pg_createsubscriber_'
-        ORDER BY s.subdbid
+		WHERE subname ~ '^pg_createsubscriber_'
+		ORDER BY s.subdbid
     )
 );
 
-is( $result, qq($db1|{test_pub_existing}
-$db2|{test_pub_new}),
+is($result, qq($db1|{test_pub3}
+$db2|{pub2}),
 	"subscriptions use the correct publications");
 
 # clean up
 $node_p->teardown_node;
 $node_s->teardown_node;
-$node_s2->teardown_node;
-$node_s3->teardown_node;
 $node_t->teardown_node;
 $node_f->teardown_node;
 
-- 
2.39.5