> On Nov 15, 2020, at 11:23 PM, Ian Lawrence Barwick <barw...@gmail.com> wrote:
>
> 2020年11月16日(月) 15:48 Bharath Rupireddy
> <bharath.rupireddyforpostg...@gmail.com>:
>>
>> On Thu, Nov 12, 2020 at 4:01 AM Mark Dilger
>> <mark.dil...@enterprisedb.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
