MIME-Version: 1.0
Date: Tue, 17 Sep 2019 11:23:45 +0000
Content-Type: multipart/mixed;
 boundary="--=_RainLoop_216_732014431.1568719425"
From: "Leif Gunnar Erlandsen" <leif@lako.no>
Message-ID: <993736dd3f1713ec1f63fc3b653839f5@lako.no>
Subject: pause recovery if pitr target not reached
To: pgsql-hackers@postgresql.org
Precedence: bulk


----=_RainLoop_216_732014431.1568719425
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

This patch allows PostgreSQL to pause recovery before PITR target is reac=
hed =0Aif recovery_target_time is specified.=0A=0AMissing WAL's could the=
n be restored from backup and applied on next restart.=0A=0AToday Postgre=
SQL opens the database in read/write on a new timeline even when =0APITR =
tareg is not reached.=0A=0Amake check is run with this patch with result =
"All 192 tests passed."=0ASource used is from version 12b4.=0A=0AFor both=
 examples below "recovery_target_time =3D '2019-09-17 09:24:00'"=0A=0A___=
______________________=0ALog from todays behavior:=0A=0A[20875] LOG:  sta=
rting point-in-time recovery to 2019-09-17 09:24:00+02=0A[20875] LOG:  re=
stored log file "000000010000000000000002" from archive=0A[20875] LOG:  r=
edo starts at 0/2000028=0A[20875] LOG:  consistent recovery state reached=
 at 0/2000100=0A[20870] LOG:  database system is ready to accept read onl=
y connections=0A[20875] LOG:  restored log file "000000010000000000000003=
" from archive=0A[20875] LOG:  restored log file "00000001000000000000000=
4" from archive=0Acp: cannot stat '/var/lib/pgsql/12/archivedwal/00000001=
0000000000000005': No such file or directory=0A[20875] LOG:  redo done at=
 0/40080C8=0A[20875] LOG:  last completed transaction was at log time 201=
9-09-17 09:13:10.524645+02=0A[20875] LOG:  restored log file "00000001000=
0000000000004" from archive=0Acp: cannot stat '/var/lib/pgsql/12/archived=
wal/00000002.history': No such file or directory=0A[20875] LOG:  selected=
 new timeline ID: 2=0A[20875] LOG:  archive recovery complete=0Acp: canno=
t stat '/var/lib/pgsql/12/archivedwal/00000001.history': No such file or =
directory=0A[20870] LOG:  database system is ready to accept connections=
=0A=0A________________________=0AAnd with patched source:=0A=0A[20899] LO=
G:  starting point-in-time recovery to 2019-09-17 09:24:00+02=0A[20899] L=
OG:  restored log file "000000010000000000000002" from archive=0A[20899] =
LOG:  redo starts at 0/2000028=0A[20899] LOG:  consistent recovery state =
reached at 0/20002B0=0A[20895] LOG:  database system is ready to accept r=
ead only connections=0A[20899] LOG:  restored log file "00000001000000000=
0000003" from archive=0A[20899] LOG:  restored log file "0000000100000000=
00000004" from archive=0Acp: cannot stat '/var/lib/pgsql/12m/archivedwal/=
000000010000000000000005': No such file or directory=0A[20899] LOG:  Reco=
very target not reached but next WAL record culd not be read=0A[20899] LO=
G:  redo done at 0/4007D40=0A[20899] LOG:  last completed transaction was=
 at log time 2019-09-17 09:13:10.539546+02=0A[20899] LOG:  recovery has p=
aused=0A[20899] HINT:  Execute pg_wal_replay_resume() to continue.=0A=0A=
=0AYou could restore WAL in several steps and when target is reached you =
get this log=0A=0A[21943] LOG:  starting point-in-time recovery to 2019-0=
9-17 09:24:00+02=0A[21943] LOG:  restored log file "000000010000000000000=
005" from archive=0A[21943] LOG:  redo starts at 0/5003C38=0A[21943] LOG:=
  consistent recovery state reached at 0/6000000=0A[21941] LOG:  database=
 system is ready to accept read only connections=0A[21943] LOG:  restored=
 log file "000000010000000000000006" from archive=0A[21943] LOG:  recover=
y stopping before commit of transaction 859, time 2019-09-17 09:24:02.585=
76+02=0A[21943] LOG:  recovery has paused=0A[21943] HINT:  Execute pg_wal=
_replay_resume() to continue.=0A=0AExecute pg_wal_replay_resume() as hint=
ed.=0A=0A[21943] LOG:  redo done at 0/6001830=0A[21943] LOG:  last comple=
ted transaction was at log time 2019-09-17 09:23:57.496945+02=0Acp: canno=
t stat '/var/lib/pgsql/12m/archivedwal/00000002.history': No such file or=
 directory=0A[21943] LOG:  selected new timeline ID: 2=0A[21943] LOG:  ar=
