Horton

Re: Synchronizing slots from primary to standby

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: "Drouvot, Bertrand" <bertranddrouvot.pg@gmail.com>
Cc: shveta malik <shveta.malik@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>
Date: 2023-10-04T11:30:01Z
Lists: pgsql-hackers 
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?

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

> > 2) If standby sends '*', then we need to update each logical-slot with
> > that standby-name. Or do we have better way to deal with '*'? Need to
> > think more on this.
> >

I can't see any better way.

> > JFYI, on the similar line, currently in ReplicationSlotPersistentData,
> > we are maintaining a flag for slot-sync feature which is:
> >
> >          bool            synced; /* Is this a slot created by a
> > sync-slot worker? */
> >
> > This flag currently holds significance only on physical-standby. This
> > has been added to distinguish between a slot created by user for
> > logical decoding purpose and the ones being synced from primary.
>
> BTW, what about having this "user visible" through pg_replication_slots?
>

We can do that.

-- 
With Regards,
Amit Kapila.

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