Re: Synchronizing slots from primary to standby

On Mon, Nov 29, 2021 at 11:14 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Mon, Nov 29, 2021 at 9:40 AM Bharath Rupireddy
> <bharath.rupireddyforpostgres@gmail.com> wrote:
> >
> > On Mon, Nov 29, 2021 at 1:48 AM SATYANARAYANA NARLAPURAM
> > <satyanarlapuram@gmail.com> wrote:
> > >
> > >> 3) Instead of the subscriber pulling the slot info, why can't the
> > >> publisher (via the walsender or a new bg worker maybe?) push the
> > >> latest slot info? I'm not sure we want to add more functionality to
> > >> the walsender, if yes, isn't it going to be much simpler?
> > >
> > > Standby pulling the information or at least making a first attempt to connect to the  primary is a better design
asprimary doesn't need to spend its cycles repeatedly connecting to an unreachable standby. In fact, primary wouldn't
evenneed to know the followers, for example followers / log shipping standbys 
> >
> > My idea was to let the existing walsender from the primary/publisher
> > to send the slot info (both logical and physical replication slots) to
> > the standby/subscriber, probably by piggybacking the slot info with
> > the WAL currently it sends. Having said that, I don't know the
> > feasibility of it. Anyways, I'm not in favour of having a new bg
> > worker to just ship the slot info. The standby/subscriber, while
> > making connection to primary/publisher, can choose to get the
> > replication slot info.
>
> I think it is possible that the standby is restoring the WAL directly
> from the archive location and there might not be any wal sender at
> time. So I think the idea of standby pulling the WAL looks better to
> me.

My point was that why can't we let the walreceiver (of course users
can configure it on the standby/subscriber) to choose whether or not
to receive the replication (both physical and logical) slot info from
the primary/publisher and if yes, the walsender(on the
primary/publisher) sending it probably as a new WAL record or just
piggybacking the replication slot info with any of the existing WAL
records.

Or simply a common bg worker (as opposed to the bg worker proposed
originally in this thread which, IIUC, works for logical replication)
running on standby/subscriber for getting both the physical and
logical replication slots info.

> > As I said upthread, the problem I see with standby/subscriber pulling
> > the info is that: how frequently the standby/subscriber is going to
> > sync the slot info from primary/publisher? How can it ensure that the
> > latest information exists say when the subscriber is down/crashed
> > before it picks up the latest slot information?
>
> Yeah that is a good question that how frequently the subscriber should
> fetch the slot information, I think that should be configurable
> values.  And the time delay is more, the chances of losing the latest
> slot is more.

I agree that it should be configurable. Even if the primary/publisher
is down/crashed, one can still compare the latest slot info from both
the primary/publisher and standby/subscriber using a new tool
pg_replslotdata proposed at [1] and see how far and which slots missed
the latest replication slot info and probably drop those alone to
recreate and retain other slots as is.

[1] - https://www.postgresql.org/message-id/CALj2ACW0rV5gWK8A3m6_X62qH%2BVfaq5hznC%3Di0R5Wojt5%2Byhyw%40mail.gmail.com

Regards,
Bharath Rupireddy.