chive recovery complete=0Acp: cannot stat '/var/lib/pgsql/12m/archivedwal=
/00000001.history': No such file or directory=0A[21941] LOG:  database sy=
stem is ready to accept connections=0A=0A=0A=0A----------------=0A=0ALeif=
 Gunnar Erlandsen

----=_RainLoop_216_732014431.1568719425
Content-Type: application/octet-stream;
 name="0001-pause-recovery-if-pitr-target-not-reached.patch"
Content-Disposition: attachment;
 filename="0001-pause-recovery-if-pitr-target-not-reached.patch"
Content-Transfer-Encoding: base64

KioqIHNyYy9iYWNrZW5kL2FjY2Vzcy90cmFuc2FtL3hsb2cuY18JMjAxOS0wOS0wOSAyMjoy
NDoyOS4wMDAwMDAwMDAgKzAyMDAKLS0tIHNyYy9iYWNrZW5kL2FjY2Vzcy90cmFuc2FtL3hs
b2cuYwkyMDE5LTA5LTE3IDExOjU0OjIyLjYzMTg4MjQwOSArMDIwMAoqKioqKioqKioqKioq
KioKKioqIDcyNzksNzI4NCAqKioqCi0tLSA3Mjc5LDczMDcgLS0tLQogIAkJCQkJY2FzZSBS
RUNPVkVSWV9UQVJHRVRfQUNUSU9OX1BST01PVEU6CiAgCQkJCQkJYnJlYWs7CiAgCQkJCX0K
KyAJCQl9CWVsc2UgaWYgKHJlY292ZXJ5VGFyZ2V0ID09IFJFQ09WRVJZX1RBUkdFVF9USU1F
KQorIAkJCXsKKyAJCQkJLyoKKyAJCQkJICogU3RvcCBwb2ludCBub3QgcmVhY2hlZCBhbmQg
bmV4dCBXQUwgY291bGQgbm90IGJlIHJlYWQKKyAJCQkJICogUmVwb3J0IHRyYW5zYWN0aW9u
IGxvZyB0aW1lIGFuZCBwYXVzZSByZWNvdmVyeQorIAkJCQkgKi8KKyAJCQkJc3dpdGNoIChy
ZWNvdmVyeVRhcmdldEFjdGlvbikKKyAJCQkJeworIAkJCQkJY2FzZSBSRUNPVkVSWV9UQVJH
RVRfQUNUSU9OX1BBVVNFOgorIAkJCQkJCWVyZXBvcnQoTE9HLAorIAkJCQkJCQkJKGVycm1z
ZygiUmVjb3ZlcnkgdGFyZ2V0IG5vdCByZWFjaGVkIGJ1dCBuZXh0IFdBTCByZWNvcmQgY3Vs
ZCBub3QgYmUgcmVhZCIpKSk7CisgCQkJCQkJZXJlcG9ydChMT0csCisgCQkJCQkJCQkoZXJy
bXNnKCJyZWRvIGRvbmUgYXQgJVgvJVgiLAorIAkJCQkJCQkJCQkodWludDMyKShSZWFkUmVj
UHRyID4+IDMyKSwgKHVpbnQzMilSZWFkUmVjUHRyKSkpOworIAkJCQkJCXh0aW1lID0gR2V0
TGF0ZXN0WFRpbWUoKTsKKyAJCQkJCQlpZiAoeHRpbWUpCisgCQkJCQkJCWVyZXBvcnQoTE9H
LAorIAkJCQkJCQkJCShlcnJtc2coImxhc3QgY29tcGxldGVkIHRyYW5zYWN0aW9uIHdhcyBh
dCBsb2cgdGltZSAlcyIsCisgCQkJCQkJCQkJCQl0aW1lc3RhbXB0el90b19zdHIoeHRpbWUp
KSkpOworIAkJCQkJCVNldFJlY292ZXJ5UGF1c2UodHJ1ZSk7CisgCQkJCQkJcmVjb3ZlcnlQ
YXVzZXNIZXJlKCk7CisgCQkJCQkJYnJlYWs7CisgCQkJCX0KICAJCQl9CiAgCiAgCQkJLyog
QWxsb3cgcmVzb3VyY2UgbWFuYWdlcnMgdG8gZG8gYW55IHJlcXVpcmVkIGNsZWFudXAuICov
Cg==

----=_RainLoop_216_732014431.1568719425--