v20-0012-Avoid-running-pg_createsubscriber-for-cascade-ph.patch

From 4dd92f56f110fa7fea3727c945368e8902d3b9b5 Mon Sep 17 00:00:00 2001
From: Shlok Kyal <shlok.kyal.oss@gmail.com>
Date: Thu, 15 Feb 2024 15:24:11 +0530
Subject: [PATCH v20 12/12] Avoid running pg_createsubscriber for cascade
 physical replication

pg_createsubscriber will throw error when run on a node which is
part of cascade physical replication.
---
 doc/src/sgml/ref/pg_createsubscriber.sgml   |  8 +++-
 src/bin/pg_basebackup/pg_createsubscriber.c | 45 +++++++++++++++++++--
 2 files changed, 48 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 579e50a0a0..115e6a2210 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -72,7 +72,8 @@ PostgreSQL documentation
    </listitem>
    <listitem>
     <para>
-     The target instance must be used as a physical standby.
+     The target instance must be used as a physical standby, and must not do
+     the cascading replication.
     </para>
    </listitem>
    <listitem>
@@ -97,6 +98,11 @@ PostgreSQL documentation
      configured to a value greater than the number of target databases.
     </para>
    </listitem>
+   <listitem>
+    <para>
+     The target instance must not be used as a physical standby.
+    </para>
+   </listitem>
    <listitem>
     <para>
      The source instance must have
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 2458c874e5..05b4783f70 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -675,6 +675,27 @@ check_publisher(LogicalRepInfo *dbinfo)
 	int			cur_walsenders;
 
 	pg_log_info("checking settings on publisher");
+	conn = connect_database(dbinfo[0].pubconninfo);
+	if (conn == NULL)
+		exit(1);
+
+	/*
+	 * The primary server must not be a cascading standby to other node because
+	 * publications would be created.
+	 */
+	res = PQexec(conn, "SELECT pg_catalog.pg_is_in_recovery()");
+
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not obtain recovery progress");
+		return false;
+	}
+
+	if (strcmp(PQgetvalue(res, 0, 0), "t") == 0)
+	{
+		pg_log_error("the primary server is a standby to other server");
+		return false;
+	}
 
 	/*
 	 * Logical replication requires a few parameters to be set on publisher.
@@ -685,10 +706,6 @@ check_publisher(LogicalRepInfo *dbinfo)
 	 * - max_replication_slots >= current + number of dbs to be converted
 	 * - max_wal_senders >= current + number of dbs to be converted
 	 */
-	conn = connect_database(dbinfo[0].pubconninfo);
-	if (conn == NULL)
-		exit(1);
-
 	res = PQexec(conn,
 				 "WITH wl AS "
 				 " (SELECT setting AS wallevel FROM pg_catalog.pg_settings "
@@ -831,6 +848,26 @@ check_subscriber(LogicalRepInfo *dbinfo)
 		return false;
 	}
 
+	/*
+	 * The target server must not be primary for other server. Because the
+	 * pg_createsubscriber would modify the system_identifier at the end of
+	 * run, but walreceiver of another standby would not accept the difference.
+	 */
+	res = PQexec(conn,
+				 "SELECT count(*) from pg_stat_activity where backend_type = 'walsender'");
+
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not obtain walsender information");
+		return false;
+	}
+
+	if (strcmp(PQgetvalue(res, 0, 0), "0") != 0)
+	{
+		pg_log_error("the target server is primary to other server");
+		return false;
+	}
+
 	/*
 	 * Subscriptions can only be created by roles that have the privileges of
 	 * pg_create_subscription role and CREATE privileges on the specified
-- 
2.43.0