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

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

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      17375
Logged by:          Ganesh Kishan
Email address:      bkishan@commvault.com
PostgreSQL version: 13.5
Operating system:   RHEL 7.6
Description:

Hi Team,

I am trying to restore a PG13 server to a particular point in time.

These are the recovery settings in postgresql.conf:
restore_command = 'cp /var/lib/pgsql/13/wal/%f %p'
recovery_end_command = 'touch
/var/lib/pgsql/13/data_small/recovery.done.cv'
recovery_target_time = '2022-01-20 07:22:00GMT'
recovery_target_action = promote

I see the server fails to start with below error in :

-bash-4.2$ tail -f postgresql-Thu.log
2022-01-20 19:57:10.937 IST [34742] LOG:  starting PostgreSQL 13.5 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
4.8.5-44), 64-bit
2022-01-20 19:57:10.938 IST [34742] LOG:  listening on IPv4 address
"0.0.0.0", port 5432
2022-01-20 19:57:10.938 IST [34742] LOG:  listening on IPv6 address "::",
port 5432
2022-01-20 19:57:10.938 IST [34742] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2022-01-20 19:57:10.939 IST [34742] LOG:  listening on Unix socket
"/tmp/.s.PGSQL.5432"
2022-01-20 19:57:10.942 IST [34744] LOG:  database system was interrupted;
last known up at 2022-01-20 12:10:21 IST
cp: cannot stat ‘/var/lib/pgsql/13/wal/00000003.history’: No such file or
directory
2022-01-20 19:57:10.967 IST [34744] LOG:  starting point-in-time recovery to
2022-01-20 12:52:00+05:30
2022-01-20 19:57:10.970 IST [34744] LOG:  restored log file
"00000002.history" from archive
2022-01-20 19:57:10.988 IST [34744] LOG:  restored log file
"000000020000000000000011" from archive
2022-01-20 19:57:11.190 IST [34744] LOG:  redo starts at 0/11000060
2022-01-20 19:57:11.190 IST [34744] LOG:  consistent recovery state reached
at 0/11000170
2022-01-20 19:57:11.191 IST [34742] LOG:  database system is ready to accept
read only connections
2022-01-20 19:57:11.208 IST [34744] LOG:  restored log file
"000000020000000000000012" from archive
2022-01-20 19:57:11.350 IST [34744] LOG:  restored log file
"000000020000000000000013" from archive
2022-01-20 19:57:11.500 IST [34744] LOG:  restored log file
"000000020000000000000014" from archive
2022-01-20 19:57:11.626 IST [34744] LOG:  restored log file
"000000020000000000000015" from archive
2022-01-20 19:57:11.795 IST [34744] LOG:  restored log file
"000000020000000000000016" from archive
2022-01-20 19:57:11.890 IST [34744] LOG:  restored log file
"000000020000000000000017" from archive
2022-01-20 19:57:12.041 IST [34744] LOG:  restored log file
"000000020000000000000018" from archive
2022-01-20 19:57:12.089 IST [34744] LOG:  restored log file
"000000020000000000000019" from archive
2022-01-20 19:57:12.126 IST [34744] LOG:  restored log file
"00000002000000000000001A" from archive
2022-01-20 19:57:12.165 IST [34744] LOG:  restored log file
"00000002000000000000001B" from archive
2022-01-20 19:57:12.196 IST [34744] LOG:  restored log file
"00000002000000000000001C" from archive
2022-01-20 19:57:12.244 IST [34744] LOG:  restored log file
"00000002000000000000001D" from archive
cp: cannot stat ‘/var/lib/pgsql/13/wal/00000002000000000000001E’: No such
file or directory
2022-01-20 19:57:12.320 IST [34744] LOG:  redo done at 0/1D000148
2022-01-20 19:57:12.320 IST [34744] FATAL:  recovery ended before configured
recovery target was reached
2022-01-20 19:57:12.322 IST [34742] LOG:  startup process (PID 34744) exited
with exit code 1
2022-01-20 19:57:12.322 IST [34742] LOG:  terminating any other active
server processes
2022-01-20 19:57:12.325 IST [34742] LOG:  database system is shut down


For restoring till the specified time we need 000000020000000000000016 log
which is already present.
Still its failing with: FATAL:  recovery ended before configured recovery
target was reached.
Same steps are working with PostgreSQL 12 but failing for PostgreSQL13. Did
anything change in this part?


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

From
B Ganesh Kishan
Date:
Hello,

The problem is that we are providing a time target that Postgres does not know how to reach. This is because there are
notransactions in between the backups.
 
For example, if I take two backups but don't have any transactions in between them the restore will fail to any point
intime between them.
 
