Horton

Re: Synchronizing slots from primary to standby

shveta malik <shveta.malik@gmail.com>

View thread View on postgr.es
From: shveta malik <shveta.malik@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: "Drouvot, Bertrand" <bertranddrouvot.pg@gmail.com>, Peter Smith <smithpb2250@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>, Ajin Cherian <itsajin@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, shveta malik <shveta.malik@gmail.com>
Date: 2023-10-04T11:50:30Z
Lists: pgsql-hackers 
On Wed, Oct 4, 2023 at 5:00 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Wed, Oct 4, 2023 at 11:55 AM Drouvot, Bertrand
> <bertranddrouvot.pg@gmail.com> wrote:
> >
> > On 10/4/23 6:26 AM, shveta malik wrote:
> > > On Wed, Oct 4, 2023 at 5:36 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >>
> > >>
> > >> How about an alternate scheme where we define sync_slot_names on
> > >> standby but then store the physical_slot_name in the corresponding
> > >> logical slot (ReplicationSlotPersistentData) to be synced? So, the
> > >> standby will send the list of 'sync_slot_names' and the primary will
> > >> add the physical standby's slot_name in each of the corresponding
> > >> sync_slot. Now, if we do this then even after restart, we should be
> > >> able to know for which physical slot each logical slot needs to wait.
> > >> We can even provide an SQL API to reset the value of
> > >> standby_slot_names in logical slots as a way to unblock decoding in
> > >> case of emergency (for example, corresponding when physical standby
> > >> never comes up).
> > >>
> > >
> > >
> > > Looks like a better approach to me. It solves most of the pain points like:
> > > 1) Avoids the need of multiple GUCs
> > > 2) Primary and standby need not to worry to be in sync if we maintain
> > > sync-slot-names GUC on both
>
> As per my understanding of this approach, we don't want
> 'sync-slot-names' to be set on the primary. Do you have a different
> understanding?
>

Same understanding. We do not need it to be set on primary by user. It
will be GUC on standby and standby will convey it to primary.

> > > 3) User still gets the flexibility to remove a standby from wait-lost
> > > of primary's logical-walsenders' using reset SQL API.
> > >
> >
> > Fully agree.
> >
> > > Now some initial thoughts:
> > > 1) Since each logical slot could be needed to be synched by multiple
> > > physical-standbys, so in ReplicationSlotPersistentData, we need to
> > > hold a list of standby's name. So this brings us to question as in how
> > > much shall we allocate initially in shared-memory? Shall it be for
> > > max_replication_slots (worst case scenario) in each
> > > ReplicationSlotPersistentData to hold physical-standby names?
> > >
> >
> > Yeah, and even if we do the opposite means add the 'to-sync'
> > logical replication slot in the ReplicationSlotPersistentData of the physical
> > slot(s) the questions still remain (as a physical standby could want to
> > sync multiples slots)
> >
>
> I think we don't need to allocate the entire max_replication_slots
> array in ReplicationSlotPersistentData. We should design something
> like the variable amount of data to be written on disk should be
> represented similar to what we do with variable TransactionIds in
> SnapBuildOnDisk. Now, we also need to store the list of standby's
> in-memory either shared or local memory of walsender. I think storing
> it in shared-memory say in ReplicationSlot has the advantage that we
> can easily set that via physical walsender and it may be easier to
> maintain both for manually created logical slots and logical slots
> associated with logical walsenders. But still this needs some thoughts
> as to what is the best way to store this information.
>

Thanks for the idea, I will review this.

thanks
Shveta

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).