0002-Remove-failover-replication-slots-on-subscriber.patch

From a10edc03a6636d6fef9e013a507a8258096a6fd3 Mon Sep 17 00:00:00 2001
From: Euler Taveira <euler.taveira@enterprisedb.com>
Date: Wed, 22 May 2024 12:43:13 -0300
Subject: [PATCH 2/4] Remove failover replication slots on subscriber

After running pg_createsubscriber, these replication slots has no use on
subscriber.
---
 doc/src/sgml/ref/pg_createsubscriber.sgml     |  8 +++
 src/bin/pg_basebackup/pg_createsubscriber.c   | 49 ++++++++++++++++++-
 .../t/040_pg_createsubscriber.pl              | 20 +++++++-
 3 files changed, 75 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index a700697f88..ac56c0cce9 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -482,6 +482,14 @@ PostgreSQL documentation
      </para>
     </step>
 
+    <step>
+     <para>
+      If the standby server contains
+      <link linkend="logicaldecoding-replication-slots-synchronization">failover replication slots</link>,
+      they cannot be synchronized anymore so drop them.
+     </para>
+    </step>
+
     <step>
      <para>
       Update the system identifier on the target server. The
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index f62f34b1a7..b42160c6c6 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -85,6 +85,7 @@ static void setup_recovery(const struct LogicalRepInfo *dbinfo, const char *data
 						   const char *lsn);
 static void drop_primary_replication_slot(struct LogicalRepInfo *dbinfo,
 										  const char *slotname);
+static void drop_failover_replication_slots(struct LogicalRepInfo *dbinfo);
 static char *create_logical_replication_slot(PGconn *conn,
 											 struct LogicalRepInfo *dbinfo);
 static void drop_replication_slot(PGconn *conn, struct LogicalRepInfo *dbinfo,
@@ -1178,6 +1179,49 @@ drop_primary_replication_slot(struct LogicalRepInfo *dbinfo, const char *slotnam
 	}
 }
 
+/*
+ * Drop failover replication slots on subscriber. After the transformation, it
+ * has no use.
+ *
+ * XXX we might not fail here. Instead, we provide a warning so the user
+ * eventually drops this replication slot later.
+ */
+static void
+drop_failover_replication_slots(struct LogicalRepInfo *dbinfo)
+{
+	PGconn	   *conn;
+	PGresult   *res;
+
+	conn = connect_database(dbinfo[0].subconninfo, false);
+	if (conn != NULL)
+	{
+		/* Get failover replication slot names. */
+		res = PQexec(conn,
+					 "SELECT slot_name FROM pg_catalog.pg_replication_slots WHERE failover");
+
+		if (PQresultStatus(res) == PGRES_TUPLES_OK)
+		{
+			/* Remove failover replication slots from subscriber. */
+			for (int i = 0; i < PQntuples(res); i++)
+				drop_replication_slot(conn, &dbinfo[0], PQgetvalue(res, i, 0));
+		}
+		else
+		{
+			pg_log_warning("could not obtain failover replication slot information: %s",
+						   PQresultErrorMessage(res));
+			pg_log_warning_hint("Drop the failover replication slots on subscriber soon to avoid retention of WAL files.");
+		}
+
+		PQclear(res);
+		disconnect_database(conn, false);
+	}
+	else
+	{
+		pg_log_warning("could not drop failover replication slot");
+		pg_log_warning_hint("Drop the failover replication slots on subscriber soon to avoid retention of WAL files.");
+	}
+}
+
 /*
  * Create a logical replication slot and returns a LSN.
  *
@@ -1309,7 +1353,7 @@ start_standby_server(const struct CreateSubscriberOptions *opt, bool restricted_
 	PQExpBuffer pg_ctl_cmd = createPQExpBuffer();
 	int			rc;
 
-	appendPQExpBuffer(pg_ctl_cmd, "\"%s\" start -D \"%s\" -s",
+	appendPQExpBuffer(pg_ctl_cmd, "\"%s\" start -D \"%s\" -s -o \"-c sync_replication_slots=off\"",
 					  pg_ctl_path, subscriber_dir);
 	if (restricted_access)
 	{
@@ -2111,6 +2155,9 @@ main(int argc, char **argv)
 	/* Remove primary_slot_name if it exists on primary */
 	drop_primary_replication_slot(dbinfo, primary_slot_name);
 
+	/* Remove failover  replication slots if it exists on subscriber */
+	drop_failover_replication_slots(dbinfo);
+
 	/* Stop the subscriber */
 	pg_log_info("stopping the subscriber");
 	stop_standby_server(subscriber_dir);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index a677fefa94..0516d4e17e 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -88,6 +88,7 @@ command_fails(
 
 # Set up node P as primary
 my $node_p = PostgreSQL::Test::Cluster->new('node_p');
+my $pconnstr = $node_p->connstr;
 $node_p->init(allows_streaming => 'logical');
 $node_p->start;
 
@@ -122,6 +123,8 @@ $node_s->init_from_backup($node_p, 'backup_1', has_streaming => 1);
 $node_s->append_conf(
 	'postgresql.conf', qq[
 primary_slot_name = '$slotname'
+primary_conninfo = '$pconnstr dbname=postgres'
+hot_standby_feedback = on
 ]);
 $node_s->set_standby_mode();
 $node_s->start;
@@ -260,6 +263,16 @@ max_worker_processes = 8
 # Restore default settings on both servers
 $node_p->restart;
 
+# Create failover slot to test its removal
+my $fslotname = 'failover_slot';
+$node_p->safe_psql('pg1',
+	"SELECT pg_create_logical_replication_slot('$fslotname', 'pgoutput', false, false, true)");
+$node_s->start;
+$node_s->safe_psql('postgres', "SELECT pg_sync_replication_slots()");
+my $result = $node_s->safe_psql('postgres', "SELECT slot_name FROM pg_replication_slots WHERE slot_name = '$fslotname' AND synced AND NOT temporary");
+is($result, 'failover_slot', 'failover slot is synced');
+$node_s->stop;
+
 # dry run mode on node S
 command_ok(
 	[
@@ -318,7 +331,7 @@ command_ok(
 	'run pg_createsubscriber on node S');
 
 # Confirm the physical replication slot has been removed
-my $result = $node_p->safe_psql('pg1',
+$result = $node_p->safe_psql('pg1',
 	"SELECT count(*) FROM pg_replication_slots WHERE slot_name = '$slotname'"
 );
 is($result, qq(0),
@@ -343,6 +356,11 @@ my @subnames = split("\n", $result);
 $node_s->wait_for_subscription_sync($node_p, $subnames[0]);
 $node_s->wait_for_subscription_sync($node_p, $subnames[1]);
 
+# Confirm the failover slot has been removed
+$result = $node_s->safe_psql('pg1',
+	"SELECT count(*) FROM pg_replication_slots WHERE slot_name = '$fslotname'");
+is($result, qq(0), 'failover slot was removed');
+
 # Check result on database pg1
 $result = $node_s->safe_psql('pg1', 'SELECT * FROM tbl1');
 is( $result, qq(first row
-- 
2.30.2