Re: Tracking cluster upgrade and configuration history

> On Nov 15, 2020, at 11:23 PM, Ian Lawrence Barwick <barwick@gmail.com> wrote:
>
> 2020年11月16日(月) 15:48 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>:
>>
>> 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.
>
> 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,
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?
>
> 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
aprimary 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
theyget 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