v8-0001-Enhance-pg_createsubscriber-to-fetch-and-append-a.patch

From 905aa060191760fcb567467728085c1666d82bf3 Mon Sep 17 00:00:00 2001
From: Shubham Khanna <shubham.khanna@fujitsu.com>
Date: Wed, 22 Jan 2025 12:03:12 +0530
Subject: [PATCH v8] Enhance 'pg_createsubscriber' to fetch and append all
 databases

This patch enhances the 'pg_createsubscriber' utility by adding the
'--all-databases' option. When '--all-databases' is specified, the tool
queries the source server (publisher) for all databases and creates
subscriptions on the target server (subscriber) for databases with matching
names. This simplifies the process of converting a physical standby to a
logical subscriber, particularly during upgrades.

The options '--database', '--publication', '--subscription', and
'--replication-slot' cannot be used when '--all-databases' is specified.
---
 doc/src/sgml/ref/pg_createsubscriber.sgml     |  52 +++++++-
 src/bin/pg_basebackup/pg_createsubscriber.c   | 124 +++++++++++++++++-
 .../t/040_pg_createsubscriber.pl              |  98 ++++++++++++++
 3 files changed, 266 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 26b8e64a4e0..08f33479d41 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -20,6 +20,27 @@ PostgreSQL documentation
  </refnamediv>
 
  <refsynopsisdiv>
+  <cmdsynopsis>
+   <command>pg_createsubscriber</command>
+   <arg rep="repeat"><replaceable>option</replaceable></arg>
+   <group choice="plain">
+    <group choice="req">
+     <arg choice="plain"><option>-a</option></arg>
+     <arg choice="plain"><option>--all-databases</option></arg>
+    </group>
+    <group choice="req">
+     <arg choice="plain"><option>-D</option> </arg>
+     <arg choice="plain"><option>--pgdata</option></arg>
+    </group>
+    <replaceable>datadir</replaceable>
+    <group choice="req">
+     <arg choice="plain"><option>-P</option></arg>
+     <arg choice="plain"><option>--publisher-server</option></arg>
+    </group>
+    <replaceable>connstr</replaceable>
+   </group>
+  </cmdsynopsis>
+
   <cmdsynopsis>
    <command>pg_createsubscriber</command>
    <arg rep="repeat"><replaceable>option</replaceable></arg>
@@ -87,6 +108,26 @@ PostgreSQL documentation
    command-line arguments:
 
    <variablelist>
+    <varlistentry>
+     <term><option>-a</option></term>
+     <term><option>--all-databases</option></term>
+     <listitem>
+      <para>
+       Automatically fetch all non-template databases from the source server
+       and create subscriptions for databases with the same names on the
+       target server.
+       If a database is present on the source but missing on the target, an
+       error is raised.
+       If a database exists on the target but not on the source, no
+       subscription is created for it.
+       Subscription names, publication names, and replication slot names are
+       automatically generated. Cannot be used together with
+       <option>--database</option>, <option>--publication</option>,
+       <option>--replication-slot</option> or <option>--subscription</option>.
+      </para>
+     </listitem>
+    </varlistentry>
+
     <varlistentry>
      <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
      <term><option>--database=<replaceable class="parameter">dbname</replaceable></option></term>
@@ -94,7 +135,7 @@ PostgreSQL documentation
       <para>
        The name of the database in which to create a subscription.  Multiple
        databases can be selected by writing multiple <option>-d</option>
-       switches.
+       switches. Cannot be used together with <option>--all-databases</option>.
       </para>
      </listitem>
     </varlistentry>
@@ -214,7 +255,8 @@ PostgreSQL documentation
        names must match the number of specified databases, otherwise an error
        is reported.  The order of the multiple publication name switches must
        match the order of database switches.  If this option is not specified,
-       a generated name is assigned to the publication name.
+       a generated name is assigned to the publication name. Cannot be used
+       together with <option>--all-databases</option>.
       </para>
      </listitem>
     </varlistentry>
@@ -230,7 +272,8 @@ PostgreSQL documentation
        otherwise an error is reported.  The order of the multiple replication
        slot name switches must match the order of database switches.  If this
        option is not specified, the subscription name is assigned to the
-       replication slot name.
+       replication slot name. Cannot be used together with
+       <option>--all-databases</option>.
       </para>
      </listitem>
     </varlistentry>
@@ -245,7 +288,8 @@ PostgreSQL documentation
        names must match the number of specified databases, otherwise an error
        is reported.  The order of the multiple subscription name switches must
        match the order of database switches.  If this option is not specified,
