0001-pg_createsubscriber-fix-slow-recovery.patch

From f4afe05fc7e73c5c23bcdeba4fc65a538c83b8ba Mon Sep 17 00:00:00 2001
From: Euler Taveira <euler@eulerto.com>
Date: Mon, 29 Jul 2024 19:44:16 -0300
Subject: [PATCH 1/2] pg_createsubscriber: fix slow recovery

If the primary server is idle when you are running pg_createsubscriber,
it used to take some time during recovery. The reason is that it was
using the LSN returned by pg_create_logical_replication_slot as
recovery_target_lsn. This LSN points to the next WAL record that might
not be available at WAL, hence, the recovery routine waits until some
activity writes a WAL record to end the recovery. Inject a new WAL
record after the last replication slot to avoid slowness.

Discussion: https://www.postgresql.org/message-id/2377319.1719766794%40sss.pgh.pa.us
---
 src/bin/pg_basebackup/pg_createsubscriber.c | 23 +++++++++++++++++++++
 1 file changed, 23 insertions(+)

diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index b02318782a6..00976c643a1 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -778,6 +778,29 @@ setup_publisher(struct LogicalRepInfo *dbinfo)
 		else
 			exit(1);
 
+		/*
+		 * An idle server might not write a new WAL record until the recovery
+		 * is about to end. Since pg_createsubscriber is using the LSN
+		 * returned by the last replication slot as recovery_target_lsn, this
+		 * LSN is ahead of the current WAL position and the recovery waits
+		 * until something writes a WAL record to reach the target and ends
+		 * the recovery. To avoid the recovery slowness in this case, injects
+		 * a new WAL record here.
+		 */
+		if (i == num_dbs - 1 && !dry_run)
+		{
+			PGresult   *res;
+
+			res = PQexec(conn, "SELECT pg_log_standby_snapshot()");
+			if (PQresultStatus(res) != PGRES_TUPLES_OK)
+			{
+				pg_log_error("could not write an additional WAL record: %s",
+							 PQresultErrorMessage(res));
+				disconnect_database(conn, true);
+			}
+			PQclear(res);
+		}
+
 		disconnect_database(conn, false);
 	}
 
-- 
2.30.2