Subject: Re: Synchronizing slots from primary to standby

From: "Drouvot, Bertrand" <[email-masked]>
Date: Fri, 27 Oct 2023 17:30:20 +0200

Hi,

On 10/27/23 10:35 AM, Amit Kapila wrote:
> On Wed, Oct 25, 2023 at 3:15 PM Drouvot, Bertrand
> 
> I think even if we provide such an API, we need to have logic to get
> the slots from the primary and create them.

Yeah, my idea was to add an API (in addition to what is already in place).

> Say, even if the user used
> the APIs, there may still be some new slots that the sync worker needs
> to create. 

Right.

> I think it might be better to provide a view for users to
> view the current state of sync. For example, in the above case, we can
> say "waiting for the primary to advance remote LSN" or something like
> that.

We are already displaying the wait event "ReplSlotsyncPrimaryCatchup" in pg_stat_activity
so that might already be enough?

My main idea was to be able to manually create/sync logical_slot2 in the test case described in [1]
without waiting for activity on logical_slot1.

But another (better?) option might be to change our current algorithm during slot creation on the
standby? (to avoid an "active" slot having to wait on a "inactive" one, like described in [1]).

[1]: https://www.postgresql.org/message-id/afe4ab6c-dde3-48ea-acd8-6f6052c7b8fd%40gmail.com

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

---

From: Amit Kapila <[email-masked]>
Date: Sat, 28 Oct 2023 09:58:53 +0530

> so that might already be enough?

I am fine if the wait is already displayed in some form.

> standby? (to avoid an "active" slot having to wait on a "inactive" one, like described in [1]).

Yeah, I guess it would be better to tweak the algorithm in this case
such that the slots can't be created immediately but can be noted in a
separate list and we can continue with other remaining slots. Once, we
are finished with all the slots, this special list can be traversed
and then we can attempt to create all the remaining slots. OTOH, the
scenario you described doesn't sound to be a frequent case to be
worried for it but if we can deal with it without adding much
complexity then it would be good.

-- 
With Regards,
Amit Kapila.