Re: 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:

https://www.postgresql.org/message-id/flat/CANbhV-F%2B8%3DY%3DcfurfD2hjoWVUvTk-Ot9BJdw2Myc%3Dst3TsZy9g%40mail.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