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
0001-pause-recovery-if-pitr-target-not-reached.patch
Description: Binary data
