On Fri, Feb 2, 2024 at 1:58 PM Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Fri, Feb 2, 2024 at 6:46 AM Masahiko Sawada <sawada.m...@gmail.com> wrote: > > > > On Thu, Feb 1, 2024 at 12:51 PM Amit Kapila <amit.kapil...@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