Horton

Re: Synchronizing slots from primary to standby

Masahiko Sawada <sawada.mshk@gmail.com>

View thread View on postgr.es
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, Peter Smith <smithpb2250@gmail.com>, Bertrand Drouvot <bertranddrouvot.pg@gmail.com>, Ajin Cherian <itsajin@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Nisha Moond <nisha.moond412@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Bruce Momjian <bruce@momjian.us>, Ashutosh Sharma <ashu.coek88@gmail.com>, Andres Freund <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>, Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: 2024-02-02T06:28:11Z
Lists: pgsql-hackers 
On Fri, Feb 2, 2024 at 1:58 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Feb 2, 2024 at 6:46 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Thu, Feb 1, 2024 at 12:51 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > >
> > > > BTW I've tested the following switch/fail-back scenario but it seems
> > > > not to work fine. Am I missing something?
> > > >
> > > > Setup:
> > > > node1 is the primary, node2 is the physical standby for node1, and
> > > > node3 is the subscriber connecting to node1.
> > > >
> > > > Steps:
> > > > 1. [node1]: create a table and a publication for the table.
> > > > 2. [node2]: set enable_syncslot = on and start (to receive WALs from node1).
> > > > 3. [node3]: create a subscription with failover = true for the publication.
> > > > 4. [node2]: promote to the new standby.
> > > > 5. [node3]: alter subscription to connect the new primary, node2.
> > > > 6. [node1]: stop, set enable_syncslot = on (and other required
> > > > parameters), then start as a new standby.
> > > >
> > > > Then I got the error "exiting from slot synchronization because same
> > > > name slot "test_sub" already exists on the standby".
> > > >
> > > > The logical replication slot that was created on the old primary
> > > > (node1) has been synchronized to the old standby (node2). Therefore on
> > > > node2, the slot's "synced" field is true. However, once node1 starts
> > > > as the new standby with slot synchronization, the slotsync worker
> > > > cannot synchronize the slot because the slot's "synced" field on the
> > > > primary is false.
> > > >
> > >
> > > Yeah, we avoided doing anything in this case because the user could
> > > have manually created another slot with the same name on standby.
> > > Unlike WAL slots can be modified on standby as we allow decoding on
> > > standby, so we can't allow to overwrite the existing slots. We won't
> > > be able to distinguish whether the existing slot was a slot that the
> > > user wants to sync with primary or a slot created on standby to
> > > perform decoding. I think in this case user first needs to drop the
> > > slot on new standby.
> >
> > Yes, but if we do a switch-back further (i.e. in above case, node1
> > backs to the primary again and node becomes the standby again), the
> > user doesn't need to remove failover slots since they are already
> > marked as "synced".
>
> But, I think in this case node-2's timeline will be ahead of node-1,
> so will we be able to make node-2 follow node-1 again without any
> additional steps? One thing is not clear to me after promotion the
> timeline changes in WAL, so the locations in slots will be as per new
> timelines, after that will it be safe to sync slots from the new
> primary to old-primary?

In order for node-1 to go back to the primary again, it needs to be
promoted. That is, the node-1's timeline increments and node-2 follows
node-1.

>
> In general, I think after failover, we recommend running pg_rewind if
> the old primary has to follow the new primary to account for
> divergence in WAL. So, not sure we can safely start syncing slots in
> old-primary from new-primary, consider that in the new primary, the
> same name slot may have dropped/re-created multiple times.

Right. And I missed the point that all replication slots are removed
after pg_rewind. It would not be a problem in a failover case. But
probably we still need to consider a switchover cas (i.e. switch roles
with clean shutdowns) since it doesn't require to run pg_rewind?

> We can
> probably reset all the fields of the existing slot the first time
> syncing for an existing slot or do something like that but I think it
> would be better to just re-create the slot.
>
> >
>  I wonder if we could do something automatically to
> > reduce the user's operation.
>
> One possibility is that we forcefully drop/re-create the slot or
> directly overwrite the slot contents but that would probably be better
> done via some GUC or slot-level parameter. I feel we should leave this
> for another day, for the first version, we can document that an error
> will occur if the same name slots on standby exist, so users need to
> ensure that there shouldn't be an existing same name slots on standby
> before sync.
>

Hmm, I'm afraid it might not be user-friendly. But probably we can
leave it for now as it's not impossible.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Commits

  1. Doc: Add the new section "Logical Replication Failover".

  2. Fix the review comments and a bug in the slot sync code.

  3. Fix a test in failover slots regression test.

  4. Fix the intermittent buildfarm failures in 040_standby_failover_slots_sync.

  5. Split XLogCtl->LogwrtResult into separate struct members

  6. Ensure that the sync slots reach a consistent state after promotion without losing data.

  7. Introduce a new GUC 'standby_slot_names'.

  8. Fix BF failure introduced by commit b3f6b14cf4.

  9. Fixups for commit 93db6cbda0.

  10. Fix BF failure in commit 93db6cbda0.

  11. Add a new slot sync worker to synchronize logical slots.

  12. Improve ERROR/LOG messages added by commits ddd5f4f54a and 7a424ece48.

  13. Disable autovacuum on primary in 040_standby_failover_slots_sync test.

  14. Fix the incorrect format specifier used in commit 7a424ece48.

  15. Change the LOG level in 040_standby_failover_slots_sync.pl to DEBUG2.

  16. Add more LOG and DEBUG messages for slot synchronization.

  17. Another try to fix BF failure introduced in commit ddd5f4f54a.

  18. Fix BF introduced in commit ddd5f4f54a.

  19. Add a slot synchronization function.

  20. Clean-ups for 776621a5e4 and 7329240437.

  21. Enhance libpqrcv APIs to support slot synchronization.

  22. Add a failover option to subscriptions.

  23. Allow setting failover property in the replication command.

  24. Allow to enable failover property for replication slots via SQL API.

  25. Add macros for looping through a List without a ListCell.

  26. Track conflict_reason in pg_replication_slots.

  27. Allow upgrades to preserve the full subscription's state.

  28. Add support for incremental backup.

  29. Simplify some logic in CreateReplicationSlot()

  30. Fix uninitialized access to InitialRunningXacts during decoding after ERROR.

  31. Flexible options for CREATE_REPLICATION_SLOT.

  32. Empty search_path in logical replication apply worker and walsender.

  33. Fix oldest xmin and LSN computation across repslots after advancing

  34. Require C99 (and thus MSCV 2013 upwards).