Re: Make unlogged table resets detectable

On Mon, Jun 07, 2021 at 02:56:57PM -0400, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Fri, Jun 4, 2021 at 8:41 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> >> Stepping back, maybe unlogged tables are the wrong level to solve this
> >> problem. We could just have a "crash counter" in pg_control that would
> >> be incremented every time a crash happened (and all unlogged tables are
> >> reset). It might be a number or maybe the LSN of the startup checkpoint
> >> after the most recent crash.
>
> > I think this would be useful for a variety of purposes. Both being
> > able to know the last time that it happened and being able to know the
> > number of times that it happened could be useful, depending on the
> > scenario.
>
> +1. I'd support recording the time of the last crash recovery, as
> well as having a counter. I think an LSN would not be as useful
> as a timestamp.

+1

It's been suggested before ;)
https://www.postgresql.org/message-id/20180228221653.GB32095%40telsasoft.com

PS. I currently monitor for crashes by checking something hacky like:
| SELECT backend_start - pg_postmaster_start_time() ORDER BY 1