v19-0003-Additional-test-cases.patch

From 77f0b90263756fdce856e0e677d12de609f36525 Mon Sep 17 00:00:00 2001
From: Khanna <Shubham.Khanna@fujitsu.com>
Date: Sat, 22 Mar 2025 19:08:30 +0530
Subject: [PATCH v19 3/3] Additional test cases

This patch contains the additional test cases related to the --all option.
---
 .../t/040_pg_createsubscriber.pl              | 151 ++++++++++++++++--
 1 file changed, 141 insertions(+), 10 deletions(-)

diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9355cb0ade9..72a0318d71e 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -41,6 +41,25 @@ sub generate_db
 	return $dbname;
 }
 
+# Wait for subscriptions on the subscriber to catch up all changes.
+sub wait_for_all_subscriptions_caught_up
+{
+	my ($node_p, $node_s) = @_;
+
+	# Get subscription names
+	my $result = $node_s->safe_psql(
+		'postgres', qq(
+		SELECT subname FROM pg_subscription WHERE subname ~ '^pg_createsubscriber_'
+	));
+	my @subnames = split("\n", $result);
+
+	# Wait for all subscriptions to catch up
+	foreach my $subname (@subnames)
+	{
+		$node_p->wait_for_catchup($subname);
+	}
+}
+
 #
 # Test mandatory options
 command_fails(['pg_createsubscriber'],
@@ -386,6 +405,23 @@ command_ok(
 	],
 	'run pg_createsubscriber without --databases');
 
+# run pg_createsubscriber with '--all' and '--database' and verify the
+# failure
+command_fails_like(
+	[
+		'pg_createsubscriber',
+		'--verbose',
+		'--dry-run',
+		'--pgdata' => $node_s->data_dir,
+		'--publisher-server' => $node_p->connstr($db1),
+		'--socketdir' => $node_s->host,
+		'--subscriber-port' => $node_s->port,
+		'--all',
+		'--database' => $db1,
+	],
+	qr/--database cannot be used with --all/,
+	'fail if --database is used with --all');
+
 # run pg_createsubscriber with '--database' and '--all' without '--dry-run'
 # and verify the failure
 command_fails_like(
@@ -419,6 +455,40 @@ command_fails_like(
 	qr/--publication cannot be used with --all/,
 	'fail if --publication is used with --all');
 
+# run pg_createsubscriber with '--replication-slot' and '--all' and
+# verify the failure
+command_fails_like(
+	[
+		'pg_createsubscriber',
+		'--verbose',
+		'--dry-run',
+		'--pgdata' => $node_s->data_dir,
+		'--publisher-server' => $node_p->connstr($db1),
+		'--socketdir' => $node_s->host,
+		'--subscriber-port' => $node_s->port,
+		'--replication-slot' => 'replslot1',
+		'--all',
+	],
+	qr/--replication-slot cannot be used with --all/,
+	'fail if --replication-slot is used with --all');
+
+# run pg_createsubscriber with '--subscription' and '--all' and
+# verify the failure
+command_fails_like(
+	[
+		'pg_createsubscriber',
+		'--verbose',
+		'--dry-run',
+		'--pgdata' => $node_s->data_dir,
+		'--publisher-server' => $node_p->connstr($db1),
+		'--socketdir' => $node_s->host,
+		'--subscriber-port' => $node_s->port,
+		'--all',
+		'--subscription' => 'sub1',
+	],
+	qr/--subscription cannot be used with --all/,
+	'fail if --subscription is used with --all');
+
 # run pg_createsubscriber with '--all' option
 my ($stdout, $stderr) = run_command(
 	[
@@ -502,16 +572,7 @@ $result = $node_s->safe_psql(
 ));
 is($result, qq(0), 'pre-existing subscription was dropped');
 
-# Get subscription names
-$result = $node_s->safe_psql(
-	'postgres', qq(
-	SELECT subname FROM pg_subscription WHERE subname ~ '^pg_createsubscriber_'
-));
-my @subnames = split("\n", $result);
-
-# Wait subscriber to catch up
-$node_s->wait_for_subscription_sync($node_p, $subnames[0]);
-$node_s->wait_for_subscription_sync($node_p, $subnames[1]);
+wait_for_all_subscriptions_caught_up($node_p, $node_s);
 
 # Confirm the failover slot has been removed
 $result = $node_s->safe_psql($db1,
@@ -537,10 +598,80 @@ my $sysid_s = $node_s->safe_psql('postgres',
 	'SELECT system_identifier FROM pg_control_system()');
 ok($sysid_p != $sysid_s, 'system identifier was changed');
 
+$node_s->stop;
+
+# Drop the database $db2 to verify subscriptions are handled correctly
+$node_p->safe_psql('postgres', "DROP DATABASE \"$db2\"");
+
+# On node P create a test table
+$node_p->safe_psql('postgres', 'CREATE TABLE tbl1 (a text)');
+
+# Set up node U as standby linking to node P
+$node_p->backup('backup_3');
+my $node_u = PostgreSQL::Test::Cluster->new('node_u');
+$node_u->init_from_backup($node_p, 'backup_3', has_streaming => 1);
+$node_u->set_standby_mode();
+
+# run pg_createsubscriber with '--all' option without '--dry-run'
+command_ok(
+	[
+		'pg_createsubscriber',
+		'--verbose',
+		'--recovery-timeout' => $PostgreSQL::Test::Utils::timeout_default,
+		'--pgdata' => $node_u->data_dir,
+		'--publisher-server' => $node_p->connstr($db1),
+		'--socketdir' => $node_u->host,
+		'--subscriber-port' => $node_u->port,
+		'--all',
+	],
+	'run pg_createsubscriber with --all');
+
+$node_u->start;
+
+# Verify that user databases (postgres, $db1) got subscriptions.
+$result = $node_u->safe_psql(
+	'postgres',
+	'SELECT datname FROM pg_subscription,
+	pg_database WHERE subdbid = pg_database.oid and datistemplate = \'f\' ORDER BY pg_database.oid'
+);
+is( $result, "postgres
+$db1", 'subscription is created on the required databases');
+
+# Verify template databases do not have subscriptions
+$result = $node_u->safe_psql(
+	'postgres',
+	"SELECT count(*) FROM pg_subscription, pg_database
+	 WHERE subdbid = pg_database.oid and datistemplate = 't';"
+);
+is($result, '0', 'subscription is not created on template databases');
+
+# Verify logical replication works for all databases
+# Insert rows on node P
+$node_p->safe_psql('postgres',
+	"INSERT INTO tbl1 VALUES('row in database postgres')");
+$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('fourth row')");
+
+wait_for_all_subscriptions_caught_up($node_p, $node_u);
+
+# Check result in database 'postgres' of node U
+$result = $node_u->safe_psql('postgres', 'SELECT * FROM tbl1');
+is( $result,
+	qq(row in database postgres),
+	"logical replication works in database postgres");
+
+# Check result in database $db1 of node U
+$result = $node_u->safe_psql($db1, 'SELECT * FROM tbl1');
+is( $result, qq(first row
+second row
+third row
+fourth row),
+	"logical replication works in database $db1");
+
 # clean up
 $node_p->teardown_node;
 $node_s->teardown_node;
 $node_t->teardown_node;
+$node_u->teardown_node;
 $node_f->teardown_node;
 
 done_testing();
-- 
2.34.1