However, if we add a transaction between the backups and restore to a time before that transaction then the restore is
successful.

Prior to 13, a missed target would be ignored, and recovery would play to the end of the WAL. So, this issue was not
seenwith earlier versions.
 
Please suggest how can we achieve a successful restore in this case.

Thanks and Regards,
Ganesh Kishan

-----Original Message-----
From: PG Bug reporting form <noreply@postgresql.org> 
Sent: 20 January 2022 20:06
To: pgsql-bugs@lists.postgresql.org
Cc: B Ganesh Kishan <bkishan@commvault.com>
Subject: BUG #17375: RECOVERY TARGET TIME RESTORE IS FAILING TO START SERVER

External email. Inspect before opening.



The following bug has been logged on the website:

Bug reference:      17375
Logged by:          Ganesh Kishan
Email address:      bkishan@commvault.com
PostgreSQL version: 13.5
Operating system:   RHEL 7.6
Description:

Hi Team,

I am trying to restore a PG13 server to a particular point in time.

These are the recovery settings in postgresql.conf:
restore_command = 'cp /var/lib/pgsql/13/wal/%f %p'
recovery_end_command = 'touch
/var/lib/pgsql/13/data_small/recovery.done.cv'
recovery_target_time = '2022-01-20 07:22:00GMT'
recovery_target_action = promote

I see the server fails to start with below error in :

-bash-4.2$ tail -f postgresql-Thu.log
2022-01-20 19:57:10.937 IST [34742] LOG:  starting PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623(Red Hat 4.8.5-44), 64-bit
 
2022-01-20 19:57:10.938 IST [34742] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-01-20 19:57:10.938 IST [34742] LOG:  listening on IPv6 address "::", port 5432
2022-01-20 19:57:10.938 IST [34742] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-01-20 19:57:10.939 IST [34742] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-01-20 19:57:10.942 IST [34744] LOG:  database system was interrupted; last known up at 2022-01-20 12:10:21 IST
cp: cannot stat ‘/var/lib/pgsql/13/wal/00000003.history’: No such file or directory
2022-01-20 19:57:10.967 IST [34744] LOG:  starting point-in-time recovery to
2022-01-20 12:52:00+05:30
2022-01-20 19:57:10.970 IST [34744] LOG:  restored log file "00000002.history" from archive
2022-01-20 19:57:10.988 IST [34744] LOG:  restored log file "000000020000000000000011" from archive
2022-01-20 19:57:11.190 IST [34744] LOG:  redo starts at 0/11000060
2022-01-20 19:57:11.190 IST [34744] LOG:  consistent recovery state reached at 0/11000170
2022-01-20 19:57:11.191 IST [34742] LOG:  database system is ready to accept read only connections
2022-01-20 19:57:11.208 IST [34744] LOG:  restored log file "000000020000000000000012" from archive
2022-01-20 19:57:11.350 IST [34744] LOG:  restored log file "000000020000000000000013" from archive
2022-01-20 19:57:11.500 IST [34744] LOG:  restored log file "000000020000000000000014" from archive
2022-01-20 19:57:11.626 IST [34744] LOG:  restored log file "000000020000000000000015" from archive
2022-01-20 19:57:11.795 IST [34744] LOG:  restored log file "000000020000000000000016" from archive
2022-01-20 19:57:11.890 IST [34744] LOG:  restored log file "000000020000000000000017" from archive
2022-01-20 19:57:12.041 IST [34744] LOG:  restored log file "000000020000000000000018" from archive
2022-01-20 19:57:12.089 IST [34744] LOG:  restored log file "000000020000000000000019" from archive
2022-01-20 19:57:12.126 IST [34744] LOG:  restored log file "00000002000000000000001A" from archive
2022-01-20 19:57:12.165 IST [34744] LOG:  restored log file "00000002000000000000001B" from archive
2022-01-20 19:57:12.196 IST [34744] LOG:  restored log file "00000002000000000000001C" from archive
2022-01-20 19:57:12.244 IST [34744] LOG:  restored log file "00000002000000000000001D" from archive
cp: cannot stat ‘/var/lib/pgsql/13/wal/00000002000000000000001E’: No such file or directory
2022-01-20 19:57:12.320 IST [34744] LOG:  redo done at 0/1D000148
2022-01-20 19:57:12.320 IST [34744] FATAL:  recovery ended before configured recovery target was reached
2022-01-20 19:57:12.322 IST [34742] LOG:  startup process (PID 34744) exited with exit code 1
2022-01-20 19:57:12.322 IST [34742] LOG:  terminating any other active server processes
2022-01-20 19:57:12.325 IST [34742] LOG:  database system is shut down


