From: Leif Gunnar Erlandsen <leif@lako.no>
To: pgsql-hackers@postgresql.org
Subject: pause recovery if pitr target not reached
Date: Tue, 17 Sep 2019 11:23:45 +0000
Message-ID: <993736dd3f1713ec1f63fc3b653839f5@lako.no> (raw)
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
Attachments:0001-pause-recovery-if-pitr-target-not-reached.patch (application/octet-stream, 1.1K) download
*** src/backend/access/transam/xlog.c_ 2019-09-09 22:24:29.000000000 +0200
--- src/backend/access/transam/xlog.c 2019-09-17 11:54:22.631882409 +0200
***************
*** 7279,7284 ****
--- 7279,7307 ----
case RECOVERY_TARGET_ACTION_PROMOTE:
break;
}
+ } else if (recoveryTarget == RECOVERY_TARGET_TIME)+ {+ /*+ * Stop point not reached and next WAL could not be read+ * Report transaction log time and pause recovery+ */+ switch (recoveryTargetAction)+ {+ case RECOVERY_TARGET_ACTION_PAUSE:+ ereport(LOG,+ (errmsg("Recovery target not reached but next WAL record culd not be read")));+ ereport(LOG,+ (errmsg("redo done at %X/%X",+ (uint32)(ReadRecPtr >> 32), (uint32)ReadRecPtr)));+ xtime = GetLatestXTime();+ if (xtime)+ ereport(LOG,+ (errmsg("last completed transaction was at log time %s",+ timestamptz_to_str(xtime))));+ SetRecoveryPause(true);+ recoveryPausesHere();+ break;+ }
}
/* Allow resource managers to do any required cleanup. */
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: leif@lako.no
Subject: Re: pause recovery if pitr target not reached
In-Reply-To: <993736dd3f1713ec1f63fc3b653839f5@lako.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox