RE: BUG #17375: RECOVERY TARGET TIME RESTORE IS FAILING TO START SERVER

Hi Tom/David,

I understand the situation now.

The problem here for us is as below:
1) We run a Full backup using pg_start_backup()--> backup data directory --> pg_stop_backup() --> backup all archive log files.
Call this as FULL_BACKUP1

2) After full, log only backups are run periodically --> Using pg_switch_wal() and backup only archive log files.
During the log backups, there is a chance that no transaction is run in-between, but we there is no way to know about that at this point and we backup the switched logs.
Example: In below 3 log only backups, say we do not have any transactions run in between.
a. LOG_BACKUP_1
b. LOG_BACKUP_2
c. LOG_BACKUP_3

3) Now in our case if the user wants to restore to the time when LOG_BACKUP_2 is run, we specify the recovery_target_time as time when LOG_BACKUP_2 was run and try to start the server. But it fails because in PG13 we cannot specify time later than the last available transaction and we need to specify the time when the last transaction is available(Is there any way to get this time?).

To handle this, we would need any of these resolution/Workarounds:
1) During restore, is there any way we can get the last available transaction near to the specified retore time?
2) Is there any way we can make sure archive logs are generated only when there is a transaction available?
3) It would be very helpful if there are any settings to override old feature(Stop and bring up the server with latest available logs) over the new one.

Thanks and Regards,
Ganesh Kishan

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: 21 January 2022 20:51
To: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: B Ganesh Kishan <bkishan(at)commvault(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)org; Meera Nair <mnair(at)commvault(dot)com>
Subject: Re: BUG #17375: RECOVERY TARGET TIME RESTORE IS FAILING TO START SERVER

External email. Inspect before opening.

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Fri, Jan 21, 2022 at 4:20 AM B Ganesh Kishan
> <bkishan(at)commvault(dot)com>
> wrote:
>> The problem is that we are providing a time target that Postgres does
>> not know how to reach. This is because there are no transactions in
>> between the backups.

> I don't quite follow the overall situation but given your observation
> and apparent acceptance of the pre-v13 behavior just don't specify a
> restore point and let WAL replay everything.

Yeah. If I'm understanding the situation, when you specify a target time that is later than the last transaction available from WAL, older versions silently assumed that stopping with the last available transaction is OK.
Newer ones complain because it's not clear whether that's OK --- in particular, there's no good way to be sure that no WAL is missing.

On the whole I think that's a good change. I can sympathize with the complaint that it creates additional complexity for restore scripts, but I'm a little dubious that this is something you'd be wanting to script anyway.

regards, tom lane