For restoring till the specified time we need 000000020000000000000016 log which is already present.
Still its failing with: FATAL:  recovery ended before configured recovery target was reached.
Same steps are working with PostgreSQL 12 but failing for PostgreSQL13. Did anything change in this part?


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

From
"David G. Johnston"
Date:
On Fri, Jan 21, 2022 at 4:20 AM B Ganesh Kishan <bkishan@commvault.com> wrote:
Hello,

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.
For example, if I take two backups but don't have any transactions in between them the restore will fail to any point in time between them.
However, if we add a transaction between the backups and restore to a time before that transaction then the restore is successful.

Prior to 13, a missed target would be ignored, and recovery would play to the end of the WAL. So, this issue was not seen with earlier versions.
Please suggest how can we achieve a successful restore in this case.


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.  Doing this for the earlier backup will get you the desired state, since as you said nothing changed since the end of that backup and the time you are presently specifying.

David J.

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

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Jan 21, 2022 at 4:20 AM B Ganesh Kishan <bkishan@commvault.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



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

From
B Ganesh Kishan
Date:
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
logfiles. 
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
thatat 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_timeas time when LOG_BACKUP_2 was run and try to start the server. But it fails because in PG13 we
cannotspecify time later than the last available transaction and we need to specify the time when the last transaction
isavailable(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
availablelogs) over the new one.  

Thanks and Regards,
Ganesh Kishan

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

External email. Inspect before opening.



"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Jan 21, 2022 at 4:20 AM B Ganesh Kishan
> <bkishan@commvault.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
availablefrom 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
WALis missing. 

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

                        regards, tom lane



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

From
B Ganesh Kishan
Date:
Adding Peter/Leif , who worked on this change.

Hi Peter/Lief,

Could you please help here?

Server start ended with FATAL error and specified the last completed transaction time.

2022-01-27 15:35:33.962 IST [29209] LOG:  starting point-in-time recovery to 2022-01-27 12:56:34+05:30
2022-01-27 15:35:33.966 IST [29209] LOG:  restored log file "00000009.history" from archive
2022-01-27 15:35:33.981 IST [29209] LOG:  restored log file "000000090000000000000061" from archive
2022-01-27 15:35:42.121 IST [29209] LOG:  redo starts at 0/61000060
2022-01-27 15:35:42.122 IST [29209] LOG:  consistent recovery state reached at 0/61000170
2022-01-27 15:35:42.123 IST [29207] LOG:  database system is ready to accept read only connections
2022-01-27 15:35:42.139 IST [29209] LOG:  restored log file "000000090000000000000062" from archive
2022-01-27 15:35:42.456 IST [29209] LOG:  restored log file "000000090000000000000063" from archive
2022-01-27 15:35:44.923 IST [29209] LOG:  restored log file "000000090000000000000064" from archive
2022-01-27 15:35:45.897 IST [29209] LOG:  restored log file "000000090000000000000065" from archive
cp: cannot stat '/var/lib/pgsql/13/wal/000000090000000000000066': No such file or directory
2022-01-27 15:35:46.280 IST [29209] LOG:  redo done at 0/65000148
2022-01-27 15:35:46.280 IST [29209] LOG:  last completed transaction was at log time 2022-01-25 15:05:34.132546+05:30
2022-01-27 15:35:46.280 IST [29209] FATAL:  recovery ended before configured recovery target was reached
2022-01-27 15:35:46.281 IST [29207] LOG:  startup process (PID 29209) exited with exit code 1
2022-01-27 15:35:46.281 IST [29207] LOG:  terminating any other active server processes
2022-01-27 15:35:46.326 IST [29207] LOG:  database system is shut down

Provided the recovery target time as the last completed transaction time specified in the above log. But the server
startis still failing with same error. 

