Re: Tracking cluster upgrade and configuration history

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.

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

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.

[1] https://github.com/ibarwick/config_log


Regards

Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com