-       a generated name is assigned to the subscription name.
+       a generated name is assigned to the subscription name. Cannot be used
+       together with <option>--all-databases</option>.
       </para>
      </listitem>
     </varlistentry>
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 2d881d54f5b..fe27c3dac62 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -43,6 +43,7 @@ struct CreateSubscriberOptions
 	SimpleStringList sub_names; /* list of subscription names */
 	SimpleStringList replslot_names;	/* list of replication slot names */
 	int			recovery_timeout;	/* stop recovery after this time */
+	bool		all_databases;	/* --all-databases option was specified */
 };
 
 struct LogicalRepInfo
@@ -106,6 +107,7 @@ static void check_and_drop_existing_subscriptions(PGconn *conn,
 												  const struct LogicalRepInfo *dbinfo);
 static void drop_existing_subscriptions(PGconn *conn, const char *subname,
 										const char *dbname);
+static void fetch_source_databases(struct CreateSubscriberOptions *opt);
 
 #define	USEC_PER_SEC	1000000
 #define	WAIT_INTERVAL	1		/* 1 second */
@@ -220,6 +222,7 @@ usage(void)
 	printf(_("Usage:\n"));
 	printf(_("  %s [OPTION]...\n"), progname);
 	printf(_("\nOptions:\n"));
+	printf(_("  -a, --all-databases             create subscriptions on the target for all source databases\n"));
 	printf(_("  -d, --database=DBNAME           database in which to create a subscription\n"));
 	printf(_("  -D, --pgdata=DATADIR            location for the subscriber data directory\n"));
 	printf(_("  -n, --dry-run                   dry run, just show what would be done\n"));
@@ -1861,11 +1864,64 @@ enable_subscription(PGconn *conn, const struct LogicalRepInfo *dbinfo)
 	destroyPQExpBuffer(str);
 }
 
