Operation log for major operations

Hi, hackers!

It is important for customer support to know what system operations 
(pg_resetwal, pg_rewind, pg_upgrade, ...) have been executed on the 
database.  A variant of implementation of the log for system operations 
(operation log) is attached to this email.

Introduction.
-------------
Operation log is designed to store information about critical system 
events (like pg_upgrade, pg_resetwal, pg_resetwal, etc.).
This information is not interesting to the ordinary user, but it is very 
important for the vendor's technical support.
An example: client complains about DB damage to technical support 
(damage was caused by pg_resetwal which was "silent" executed by one of 
administrators).

Concepts.
---------
* operation log is placed in the file 'global/pg_control', starting from 
position 4097 (log size is 4kB);
* user can not modify the operation log;  log can be changed  by 
function call only (from postgres or from postgres utilities);
* operation log is a ring buffer (with CRC-32 protection), deleting 
entries from the operation log is possible only when the buffer is 
overflowed;
* SQL-function is used to read data of operation log.

Example of operation log data.
------------------------------

 >select * from pg_operation_log();
    event    |edition|version|   lsn   |         last         |count
------------+-------+-------+---------+----------------------+------
  startup    |vanilla|10.22.0|0/8000028|2022-11-18 23:06:27+03|    1
  pg_upgrade |vanilla|15.0.0 |0/8000028|2022-11-18 23:06:27+03|    1
  startup    |vanilla|15.0.0 |0/80001F8|2022-11-18 23:11:53+03|    3
  pg_resetwal|vanilla|15.0.0 |0/80001F8|2022-11-18 23:09:53+03|    2
(4 rows)

Some details about inserting data to operation log.
---------------------------------------------------
There are two modes of inserting information about events in the 
operation log.

* MERGE mode (events "startup", "pg_resetwal", "pg_rewind").
We searches in ring buffer of operation log an event with the same type 
("startup" for example) with the same version number.
If event was found, we will increment event counter by 1 and update the 
date/time of event ("last" field) with the current value.
If event was not found, we will add this event to the ring buffer (see 
INSERT mode).
* INSERT mode (events "bootstrap", "pg_upgrade", "promoted").
We will add an event to the ring buffer (without searching).


P.S. File 'global/pg_control' was chosen as operation log storage 
because data of this file cannot be removed or modified in a simple way 
and no need to change any extensions and utilities to support this file.

I attached the patch (v1-0001-Operation-log.patch) and extended 
description of operation log (Operation-log.txt).


With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com