<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
span.StylE-mailovZprvy17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:301155162;
mso-list-type:hybrid;
mso-list-template-ids:-1529702614 67436561 67436569 67436571 67436559 67436569 67436571 67436559 67436569 67436571;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1
{mso-list-id:725224157;
mso-list-type:hybrid;
mso-list-template-ids:1300891040 67436561 67436569 67436571 67436559 67436569 67436571 67436559 67436569 67436571;}
@list l1:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l1:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l1:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l1:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l1:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l1:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l1:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="CS" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><a name="_Hlk30747702"><span lang="EN-US">Hello,<o:p></o:p></span></a></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I am trying to figure out the way to implement the scripts needed for the online recovery feature, while running pgpool in replication mode (as opposed to master/slave streaming
mode). I have found several examples of those scripts for streaming replication mode (such as this one -
</span></span><a href="https://www.pgpool.net/docs/latest/en/html/example-cluster.html#EXAMPLE-CLUSTER-PGPOOL-CONFIG-ONLINE-RECOVERY"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">https://www.pgpool.net/docs/latest/en/html/example-cluster.html#EXAMPLE-CLUSTER-PGPOOL-CONFIG-ONLINE-RECOVERY</span></span><span style="mso-bookmark:_Hlk30747702"></span></a><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">),
but have yet to come across some example for the replication mode.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Now – from what I gather, replication mode is the only mode, where the 2nd stage of online recovery is used, but from what I saw in the sample script distributed with YUM package,
only thing I am supposed to do here is to flush sequence values to WAL and then switch the WAL file. Based on that, here is what I think each stage should do in replication mode:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<ol style="margin-top:0cm" start="1" type="1">
<li class="MsoListParagraph" style="margin-left:0cm;mso-list:l0 level1 lfo1"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">1st stage – restore the PGDATA data directory (pg_basebackup) and start streaming changes (via a replication slot) from the
source DB to the target DB.<o:p></o:p></span></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l0 level1 lfo1"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">2nd stage – switch WAL file on the source DB, so any pending changes get streamed to the target DB.<o:p></o:p></span></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l0 level1 lfo1"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Remote start – promote the target DB from standby to read/write mode (and drop the replication slot on the source DB).<o:p></o:p></span></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l0 level1 lfo1"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Target DB is attached to pgpool.<o:p></o:p></span></span></li></ol>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I have some question however as to what will happen in between these stages:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<ol style="margin-top:0cm" start="1" type="1">
<li class="MsoListParagraph" style="margin-left:0cm;mso-list:l1 level1 lfo2"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">After the 1st stage, are all clients disconnected automatically or pgpool just waits for them to disconnect on their own
– so in order for the online recovery to proceed, I need to force the disconnection from their side? Documentation seems to imply the second option, but I would rather be sure about that…<o:p></o:p></span></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l1 level1 lfo2"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">From the start of the 2nd stage, does pgpool prevent the clients from connecting until the online recovery finishes?<o:p></o:p></span></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l1 level1 lfo2"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Is there some ideal way to check the status of the replication and not let the online recovery continue, until the target
DB fully caught up to the source DB (probably during the 2nd stage…)?<o:p></o:p></span></span></li><li class="MsoListParagraph" style="margin-left:0cm;mso-list:l1 level1 lfo2"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">After performing the remote start script (which needs to called
</span></span><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US" style="font-size:13.5pt;font-family:"Courier New";color:#440000;background:white">pgpool_remote_start</span></span><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US" style="color:#440000;background:white">
and be placed in the same PGDATA directory as the scripts for other stages) and the automatic node attach, does pgpool immediately allow clients to connect again?</span></span><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p></o:p></span></span></li></ol>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">As an attachment I am sending my test versions of those 3 scripts – I have not used them yet, I only did their steps manually to see if they can recover the database that has fallen
behind. Can you please review them if they perform correct steps in order for any outage to be absolutely minimal? Let me know if you need some additional explanation about the scripts…<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Thank you for any advice.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">With regards,
<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Vladimír Láznička, CCA Group a.s.<o:p></o:p></span></span></p>
</div>
</body>
</html>