Re: Synchronizing slots from primary to standby

On Tue, Oct 24, 2023 at 3:35 PM Drouvot, Bertrand
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> On 10/24/23 7:44 AM, Ajin Cherian wrote:
> > On Mon, Oct 23, 2023 at 11:22 PM Drouvot, Bertrand
> > <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> 2) When we create a subscription, another slot is created during the subscription synchronization, namely
> like "pg_16397_sync_16388_7293447291374081805" (coming from ReplicationSlotNameForTablesync()).
>
> This extra slot appears to have failover also set to true.
>
> So, If the standby refresh the list of slot to sync when the subscription is still synchronizing we'd see things like
> on the standby:
>
> LOG: waiting for remote slot "mysub" LSN (0/C0034808) and catalog xmin (756) to pass local slot LSN (0/C0034840) and and catalog xmin (756)
> LOG: wait over for remote slot "mysub" as its LSN (0/C00368B0) and catalog xmin (756) has now passed local slot LSN (0/C0034840) and catalog xmin (756)
> LOG: waiting for remote slot "pg_16397_sync_16388_7293447291374081805" LSN (0/C0034808) and catalog xmin (756) to pass local slot LSN (0/C00368E8) and and catalog xmin (756)
> WARNING: slot "pg_16397_sync_16388_7293447291374081805" disappeared from the primary, aborting slot creation
>
> I'm not sure this "pg_16397_sync_16388_7293447291374081805" should have failover set to true. If there is a failover
> during the subscription creation, better to re-launch the subscription instead?
>

But note that the subscription doesn't wait for the completion of
tablesync. So, how will we deal with that? Also, this situation is the
same for non-tablesync slots as well. I have given another option in
the email [1] which is to enable failover even for the main slot after
all tables are in ready state, something similar to what we do for
two_phase.

[1] - https://www.postgresql.org/message-id/CAA4eK1J6BqO5%3DueFAQO%2BaYyHLaU-oCHrrVFJqHS-i0Ce9aPY2w%40mail.gmail.com

--
With Regards,
Amit Kapila.