Re: Tracking cluster upgrade and configuration history - Mailing list pgsql-hackers

> On Nov 15, 2020, at 10:47 PM, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
>
> On Thu, Nov 12, 2020 at 4:01 AM Mark Dilger
> <mark.dilger@enterprisedb.com> wrote:
>>
>> While supporting customers, it would frequently be useful to have more information about the history of a cluster.
Forexample, 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.

Thanks for the feedback.

>
>>
>> Some of this type of information could strictly be fixed size, such as a fixed set of timestamps for the time at
whicha 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
haveever been run on the cluster. 
>>
>> Logging the information via the usual log mechanism seems insufficient, as log files may get rotated and this
informationlost. 
>>
>
> 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.

That's going in a very different direction from what I had in mind.  I was imagining something like a single binary or
textfile of either fixed size or something very short but not fixed.  The "very short but not fixed" part of that seems
abit too hand-waving on reflection.  Any variable length list, such as the list of postgres versions started on the
cluster,could be made fixed length by only tracking the most recent N of them, perhaps with a flag bit to indicate if
thelist has overflowed. 

Using elog/ereport with a new log level that gets directed into a different log file is an interesting idea, but it is
notclear how to use elog/ereport in a principled way to write files that need never get too large. 

>> 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.

Variable length texts seem completely out of scope for this.  I would expect the data to be a collection of integer
typesand flag bits.  Fixed length text might also be possible, but I don't have any examples in mind of text that we'd
wantto track. 

>> Would it make sense to alternately, or additionally, store some of this information in a flat text file in pg_data,
saya 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?

This depends in part on feedback about which information others on this list would like to see included, but I was
imaginingsomething similar to how pg_control works, or using pg_control itself.  The maximum size for pg_control is 512
bytes,and on my system sizeof(ControlFileData) = 296, which leaves 216 bytes free.  I didn't check how much that might
changeon systems with different alignments.  We could either use some of the ~200 bytes currently available in
pg_control,or use another file, "pg_history" or such, following the design pattern already used for pg_control. 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company