RE: Synchronizing slots from primary to standby

On Wednesday, February 7, 2024 9:13 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Tue, Feb 6, 2024 at 8:21 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Tue, Feb 6, 2024 at 3:33 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> > >
> > > On Tue, Feb 6, 2024 at 1:09 PM Masahiko Sawada
> <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > > >
> > > > On Tue, Feb 6, 2024 at 3:19 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> > > > >
> > > > > I think users can refer to LOGs to see if it has changed since
> > > > > the first time it was configured. I tried by existing parameter
> > > > > and see the following in LOG:
> > > > > LOG: received SIGHUP, reloading configuration files
> > > > > 2024-02-06 11:38:59.069 IST [9240] LOG: parameter "autovacuum"
> changed to "on"
> > > > >
> > > > > If the user can't confirm then it is better to follow the steps
> > > > > mentioned in the patch. Do you want something else to be written
> > > > > in docs for this? If so, what?
> > > >
> > > > IIUC even if a wrong slot name is specified to standby_slot_names
> > > > or even standby_slot_names is empty, the standby server might not
> > > > be lagging behind the subscribers depending on the timing. But
> > > > when checking it the next time, the standby server might lag
> > > > behind the subscribers. So what I wanted to know is how the user
> > > > can confirm if a failover-enabled subscription is ensured not to
> > > > go in front of failover-candidate standbys (i.e., standbys using
> > > > the slots listed in standby_slot_names).
> > > >
> > >
> > > But isn't the same explained by two steps ((a) Firstly, on the
> > > subscriber node check the last replayed WAL. (b) Next, on the
> > > standby server check that the last-received WAL location is ahead of
> > > the replayed WAL location on the subscriber identified above.) in
> > > the latest *_0004 patch.
> > >
> >
> > Additionally, I would like to add that the users can use the queries
> > mentioned in the doc after the primary has failed and before promoting
> > the standby. If she wants to do that when both primary and standby are
> > available, the value of 'standby_slot_names' on primary should be
> > referred. Isn't those two sufficient that there won't be false
> > positives?
>
> From a user perspective, I'd like to confirm the following two points :
>
> 1. replication slots used by subscribers are synchronized to the standby.
> 2. it's guaranteed that logical replication doesn't go ahead of physical
> replication to the standby.
>
> These checks are necessary at least when building a replication setup (primary,
> standby, and subscriber). Otherwise, it's too late if we find out that no standby
> is failover-ready when the primary fails and we're about to do a failover.
>
> As for the point 1 above, we can use the step 1 described in the doc.
>
> As for point 2, the step 2 described in the doc could return true even if
> standby_slot_names isn't working. For example, standby_slot_names is empty,
> the user changed the standby_slot_names but forgot to reload the config file,
> and the walsender doesn't reflect the standby_slot_names update yet for some
> reason etc. It's possible that standby's last-received WAL location just happens
> to be ahead of the replayed WAL location on the subscriber. So even if the
> check query returns true once, it could return false when we check it again, if
> standby_slot_names is not working. On the other hand, IIUC if the point 2 is
> ensured, the check query always returns true. I think it would be good if we
> could provide a reliable way to check point 2 ideally via SQL queries (especially
> for tools).

Based on off-list discussions with Sawada-san and Amit, an alternative approach to
improve this would be collecting the names of the standby slots that each
walsender has waited for, which will be visible in the pg_stat_replication
view. By checking this information, users can confirm that the GUC
standby_slot_names is correctly configured and that logical replication is not
lagging behind the standbys that hold these slots.

To achieve this, we can implement the collection of slot information within
each logical walsender with failover slot acquired, when waiting for he standby
to catch up (WalSndWaitForWal). For each valid standby slot that the walsender
has waited for, we will store the slot names in a shared memory area specific
to each walsender. To optimize performance, we can only rebuild the slot names
if the GUC has changed. We can track this by introducing a flag to monitor GUC
modifications.

When a user queries the pg_stat_replication view, we will retrieve the
collected slot names from the shared memory area associated with each
walsender. However, before returning the slot names, we can verify their
validity once again. If any of the collected slots have been dropped or
invalidated during this time, we will exclude them from the result returned to
the user.

Apart from the above design, I feel since user currently have a way to detect this
manually as mentioned in the 0004 patch(we can improve the doc if needed),
the new view info can be a separate improvement for it after pushing the main
patch set.

Best Regards,
Hou zj