pause recovery if pitr target not reached - Mailing list pgsql-hackers

This patch allows PostgreSQL to pause recovery before PITR target is reached
if recovery_target_time is specified.

Missing WAL's could then be restored from backup and applied on next restart.

Today PostgreSQL opens the database in read/write on a new timeline even when
PITR tareg is not reached.

make check is run with this patch with result "All 192 tests passed."
Source used is from version 12b4.

For both examples below "recovery_target_time = '2019-09-17 09:24:00'"

_________________________
Log from todays behavior:

[20875] LOG:  starting point-in-time recovery to 2019-09-17 09:24:00+02
[20875] LOG:  restored log file "000000010000000000000002" from archive
[20875] LOG:  redo starts at 0/2000028
[20875] LOG:  consistent recovery state reached at 0/2000100
[20870] LOG:  database system is ready to accept read only connections
[20875] LOG:  restored log file "000000010000000000000003" from archive
[20875] LOG:  restored log file "000000010000000000000004" from archive
cp: cannot stat '/var/lib/pgsql/12/archivedwal/000000010000000000000005': No such file or directory
[20875] LOG:  redo done at 0/40080C8
[20875] LOG:  last completed transaction was at log time 2019-09-17 09:13:10.524645+02
[20875] LOG:  restored log file "000000010000000000000004" from archive
cp: cannot stat '/var/lib/pgsql/12/archivedwal/00000002.history': No such file or directory
[20875] LOG:  selected new timeline ID: 2
[20875] LOG:  archive recovery complete
cp: cannot stat '/var/lib/pgsql/12/archivedwal/00000001.history': No such file or directory
[20870] LOG:  database system is ready to accept connections

________________________
And with patched source:

[20899] LOG:  starting point-in-time recovery to 2019-09-17 09:24:00+02
[20899] LOG:  restored log file "000000010000000000000002" from archive
[20899] LOG:  redo starts at 0/2000028
[20899] LOG:  consistent recovery state reached at 0/20002B0
[20895] LOG:  database system is ready to accept read only connections
[20899] LOG:  restored log file "000000010000000000000003" from archive
[20899] LOG:  restored log file "000000010000000000000004" from archive
cp: cannot stat '/var/lib/pgsql/12m/archivedwal/000000010000000000000005': No such file or directory
[20899] LOG:  Recovery target not reached but next WAL record culd not be read
[20899] LOG:  redo done at 0/4007D40
[20899] LOG:  last completed transaction was at log time 2019-09-17 09:13:10.539546+02
[20899] LOG:  recovery has paused
[20899] HINT:  Execute pg_wal_replay_resume() to continue.


You could restore WAL in several steps and when target is reached you get this log

[21943] LOG:  starting point-in-time recovery to 2019-09-17 09:24:00+02
[21943] LOG:  restored log file "000000010000000000000005" from archive
[21943] LOG:  redo starts at 0/5003C38
[21943] LOG:  consistent recovery state reached at 0/6000000
[21941] LOG:  database system is ready to accept read only connections
[21943] LOG:  restored log file "000000010000000000000006" from archive
[21943] LOG:  recovery stopping before commit of transaction 859, time 2019-09-17 09:24:02.58576+02
[21943] LOG:  recovery has paused
[21943] HINT:  Execute pg_wal_replay_resume() to continue.

Execute pg_wal_replay_resume() as hinted.

[21943] LOG:  redo done at 0/6001830
[21943] LOG:  last completed transaction was at log time 2019-09-17 09:23:57.496945+02
cp: cannot stat '/var/lib/pgsql/12m/archivedwal/00000002.history': No such file or directory
[21943] LOG:  selected new timeline ID: 2
[21943] LOG:  archive recovery complete
cp: cannot stat '/var/lib/pgsql/12m/archivedwal/00000001.history': No such file or directory
[21941] LOG:  database system is ready to accept connections



----------------

Leif Gunnar Erlandsen