Re: Is there a way (except from server logs) to know the kind of on-going/last checkpoint?

On Wed, Dec 8, 2021 at 3:48 AM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> On 12/7/21 15:36, Bharath Rupireddy wrote:
> > Hi,
> >
> > Currently one can know the kind of on-going/last checkpoint (shutdown,
> > end-of-recovery, immediate, force etc.) only via server logs that to
> > when log_checkpoints GUC is on. At times, the users/service layer
> > components would want to know the kind of checkpoint (along with other
> > checkpoint related info) to take some actions and it will be a bit
> > difficult to search through the server logs. The checkpoint info can
> > be obtained from the control file (either by pg_control_checkpoint()
> > or by pg_controldata tool) whereas checkpoint kind isn't available
> > there.
> >
> > How about we add an extra string field to the control file alongside
> > the other checkpoint info it already has? This way, the existing
> > pg_control_checkpoint() or pg_controldata tool can easily be enhanced
> > to show the checkpoint kind as well. One concern is that we don't want
> > to increase the size of pg_controldata by more than the typical block
> > size (of 8K) to avoid any torn-writes. With this change, we might add
> > at max the strings specified at [1]. Adding it to the control file has
> > an advantage of preserving the last checkpoint kind which might be
> > useful.
> >
> > Thoughts?
> >
>
> I agree it might be useful to provide information about the nature of
> the checkpoint, and perhaps even PID of the backend that triggered it
> (although that may be tricky, if the backend terminates).

Thanks. I agree to have pg_stat_progress_checkpoint and yes PID of the
triggered backend can possibly go there (we can mention in the
documentation that the backend that triggered the checkpoint can get
terminated).

> I'm not sure about adding it to control data, though. That doesn't seem
> like a very good match for something that's mostly for monitoring.

Having it in the control data file (along with the existing checkpoint
information) will be helpful to know what was the last checkpoint
information and we can use the existing pg_control_checkpoint function
or the tool to emit that info. I plan to add an int16 flag as
suggested by Justin in this thread and come up with a patch.

> We already have some checkpoint info in pg_stat_bgwriter, but that's
> just aggregated data, not very convenient for info about the current
> checkpoint. So maybe we should add pg_stat_progress_checkpoint, showing
> various info about the current checkpoint?

+1 to have pg_stat_progress_checkpoint view to know what's going on
with the current checkpoint.

Regards,
Bharath Rupireddy.