2022-01-27 19:23:30.148 IST [7598] LOG:  starting PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623(Red Hat 4.8.5-44), 64-bit 
2022-01-27 19:23:30.148 IST [7598] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-01-27 19:23:30.148 IST [7598] LOG:  listening on IPv6 address "::", port 5432
2022-01-27 19:23:30.149 IST [7598] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-01-27 19:23:30.150 IST [7598] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-01-27 19:23:30.153 IST [7600] LOG:  database system was interrupted while in recovery at log time 2022-01-25
14:59:53IST 
2022-01-27 19:23:30.153 IST [7600] HINT:  If this has occurred more than once some data might be corrupted and you
mightneed to choose an earlier recovery target. 
cp: cannot stat '/var/lib/pgsql/13/wal/0000000A.history': No such file or directory
2022-01-27 19:23:30.206 IST [7600] LOG:  starting point-in-time recovery to 2022-01-25 15:05:34.132546+05:30
2022-01-27 19:23:30.209 IST [7600] LOG:  restored log file "00000009.history" from archive
2022-01-27 19:23:30.247 IST [7600] LOG:  restored log file "000000090000000000000061" from archive
2022-01-27 19:23:30.502 IST [7600] LOG:  redo starts at 0/61000060
2022-01-27 19:23:30.528 IST [7600] LOG:  restored log file "000000090000000000000062" from archive
2022-01-27 19:23:31.222 IST [7600] LOG:  restored log file "000000090000000000000063" from archive
2022-01-27 19:23:31.942 IST [7600] LOG:  restored log file "000000090000000000000064" from archive
2022-01-27 19:23:33.007 IST [7600] LOG:  consistent recovery state reached at 0/640016A0
2022-01-27 19:23:33.008 IST [7598] LOG:  database system is ready to accept read only connections
2022-01-27 19:23:33.025 IST [7600] LOG:  restored log file "000000090000000000000065" from archive
cp: cannot stat '/var/lib/pgsql/13/wal/000000090000000000000066': No such file or directory
2022-01-27 19:23:33.409 IST [7600] LOG:  redo done at 0/65000148
2022-01-27 19:23:33.409 IST [7600] LOG:  last completed transaction was at log time 2022-01-25 15:05:34.132546+05:30
2022-01-27 19:23:33.409 IST [7600] FATAL:  recovery ended before configured recovery target was reached
2022-01-27 19:23:33.411 IST [7598] LOG:  startup process (PID 7600) exited with exit code 1
2022-01-27 19:23:33.411 IST [7598] LOG:  terminating any other active server processes
2022-01-27 19:23:33.415 IST [7598] LOG:  database system is shut down

Thanks and Regards,
Ganesh Kishan

-----Original Message-----
From: B Ganesh Kishan
Sent: 24 January 2022 11:25
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org; Meera Nair <mnair@commvault.com>
Subject: 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
logfiles. 
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
thatat 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_timeas time when LOG_BACKUP_2 was run and try to start the server. But it fails because in PG13 we
cannotspecify time later than the last available transaction and we need to specify the time when the last transaction
isavailable(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
availablelogs) over the new one.  

Thanks and Regards,
Ganesh Kishan

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

External email. Inspect before opening.



"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Jan 21, 2022 at 4:20 AM B Ganesh Kishan
> <bkishan@commvault.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
availablefrom 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
WALis missing. 

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

                        regards, tom lane



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

From
Bharath Rupireddy
Date:
On Fri, Jan 21, 2022 at 8:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Fri, Jan 21, 2022 at 4:20 AM B Ganesh Kishan <bkishan@commvault.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.

This reminds me of the thread [1], where the proposal was to allow
users to choose what should happen in this situation. Basically, a GUC
that takes us to the old behavior i.e. not fail FATALly but emit a
warning and continue.

[1]
https://www.postgresql.org/message-id/flat/CALj2ACWR4iaph7AWCr5-V9dXqpf2p5B%3D3fTyvLfL8VD_E%2Bx0tA%40mail.gmail.com

Regards,
Bharath Rupireddy.



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

From
B Ganesh Kishan
Date:
Hello,

Can someone please help here?

Thanks and Regards,
Ganesh Kishan

-----Original Message-----
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Sent: 27 January 2022 19:47
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>; B Ganesh Kishan <bkishan@commvault.com>;
pgsql-bugs@lists.postgresql.org;Meera Nair <mnair@commvault.com> 
Subject: Re: BUG #17375: RECOVERY TARGET TIME RESTORE IS FAILING TO START SERVER

External email. Inspect before opening.



On Fri, Jan 21, 2022 at 8:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Fri, Jan 21, 2022 at 4:20 AM B Ganesh Kishan
> > <bkishan@commvault.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.

This reminds me of the thread [1], where the proposal was to allow users to choose what should happen in this
situation.Basically, a GUC that takes us to the old behavior i.e. not fail FATALly but emit a warning and continue. 

[1]
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2FCALj2ACWR4iaph7AWCr5-V9dXqpf2p5B%253D3fTyvLfL8VD_E%252Bx0tA%2540mail.gmail.com&data=04%7C01%7Cbkishan%40commvault.com%7C9a2731e7ee4a4ddfa02808d9e19fb56a%7C40ed1e38a16e46229d7c45161b6969d5%7C0%7C0%7C637788898326072542%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=DuJTWKxhxWJkg%2B%2FMNRbK%2B1s9TXTojRPei1cNSDNF5oY%3D&reserved=0

Regards,
Bharath Rupireddy.