RE: [PoC] pg_upgrade: allow to upgrade publisher node

Dear Julien,

Thank you for giving idea! I have analyzed about it.

> > > If
> > > yes, how does this work if some subscriber node isn't connected when the
> > > publisher node is stopped? I guess you could add a check in pg_upgrade to
> make
> > > sure that all logical slot are indeed caught up and fail if that's not the case
> > > rather than assuming that a clean shutdown implies it. It would be good to
> > > cover that in the TAP test, and also cover some corner cases, like any new
> row
> > > added on the publisher node after the pg_upgrade but before the subscriber is
> > > reconnected is also replicated as expected.
> >
> > Hmm, good point. Current patch could not be handled the case because
> walsenders
> > for the such slots do not exist. I have tested your approach, however, I found that
> > CHECKPOINT_SHUTDOWN record were generated twice when publisher was
> > shutted down and started. It led that the confirmed_lsn of slots always was
> behind
> > from WAL insert location and failed to upgrade every time.
> > Now I do not have good idea to solve it... Do anyone have for this?
>
> I'm wondering if we could just check that each slot's LSN is exactly
> sizeof(CHECKPOINT_SHUTDOWN) ago or something like that? That's hackish,
> but if
> pg_upgrade can run it means it was a clean shutdown so it should be safe to
> assume that what's the last record in the WAL was. For the double
> shutdown checkpoint, I'm not sure that I get the problem. The check should
> only be done at the very beginning of pg_upgrade, so there should have been
> only one shutdown checkpoint done right?

I have analyzed about the point but it seemed to be difficult. This is because
some additional records like followings may be inserted. PSA the script which is
used for testing. Note that "double CHECKPOINT_SHUTDOWN" issue might be wrong,
so I wanted to withdraw it once. Sorry for noise.

* HEAP/HEAP2 records. These records may be inserted by checkpointer.

IIUC, if there are tuples which have not been flushed yet when shutdown is requested,
the checkpointer writes back all of them into heap file. At that time many WAL
records are generated. I think we cannot predict the number of records beforehand.

* INVALIDATION(S) records. These records may be inserted by VACUUM.

There is a possibility that autovacuum runs and generate WAL records. I think we
cannot predict the number of records beforehand because it depends on the number
of objects.

* RUNNING_XACTS record

It might be a timing issue, but I found that sometimes background writer generated
a XLOG_RUNNING record. According to the function BackgroundWriterMain(), it will be
generated when the process spends 15 seconds since last logging and there are
important records. I think it is difficult to predict whether this will be appeared or not.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED