<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=us-ascii">
<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;
text-align:right;
direction:rtl;
unicode-bidi:embed;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
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:36.0pt;
margin-bottom:0cm;
margin-left:0cm;
margin-bottom:.0001pt;
text-align:right;
direction:rtl;
unicode-bidi:embed;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1855151429;
mso-list-type:hybrid;
mso-list-template-ids:160068356 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@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;}
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="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed">Hi all,<o:p></o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed"><o:p> </o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed">I wrote a restore script for master / slave streaming replication with pgpool running on both machines in HA mode. The script does the following<o:p></o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed"><o:p> </o:p></p>
<p class="MsoListParagraph" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;text-align:left;text-indent:-18.0pt;mso-list:l0 level1 lfo1;direction:ltr;unicode-bidi:embed">
<![if !supportLists]><span style="mso-list:Ignore">1)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Stop both pgpools (so I will not cause failover when I stop the postgres service)<o:p></o:p></p>
<p class="MsoListParagraph" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;text-align:left;text-indent:-18.0pt;mso-list:l0 level1 lfo1;direction:ltr;unicode-bidi:embed">
<![if !supportLists]><span style="mso-list:Ignore">2)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Stop both postgres services<o:p></o:p></p>
<p class="MsoListParagraph" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;text-align:left;text-indent:-18.0pt;mso-list:l0 level1 lfo1;direction:ltr;unicode-bidi:embed">
<![if !supportLists]><span style="mso-list:Ignore">3)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Delete recovery.conf file<o:p></o:p></p>
<p class="MsoListParagraph" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;text-align:left;text-indent:-18.0pt;mso-list:l0 level1 lfo1;direction:ltr;unicode-bidi:embed">
<![if !supportLists]><span style="mso-list:Ignore">4)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Start postgres on one of the servers (regardless if it was master or slave since now it come up normally since I deleted recovery.conf file)<o:p></o:p></p>
<p class="MsoListParagraph" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;text-align:left;text-indent:-18.0pt;mso-list:l0 level1 lfo1;direction:ltr;unicode-bidi:embed">
<![if !supportLists]><span style="mso-list:Ignore">5)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Start pgpool on both servers<o:p></o:p></p>
<p class="MsoListParagraph" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;text-align:left;text-indent:-18.0pt;mso-list:l0 level1 lfo1;direction:ltr;unicode-bidi:embed">
<![if !supportLists]><span style="mso-list:Ignore">6)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Create a new database (create_db)<o:p></o:p></p>
<p class="MsoListParagraph" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;text-align:left;text-indent:-18.0pt;mso-list:l0 level1 lfo1;direction:ltr;unicode-bidi:embed">
<![if !supportLists]><span style="mso-list:Ignore">7)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Perform pg_restore to the server that I started its postgres service<o:p></o:p></p>
<p class="MsoListParagraph" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;text-align:left;text-indent:-18.0pt;mso-list:l0 level1 lfo1;direction:ltr;unicode-bidi:embed">
<![if !supportLists]><span style="mso-list:Ignore">8)<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Run follow_master so the other machine with the stopped postgres service will become the slave<o:p></o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed"><o:p> </o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed"><o:p> </o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed">Is there a better way of doing it?
<o:p></o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed"><o:p> </o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed">Sometimes I get the following error
<o:p></o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed">psql: ERROR: unable to read message kind<o:p></o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed">DETAIL: kind does not match between master(53) slot[1] (45)<o:p></o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed"><o:p> </o:p></p>
<p class="MsoNormal" style="text-align:left;direction:ltr;unicode-bidi:embed">When I run it again it works. What might have cause this error
<o:p></o:p></p>
</div>
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you
should not copy or use this e-mail for any purpose nor disclose its contents to any person.
</body>
</html>