On Thu, Jul 28, 2022 at 11:50 AM Harinath Kanchu wrote:
>
> Greetings,
>
>
> When we take backups from a synchronous standby replica, how can we get the
> accurate timestamp of the backup end time ? (As backup history files are not
> generated on standbys)For example:
> this is a part of control file after a backup (created using wal-g by calling
> pg_startbackup and pg_stopbackup),
>
> Fake LSN counter for unlogged rels: 0/3E8
> Minimum recovery ending location: 28/28000B68
> Min recovery ending loc's timeline: 2
> Backup start location:0/0
> Backup end location: 0/0
> End-of-backup record required:no
>
> here I can see that minimum recovery ending location as LSN value, how can we
> get the timestamp of it ?The backup label file looks like this.
>
> INFO: 2022/07/26 23:25:23.850621 LABLE FILE START --
> INFO: 2022/07/26 23:25:23.850628 START WAL LOCATION: 1D/F94C7320 (file
> 0002001D00F9)
> INFO: 2022/07/26 23:25:23.850633 CHECKPOINT LOCATION: 1E/EDA8700
> INFO: 2022/07/26 23:25:23.850639 BACKUP METHOD: streamed
> INFO: 2022/07/26 23:25:23.850645 BACKUP FROM: standby
> INFO: 2022/07/26 23:25:23.850653 START TIME: 2022-07-26 23:10:27 GMT
> INFO: 2022/07/26 23:25:23.850659 LABEL: 2022-07-26 23:10:27.545378 + UTC
> m=+0.167723956
> INFO: 2022/07/26 23:25:23.850665 START TIMELINE: 2
> INFO: 2022/07/26 23:25:23.850669
> INFO: 2022/07/26 23:25:23.850676 LABLE FILE END --
>
>
> How can we do PITR using timestamp if we don’t know the accurate timestamp of
> minimum recovery point ?
You can use any of the methods specified in the other thread [1].
Otherwise, you can as well use recovery_target_lsn =
min_recovery_point for PITR target instead of relying on timestamps.
I believe the other thread [1] can be merged into this thread for a
focussed, use-case based and meaningful discussion.
[1]
https://www.postgresql.org/message-id/CALj2ACVgFvOQQEoyuuZeceQrStGsePWvU1noU5aAvJNenv8qTQ%40mail.gma
--
Bharath Rupireddy
RDS Open Source Databases: https://aws.amazon.com/
