Re: Tracking cluster upgrade and configuration history

> On Nov 15, 2020, at 11:23 PM, Ian Lawrence Barwick <barwick(at)gmail(dot)com> wrote:
>
> 2020年11月16日(月) 15:48 Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>:
>>
>> 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.
>
> It's certainly something which would be nice to have.

Thanks for the feedback.

>>> 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?
>
> pg_stat_statements might be worth looking at as one way of handling that kind
> of file.
>
> However the problem with keeping a separate file which is not WAL-logged would
> mean it doesn't get propagated to standbys, and there's also the question
> of how it could be maintained across upgrades via pg_upgrade.

Hmmm. I was not expecting the file to be propagated to standbys. The information could legitimately be different for a primary and a standby. As a very simple example, there may be a flag bit for whether the cluster has operated as a standby. That does raise questions about what sort of information about a primary that a standby should track, in case they get promoted to primary and information about the old primary would be useful for troubleshooting. Ideas welcome....

>
> FWIW I did once create a background worker extension [1] which logs
> configuration changes to a table, though it's not particularly maintained or
> recommended for production use.

I'm happy to change course if the consensus on the list favors using something larger, like log files or logging to a table, but for now I'm still thinking about this in terms of something smaller than that.

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