On Sun, Dec 7, 2025, at 11:52 PM, Peter Smith wrote:
>
> OK. Done as suggested in v23.
>
I took another look at this patch. I noticed that it increased the test time in
3 or 4s. I started writing the suggestions to this email but since it increases
a lot, it is better to provide a patch and explain the main points. Regarding
the tests, there are 2 new nodes (one used to test this feature -- node_s2 --
and another one with no real function -- node_s3). There is no need to add new
nodes, just use the created publication into one of the existing tests. That's
what I did (see test_pub3). I also added a new table to ensure the publication
only contains tbl1 and not not_replicated table. I removed the dry run part
because it only increases the total time and doesn't improve coverage. Finally,
coverage stays the same and a small increment in the execution time (less than
1s).
$ tail -n 8 src/bin/pg_basebackup/pg_createsubscriber.c.gcov.out
File 'pg_createsubscriber.c'
Lines executed:83.81% of 951
Branches executed:93.15% of 467
Taken at least once:74.73% of 467
Calls executed:73.48% of 675
Creating 'pg_createsubscriber.c.gcov'
Lines executed:83.81% of 951
I also changed the code a bit. In particular, I don't like
check_publication_exists and find_publication seems cleaner to me so I renamed
it. I replaced the psprintf because the pattern is to use PQExpBuffer functions
for queries. Added pg_catalog to the query. I changed pg_fatal with pg_log_error
plus disconnect_database -- see previous discussion about it. I added a comment
explaining why made_publication = false. Finally, I refactored the logic that
drops publications; the if (!drop_all_pubs || dry_run) is rather confusing.
Documentation was also changed a bit. One paragraph instead of two because it is
talking about the same topic.
Here is your v23 plus the fixes that I described above. IMO it is good to go so
I set it to Ready for Committer. Additional comments are always welcome.
--
Euler Taveira
EDB https://www.enterprisedb.com/
From 4b8075327dd2c783ec75852e510d1529325e7208 Mon Sep 17 00:00:00 2001
From: Peter Smith <[email protected]>
Date: Mon, 8 Dec 2025 13:44:16 +1100
Subject: [PATCH v24 1/2] Support existing publications in pg_createsubscriber
Allow pg_createsubscriber to reuse existing publications instead of failing
when they already exist on the publisher.
Previously, pg_createsubscriber would fail if any specified publication already
existed. Now, existing publications are reused as-is with their current
configuration, and non-existing publications are created automatically with
FOR ALL TABLES.
This change provides flexibility when working with mixed scenarios of existing
and new publications. Users should verify that existing publications have the
desired configuration before reusing them, and can use --dry-run to see which
publications will be reused and which will be created.
Only publications created by pg_createsubscriber are cleaned up during error
cleanup operations. Pre-existing publications are preserved unless
'--clean=publications' is explicitly specified, which drops all publications.
Author: Shubham Khanna <[email protected]>
Reviewed-by: Peter Smith <[email protected]>
Reviewed-by: Zhijie Hou (Fujitsu)" <[email protected]
Reviewed-by: Euler Taveira" <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: vignesh C <[email protected]>
Reviewed-by: tianbing <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAHv8Rj%2BsxWutv10WiDEAPZnygaCbuY2RqiLMj2aRMH-H3iZwyA%40mail.gmail.com
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 11 ++
src/bin/pg_basebackup/pg_createsubscriber.c | 82 +++++++--
.../t/040_pg_createsubscriber.pl | 156 +++++++++++++++++-
3 files changed, 228 insertions(+), 21 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index bb9cc72576c..75e2edc83fd 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -285,6 +285,17 @@ PostgreSQL documentation
a generated name is assigned to the publication name. This option cannot
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.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index ef6deec14af..a6f9e21ccd4 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -116,6 +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 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);
@@ -763,6 +764,33 @@ generate_object_name(PGconn *conn)
return objname;
}
+/*
+ * Return whether a specified publication exists in the specified database.
+ */
+static bool
+check_publication_exists(PGconn *conn, const char *pubname, const char *dbname)
+{
+ PGresult *res;
+ bool exists;
+ char *query;
+ char *pubname_esc = PQescapeLiteral(conn, pubname, strlen(pubname));
+
+ query = psprintf("SELECT 1 FROM pg_publication WHERE pubname = %s",
+ pubname_esc);
+ res = PQexec(conn, query);
+
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pg_fatal("could not check for publication \"%s\" in database \"%s\": %s",
+ pubname, dbname, PQerrorMessage(conn));
+
+ exists = (PQntuples(res) == 1);
+
+ PQfreemem(pubname_esc);
+ PQclear(res);
+ pg_free(query);
+ return exists;
+}
+
/*
* Create the publications and replication slots in preparation for logical
* replication. Returns the LSN from latest replication slot. It will be the
@@ -799,13 +827,24 @@ setup_publisher(struct LogicalRepInfo *dbinfo)
if (num_replslots == 0)
dbinfo[i].replslotname = pg_strdup(dbinfo[i].subname);
- /*
- * Create publication on publisher. This step should be executed
- * *before* promoting the subscriber to avoid any transactions between
- * consistent LSN and the new publication rows (such transactions
- * wouldn't see the new publication rows resulting in an error).
- */
- create_publication(conn, &dbinfo[i]);
+ if (check_publication_exists(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);
+ dbinfo[i].made_publication = false;
+ }
+ else
+ {
+ /*
+ * Create publication on publisher. This step should be executed
+ * *before* promoting the subscriber to avoid any transactions
+ * between consistent LSN and the new publication rows (such
+ * transactions wouldn't see the new publication rows resulting in
+ * an error).
+ */
+ create_publication(conn, &dbinfo[i]);
+ }
/* Create replication slot on publisher */
if (lsn)
@@ -1749,11 +1788,10 @@ drop_publication(PGconn *conn, const char *pubname, const char *dbname,
/*
* Retrieve and drop the publications.
*
- * Since the publications were created before the consistent LSN, they
- * remain on the subscriber even after the physical replica is
- * promoted. Remove these publications from the subscriber because
- * they have no use. Additionally, if requested, drop all pre-existing
- * publications.
+ * Publications copied during physical replication remain on the subscriber
+ * after promotion. If --clean=publications is specified, drop all existing
+ * publications in the subscriber database. Otherwise, only drop publications
+ * that were created by pg_createsubscriber during this operation.
*/
static void
check_and_drop_publications(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1787,12 +1825,24 @@ check_and_drop_publications(PGconn *conn, struct LogicalRepInfo *dbinfo)
}
/*
- * In dry-run mode, we don't create publications, but we still try to drop
- * those to provide necessary information to the user.
+ * 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)
- drop_publication(conn, dbinfo->pubname, dbinfo->dbname,
- &dbinfo->made_publication);
+ {
+ 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);
+ }
}
/*
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 3d6086dc489..c07bde2728d 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -443,10 +443,17 @@ 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.
+$node_p->safe_psql($db1,
+ "CREATE PUBLICATION test_pub_existing FOR TABLE tbl1");
+
# Run pg_createsubscriber on node S. --verbose is used twice
# to show more information.
-# In passing, also test the --enable-two-phase option and
-# --clean option
+#
+# 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).
command_ok(
[
'pg_createsubscriber',
@@ -456,7 +463,7 @@ command_ok(
'--publisher-server' => $node_p->connstr($db1),
'--socketdir' => $node_s->host,
'--subscriber-port' => $node_s->port,
- '--publication' => 'pub1',
+ '--publication' => 'test_pub_existing',
'--publication' => 'pub2',
'--replication-slot' => 'replslot1',
'--replication-slot' => 'replslot2',
@@ -483,8 +490,14 @@ $node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
$node_s->start;
# Confirm publications are removed from the subscriber node
-is($node_s->safe_psql($db1, "SELECT COUNT(*) FROM pg_publication;"),
- '0', 'all publications on subscriber have been removed');
+$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');
# Verify that all subtwophase states are pending or enabled,
# e.g. there are no subscriptions where subtwophase is disabled ('d')
@@ -537,9 +550,142 @@ 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;
+
+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(
+ '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");
+
+# Verify that the correct publications are being used
+$result = $node_s2->safe_psql(
+ 'postgres', qq(
+ 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
+ )
+);
+
+is( $result, qq($db1|{test_pub_existing}
+$db2|{test_pub_new}),
+ "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
From f6cd0469b62b091cc62997201ec3fb2934331f72 Mon Sep 17 00:00:00 2001
From: Euler Taveira <[email protected]>
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
