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