Re: Tracking cluster upgrade and configuration history

On Thu, Nov 12, 2020 at 4:01 AM Mark Dilger
<mark(dot)dilger(at)enterprisedb(dot)com> wrote:
>
> While supporting customers, it would frequently be useful to have more information about the history of a cluster. For example, which prior versions were ever installed and running on the cluster? Has the cluster ever been run with fsync=off? When did the server last enter recovery, if ever? Was a backup_label file present at that time?
>

+1 for the idea. The information will be useful at times for debugging purposes.

>
> Some of this type of information could strictly be fixed size, such as a fixed set of timestamps for the time at which a fixed set of things last occurred, or a fixed set of bits indicating whether a fixed set of things ever happened.
>
> Some other types would be variable size, but hopefully short in practice, like a list of all postgres versions that have ever been run on the cluster.
>
> Logging the information via the usual log mechanism seems insufficient, as log files may get rotated and this information lost.
>

True. Just a thought, can we use existing logging mechanism and APIs
to write to a new file that never gets rotated by the syslogger(Of
course, we need to think of the maximum file size that's allowed)? The
idea is like this: we use elog/ereport and so on with a new debug
level, when specified, instead of logging into the standard log files,
we log it to the new file.

>
> Would it be acceptable to store some fixed set of flag bits and timestamps in pg_control? Space there is at a premium.
>

Since we allocate ControlFileData in shared memory and also we may
have some data with timestamps, variable texts and so on, having this
included in pg_control data structure would not seem a good idea to
me.

>
> Would it make sense to alternately, or additionally, store some of this information in a flat text file in pg_data, say a new file named "cluster_history" or such?
>

IMHO, this is also a good idea. We need to think of the APIs to
open/read/write/close that history file? How often and which processes
and what type of data they write? Is it that the postmaster alone will
write into that file? If multiple processes are allowed to write, how
to deal with concurrent writers? Will users have to open manually and
read that file? or Will we have some program similar to
pg_controldata? Will we have some maximum limit to the size of this
file?

>
> I'm happy to put together a more concrete proposal, but solicit your opinions first on the merits of the idea generally, and if you think the idea good, on the specifics you'd like to see included.
>

Welcome to know more about this idea.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com