RE: Enhance traceability of wal_level changes for backup management

From: osumi(dot)takamichi(at)fujitsu(dot)com <osumi(dot)takamichi(at)fujitsu(dot)com>
> I wondered, couldn't backup management tools utilize the information
> in the backup that is needed to be made when wal_level is changed to "none"
> for example ?

IIRC, someone proposed in the original thread that the change count can be recorded in pg_control. The change count is incremented when wal_level is changed from replica or higher to minimal or lower. Maybe you can do it easily in XLogReportParameters().

Then, the backup management tool compares the change counts of pg_control in a backup and that of the current pg_control. If the change count is different, the tool assumes that the backup cannot be used to recover the database up to date.

Ideally, it'd be desirable for PostgreSQL core to have a backup catalog management capability like Oracle RMAN. Then, when the wal_level is changed, Postgres may be able to invalidate all backups in the backup catalog.

> As I said before, existing backup management tools support
> only wal_level=replica or logical at present. And, if they would wish to alter the
> status quo and want to cover the changes over wal_levels, I felt it's natural that
> they support feature like taking a full backup, trigged by the wal_level changes
> (or server stop).

In that regard, a feature like Oracle Server Alert would be useful. When important events occur, the database server records them in the alert queue. Administration tools read from the alert queue and act accordingly. wal_level change can be recorded in the alert queue, and the backup management tool polls the queue and detect the change.

Regards
Takayuki Tsunakawa