<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)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@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;}
@font-face
{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Aptos",sans-serif;
mso-ligatures:standardcontextual;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Aptos",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:11.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></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="#467886" vlink="#96607D" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal">So I have failover and failback working properly on a two-node postgres cluster. I can bring down my primary database, pgpool fails over to the secondary, and I can do pcp_recovery_node on the primary and fail back. Fantastic. My users
don’t skip a beat. When I bring down my secondary node though, the failover script drops the replication slot. This means that when I go to run pcp_attach_node, the logs note that the system is not in sync with this:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2025-02-19 16:22:07.231: sr_check_worker pid 1076194: LOG: get_query_result failed: status: -2<o:p></o:p></p>
<p class="MsoNormal">2025-02-19 16:22:07.231: sr_check_worker pid 1076194: CONTEXT: while checking replication time lag<o:p></o:p></p>
<p class="MsoNormal">2025-02-19 16:22:17.262: sr_check_worker pid 1076194: LOG: get_query_result failed: status: -2<o:p></o:p></p>
<p class="MsoNormal">2025-02-19 16:22:17.262: sr_check_worker pid 1076194: CONTEXT: while checking replication time lag<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">If I query pg_replication_slots on the primary database, I get nothing there:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"># select * from pg_replication_slots ;<o:p></o:p></p>
<p class="MsoNormal">slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_<o:p></o:p></p>
<p class="MsoNormal">flush_lsn | wal_status | safe_wal_size | two_phase<o:p></o:p></p>
<p class="MsoNormal">-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+-----------<o:p></o:p></p>
<p class="MsoNormal">----------+------------+---------------+-----------<o:p></o:p></p>
<p class="MsoNormal">(0 rows)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Now of course the obvious fix is to re-add the replication slot but by dropping the slot, that means we’re falling back on the wal_keep_size parameter and hoping that we haven’t lost any wal files. If we have, we’ll need to do a pcp_recovery_node
command. Shouldn’t the script keep the replication slot around if the secondary fails and only drop it if the primary is the thing that went down? It seems like I’m not getting the full capability of replication slots, because the whole point of it is to keep
the WAL files around until the database in recovery confirms it’s got them. <o:p>
</o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks for the help!<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal" style="background:white"><span style="font-family:"Calibri",sans-serif;color:#1F497D"><img width="225" height="50" style="width:2.3437in;height:.5208in" id="Picture_x0020_1" src="cid:image002.png@01DB82E8.20EFEDA0" alt="Volanno"></span><span style="font-family:"Calibri",sans-serif;color:#201F1E;mso-ligatures:none"><o:p></o:p></span></p>
<p class="MsoNormal" style="background:white"><span style="font-family:"Helvetica",sans-serif;color:#1F497D;mso-ligatures:none"> </span><span style="font-family:"Calibri",sans-serif;color:#201F1E;mso-ligatures:none"><o:p></o:p></span></p>
<p class="MsoNormal" style="background:white"><b><span style="font-family:"Helvetica",sans-serif;color:#1F497D;mso-ligatures:none">Adam Blomeke, PSD</span></b><span style="font-family:"Helvetica",sans-serif;color:#1F497D;mso-ligatures:none"> | Developer/Analyst
|</span><span style="font-family:"Calibri",sans-serif;color:#201F1E;mso-ligatures:none"><o:p></o:p></span></p>
<p class="MsoNormal" style="background:white"><span style="font-family:"Helvetica",sans-serif;color:#1F497D;mso-ligatures:none"><a href="mailto:Adam.Blomeke@volanno.com" target="_blank"><span style="color:blue">Adam.Blomeke@volanno.com</span></a> | 202.455.4781
ext. 109</span><span style="font-family:"Calibri",sans-serif;color:#201F1E;mso-ligatures:none"><o:p></o:p></span></p>
<p class="MsoNormal" style="background:white"><span style="font-family:"Helvetica",sans-serif;color:#1F497D;mso-ligatures:none"><a href="http://www.volanno.com/" target="_blank"><span style="color:blue">www.volanno.com</span></a> | </span><span style="font-family:"Helvetica",sans-serif;color:#333333;background:white;mso-ligatures:none">Certified
WOSB, ISO 9001:2015</span><span style="font-family:"Calibri",sans-serif;color:#201F1E;mso-ligatures:none"><o:p></o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>