+/*
+ * If --all-databases is specified, fetch a list of all user-created databases
+ * from the source server. Internally, this is treated as if the user specified
+ * multiple --database options, one for each source database.
+ */
+static void
+fetch_source_databases(struct CreateSubscriberOptions *opt)
+{
+	PGconn	   *conn;
+	PGresult   *res;
+	int			num_rows;
+
+	/* Establish a connection to the PostgreSQL server */
+	conn = connect_database(opt->pub_conninfo_str, true);
+
+	res = PQexec(conn, "SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true");
+
+	/* Check for errors during query execution */
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not obtain a list of databases: %s", PQresultErrorMessage(res));
+		PQclear(res);
+		disconnect_database(conn, false);
+		exit(1);
+	}
+
+	/* Process the query result */
+	num_rows = PQntuples(res);
+	for (int i = 0; i < num_rows; i++)
+	{
+		const char *dbname = PQgetvalue(res, i, 0);
+
+		simple_string_list_append(&opt->database_names, dbname);
+
+		/* Increment num_dbs to reflect multiple --database options */
+		num_dbs++;
+	}
+
+	/* Error if no databases were found on the source server */
+	if (num_rows == 0)
+	{
+		pg_log_error("no suitable databases found on the source server");
+		pg_log_error_hint("Ensure that there are non-template and connectable databases on the source server.");
+		PQclear(res);
+		disconnect_database(conn, false);
+		exit(1);
+	}
+
+	PQclear(res);
+	disconnect_database(conn, false);
+}
+
 int
 main(int argc, char **argv)
 {
 	static struct option long_options[] =
 	{
+		{"all-databases", no_argument, NULL, 'a'},
 		{"database", required_argument, NULL, 'd'},
 		{"pgdata", required_argument, NULL, 'D'},
 		{"dry-run", no_argument, NULL, 'n'},
@@ -1928,6 +1984,7 @@ main(int argc, char **argv)
 	opt.socket_dir = NULL;
 	opt.sub_port = DEFAULT_SUB_PORT;
 	opt.sub_username = NULL;
+	opt.all_databases = false;
 	opt.database_names = (SimpleStringList)
 	{
 		0
@@ -1950,12 +2007,49 @@ main(int argc, char **argv)
 
 	get_restricted_token();
 
-	while ((c = getopt_long(argc, argv, "d:D:np:P:s:t:U:v",
+	while ((c = getopt_long(argc, argv, "ad:D:np:P:s:t:U:v",
 							long_options, &option_index)) != -1)
 	{
 		switch (c)
 		{
+			case 'a':
+				if (opt.all_databases)
+				{
+					pg_log_error("--all-databases specified multiple times");
+					exit(1);
+				}
+
+				if (num_dbs > 0)
+				{
+					pg_log_error("--all-databases cannot be used with --database");
+					exit(1);
+				}
+
+				if (num_pubs > 0)
+				{
+					pg_log_error("--all-databases cannot be used with --publication");
+					exit(1);
+				}
+
+				if (num_replslots > 0)
+				{
+					pg_log_error("--all-databases cannot be used with --replication-slot");
+					exit(1);
+				}
+
+				if (num_subs > 0)
+				{
+					pg_log_error("--all-databases cannot be used with --subscription");
+					exit(1);
+				}
+				opt.all_databases = true;
+				break;
 			case 'd':
+				if (opt.all_databases)
+				{
+					pg_log_error("--database cannot be used with --all-databases");
+					exit(1);
+				}
 				if (!simple_string_list_member(&opt.database_names, optarg))
 				{
 					simple_string_list_append(&opt.database_names, optarg);
@@ -1997,6 +2091,11 @@ main(int argc, char **argv)
 				opt.config_file = pg_strdup(optarg);
 				break;
 			case 2:
+				if (opt.all_databases)
+				{
+					pg_log_error("--publication cannot be used with --all-databases");
+					exit(1);
+				}
 				if (!simple_string_list_member(&opt.pub_names, optarg))
 				{
 					simple_string_list_append(&opt.pub_names, optarg);
@@ -2009,6 +2108,11 @@ main(int argc, char **argv)
 				}
 				break;
 			case 3:
+				if (opt.all_databases)
+				{
+					pg_log_error("--replication-slot cannot be used with --all-databases");
+					exit(1);
+				}
 				if (!simple_string_list_member(&opt.replslot_names, optarg))
 				{
 					simple_string_list_append(&opt.replslot_names, optarg);
@@ -2021,6 +2125,11 @@ main(int argc, char **argv)
 				}
 				break;
 			case 4:
+				if (opt.all_databases)
+				{
+					pg_log_error("--subscription cannot be used with --all-databases");
+					exit(1);
+				}
 				if (!simple_string_list_member(&opt.sub_names, optarg))
 				{
 					simple_string_list_append(&opt.sub_names, optarg);
@@ -2092,14 +2201,21 @@ main(int argc, char **argv)
 	pg_log_info("validating subscriber connection string");
 	sub_base_conninfo = get_sub_conninfo(&opt);
 
+	/*
+	 * Fetch all databases from the source (publisher) if --all-databases is
+	 * specified.
+	 */
+	if (opt.all_databases)
+		fetch_source_databases(&opt);
+
 	if (opt.database_names.head == NULL)
 	{
 		pg_log_info("no database was specified");
 
 		/*
-		 * If --database option is not provided, try to obtain the dbname from
-		 * the publisher conninfo. If dbname parameter is not available, error
-		 * out.
+		 * If neither --database nor --all-databases option is provided, try
+		 * to obtain the dbname from the publisher conninfo. If dbname
+		 * parameter is not available, error out.
 		 */
 		if (dbname_conninfo)
 		{
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index c8dbdb7e9b7..33a6c03bf29 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -371,6 +371,104 @@ command_ok(
 	],
 	'run pg_createsubscriber without --databases');
 
+# run pg_createsubscriber with specifying '--all-databases' twice 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-databases', '--all-databases',
+	],
+	qr/--all-databases specified multiple times/,
+	'fail if --all-databases is used more than once');
+
+# run pg_createsubscriber with '--database' and '--all-databases' 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,
+		'--database' => $db1,
+		'--all-databases',
+	],
+	qr/--all-databases cannot be used with --database/,
+	'fail if --all-databases is used with --database');
+
+# run pg_createsubscriber with '--publication' and '--all-databases' 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-databases',
+		'--publication' => 'pub1',
+	],
+	qr/--publication cannot be used with --all-databases/,
+	'fail if --all-databases is used with --publication');
+
+# run pg_createsubscriber with '--replication-slot' and '--all-databases' 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-databases',
+	],
+	qr/--all-databases cannot be used with --replication-slot/,
+	'fail if --all-databases is used with --replication-slot');
+
+# run pg_createsubscriber with '--subscription' and '--all-databases' 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-databases',
+		'--subscription' => 'sub1',
+	],
+	qr/--subscription cannot be used with --all-databases/,
+	'fail if --all-databases is used with --subscription');
+
+# run pg_createsubscriber with '--all-databases' option
+command_ok(
+	[
+		'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-databases',
+	],
+	'run pg_createsubscriber with --all-databases');
+
 # Run pg_createsubscriber on node S.  --verbose is used twice
 # to show more information.
 command_ok(
-- 
2.41.0.windows.3