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