Suggest two small improvements for PITR.

11.01.2024 19:58, Yura Sokolov пишет:

Good day, hackers.

Here I am to suggest two small improvements to Point In Time Recovery.

First is ability to recover recovery-target-time with timestamp stored
in XLOG_RESTORE_POINT. Looks like historically this ability did exist
and were removed unintentionally during refactoring at commit [1]
c945af80 "Refactor checking whether we've reached the recovery target."

Second is extending XLOG_BACKUP_END record with timestamp, therefore
backup will have its own timestamp as well. It is backward compatible
change since there were no record length check before.

Both changes slightly helps in mostly idle systems, when between several
backups may happens no commits at all, so there's no timestamp to
recover to.

Attached sample patches are made in reverse order:
- XLOG_BACKUP_END then XLOG_RESTORE_POINT.
Second patch made by colleague by my idea.
Publishing for both is permitted.

If idea is accepted, patches for tests will be applied as well.

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=c945af80

Good day.

Here're reordered and rebased patches with tests.
Now first patch is for XLOG_RETORE_POINT, and second one adds timestamp
to XLOG_BACKUP_END.

Btw, there's other thread by Simon Riggs with additions to
getRecordTimestamp:

/messages/by-id/CANbhV-F+8=Y=cfurfD2hjoWVUvTk-Ot9BJdw2Myc=st3TsZy9g@mail.gmail.com

I didn't rush to adsorb it, because I'm not recoveryTargetUseOriginTime.
Though reaction on XLOG_END_OF_RECOVERY's timestamp is easily could be
copied from.

I believe, to react on XLOG_CHECKPOINT_ONLINE/XLOG_CHECKPOINT_SHUTDOWN
the CheckPoint.time field should be changed from `pg_time_t` to
`TimestampTz` type, since otherwise it interfere hard with
"inclusive"-ness of recovery_target_time.

-----

regards,
Yura