v5_REL_18-0001-Fix-duplicate-insert-during-pg_createsubsc.txt

From 772290f7a113b54f377484671f92c67eaae62773 Mon Sep 17 00:00:00 2001
From: Shlok Kyal <shlok.kyal.oss@gmail.com>
Date: Mon, 28 Apr 2025 11:18:01 +0530
Subject: [PATCH v5_REL_18] Fix duplicate insert during pg_createsubscriber

When pg_createsubscriber is run, the standby node is recovered to a
point 'consistent_lsn' and promoted. Then, when the subscription is
created, the replication origin is advanced to the 'consistent_lsn'.
Then the subscription is enabled and the apply worker starts to send
changes from 'consistent_lsn'.

When this 'consistent_lsn' is an LSN corresponding to a COMMIT, the
records for the transaction for that COMMIT are already replicated to
the standby node during the recovery phase. Now, when the subscription
is created, the replication origin is advanced and the subscription is
enabled. The apply worker starts to apply changes from 'consistent_lsn'.
So, records corresponding to the transaction whose COMMIT LSN is
'consistent_lsn' are replicated again.

To avoid this, set recovery_target_inclusive = false instead of true.
So the standby will recovery till point just before 'consistent_lsn'.
And apply worker will start(after enabling of subscription) to apply
changes from 'consistent_lsn'. So, above scenorio is avoided.
---
 src/bin/pg_basebackup/pg_createsubscriber.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 11f71c03801..5b3ea435775 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1251,7 +1251,7 @@ setup_recovery(const struct LogicalRepInfo *dbinfo, const char *datadir, const c
 	appendPQExpBufferStr(recoveryconfcontents,
 						 "recovery_target_timeline = 'latest'\n");
 	appendPQExpBufferStr(recoveryconfcontents,
-						 "recovery_target_inclusive = true\n");
+						 "recovery_target_inclusive = false\n");
 	appendPQExpBufferStr(recoveryconfcontents,
 						 "recovery_target_action = promote\n");
 	appendPQExpBufferStr(recoveryconfcontents, "recovery_target_name = ''\n");
-- 
2.34.1