Re: BUG #18897: Logical replication conflict after using pg_createsubscriber under heavy load - Mailing list pgsql-bugs

On Thu, 24 Apr 2025 at 11:57, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
>
> On Thu, 24 Apr 2025 at 09:08, vignesh C <vignesh21@gmail.com> wrote:
> >
> > On Wed, 23 Apr 2025 at 07:29, Euler Taveira <euler@eulerto.com> wrote:
> > >
> > > On Wed, Apr 16, 2025, at 8:14 PM, PG Bug reporting form wrote:
> > >
> > > I'm in the process of converting our databases from pglogical logical
> > > replication to the native logical replication implementation on PostgreSQL
> > > 17. One of the bugs we encountered and had to work around with pglogical was
> > > the plugin dropping records while converting to a streaming replica to
> > > logical via pglogical_create_subscriber (reported
> > > https://github.com/2ndQuadrant/pglogical/issues/349). I was trying to
> > > confirm that the native logical replication implementation did not have this
> > > problem, and I've found that it might have a different problem.
> > >
> > >
> > > pg_createsubscriber uses a different approach than pglogical. While pglogical
> > > uses a restore point, pg_createsubscriber uses the LSN from the latest
> > > replication slot as a replication start point. The restore point approach is
> > > usually suitable to physical replication but might not cover all scenarios for
> > > logical replication (such as when there are in progress transactions). Since
> > > creating a logical replication slot does find a consistent decoding start
> > > point, it is a natural choice to start the logical replication (that also needs
> > > to find a decoding start point).
> >
> > I observed a difference between logical replication and
> > pg_createsubscriber in how the replication origin is set, which can
> > lead to different behaviors during replication restarts.
> >
> > Consider the following WAL records:
> > rmgr: Transaction len (rec/tot):     46/    46, tx:        767, lsn:
> > 0/01756868, prev 0/01756780, desc: COMMIT 2025-04-23 15:49:24.349942
> > IST
> > rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn:
> > 0/01756898, prev 0/01756868, desc: RUNNING_XACTS nextXid 768
> > latestCompletedXid 767 oldestRunningXid 755; 1 xacts: 755
> >
> > Behavior in Logical Replication
> > In logical replication, after applying the COMMIT record at LSN
> > 0/01756868, the replication origin is set to the end LSN of the commit
> > record, i.e., 0/01756898. On restart, replication resumes from the
> > next LSN after COMMIT records, which is 0/01756898 in this case.
> > As a result, the same transaction is not sent again, and duplicate
> > data is avoided.
> >
> > Behavior in pg_createsubscriber
> > However, in the case of pg_createsubscriber, the consistent point used
> > for creating the replication slot on the publisher may be set exactly
> > at the commit LSN (0/01756868, xid 767). When promoting the standby,
> > this same LSN is used as recovery_target_lsn, so the standby recovers
> > up to and including the commit of transaction 767.
> >
> > After promotion, if the replication origin is also set to this same
> > commit LSN, the subscriber will request changes starting from that
> > point. Since the origin doesn't reflect the commit as applied,
> > transaction 767 gets replicated again, leading to duplicate data and
> > possible replication failure.
> >
> > If the issue is not reproducible using Zane's suggested steps, we can
> > try the following:
> > 1) Stop the standby server.
> > 2) Perform an insert transaction and note the commit LSN using pg_waldump.
> > 3) Set up the publisher, replication slot, etc., simulating the
> > pg_createsubscriber behavior.
> > 4) Restart the standby with promotion configuration from
> > setup_recovery(), setting recovery_target_lsn to the commit LSN from
> > step 2.
> > 5) Create the subscription and set the replication origin to the same
> > commit LSN.
> > 6) Enable the subscription.
> >
> > This setup should reproduce the issue where the transaction gets
> > applied twice on the subscriber due to the replication origin being
> > aligned with the commit LSN rather than its end.
> >
> > Thoughts?
>
> Hi Vignesh,
>
> I have verified the behaviour shared by you for Logical Replication
> and pg_createsubscriber and agree with your analysis.
> I have also tried the steps shared by you and am able to reproduce the issue.
>
> I am thinking of resolving it by introducing a new API, which can give
> us the next lsn to the lsn provided.
> During pg_createsusbcriber run where we are advancing the replication
> origin to 'consistent_lsn'. I think we should advance it to the next
> lsn of 'consistent_lsn' instead.
> I think this will resolve the issue. Thoughts?
>

I have created a patch to fix the issue reported by Zane.
I ran the test script with the patch and was not able to reproduce the
issue. The script ran for around ~6hrs.

The patch applies on HEAD.


Thanks and Regards,
Shlok Kyal