<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div class="WordSection1">
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
Hi All, </p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
I am investigating a possible bug in pgpool , where it keeps two servers as standby and not
<span class="SpellE">promote</span> the active node </p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
to a primary role.</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
<span style="font-family: Calibri, sans-serif; font-size: 11pt;"> </span><br>
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
Here is an <span class="SpellE">output</span> of show pool nodes describe the issue</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
[root@mgrdb100 ~]# PGPASSWORD=<span class="SpellE">xxxxxxx</span> <span class="SpellE">
psql</span> -U <span class="SpellE">postgres</span> -h 10.65.181.99 -p 9999 -c 'show
<span class="SpellE">pool_nodes</span>'</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
<span class="SpellE">node_id</span> | hostname<span style=""> </span>| port | status |
<span class="SpellE">lb_weight</span> |<span style=""> </span>role<span style="">
</span>| <span class="SpellE">select_cnt</span> | <span class="SpellE">load_balance_node</span> |
<span class="SpellE">replication_delay</span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
0<span style=""> </span>| 1.1.1.100 | 5432 | down<span style=""> </span>| 0.500000<span style="">
</span>| standby | 5392<span style=""> </span>| false<span style="">
</span>| 0</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
1<span style=""> </span>| 1.1.1.101 | 5432 | up<span style=""> </span>| 0.500000<span style="">
</span>| standby | 0<span style=""> </span>| true<span style="">
</span>| 0</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
My scenario happened on <span class="SpellE">PGPool</span> v 3.6.6<span style="">
</span>and I also confirm it happened on the latest version 4.1.2 as well, </p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
The scenario is the following:</p>
<ol start="1" type="a" style="margin-bottom: 0cm;margin-top:0cm">
<li class="MsoListParagraph" style="margin: 0cm 0cm 0.0001pt 36pt; font-size: 11pt; font-family: Calibri, sans-serif;margin-left:0cm; mso-list:l0 level1 lfo1">
Two <span class="SpellE">postgress</span> are up and running node0 primary node 1 standby</li><li class="MsoListParagraph" style="margin: 0cm 0cm 0.0001pt 36pt; font-size: 11pt; font-family: Calibri, sans-serif;margin-left:0cm; mso-list:l0 level1 lfo1">
Two <span class="SpellE">pgpools</span> are up and running <span style=""> </span><span class="SpellE">vip</span> attach properly</li><li class="MsoListParagraph" style="margin: 0cm 0cm 0.0001pt 36pt; font-size: 11pt; font-family: Calibri, sans-serif;margin-left:0cm; mso-list:l0 level1 lfo1">
I execute kill -9 to kill <span class="SpellE">postgress</span> 1.1.1.100 which was the primary (node_id0)</li><li class="MsoListParagraph" style="margin: 0cm 0cm 0.0001pt 36pt; font-size: 11pt; font-family: Calibri, sans-serif;margin-left:0cm; mso-list:l0 level1 lfo1">
I examine <span class="SpellE">pgpool</span> logs and I see FailOver.SH executed successfully
<br>
trigger file is being written on the node1 (the standby),<span style=""> </span>
and on <span class="SpellE">postgres</span> logs <span style=""> </span>of the standby <br>
I found message indicate that trigger file found, <br>
as well i verify postgress move from standby to master,<br>
<span class="SpellE">autovacum</span> start work and in <span class="SpellE">pg_in_recovery</span> the return false</li><li class="MsoListParagraph" style="margin: 0cm 0cm 0.0001pt 36pt; font-size: 11pt; font-family: Calibri, sans-serif;margin-left:0cm; mso-list:l0 level1 lfo1">
So <span class="SpellE">basically</span> almost everything is fine<span style=""> except </span>pgpool which still inform that the role on standby</li></ol>
<div><br>
</div>
<div><br>
</div>
<div>I appreciate getting some steps I should check in order to progress to a solution to the issue</div>
<div>or step to in order to confirm it is a bug, I pulled out from logs the Backend DB status</div>
<div>see log below, I am not clear status 2 meaning my concern right now relate on authentication issue</div>
<div>but still, it is just a guess </div>
<div><br>
</div>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>starting health check</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>doing health check against <span class="SpellE">database:postgres</span> <span class="SpellE">
user:postgres</span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>Backend DB node 0 status is 3</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>Backend DB node 1 status is 2</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>Trying to make persistent DB connection to backend node 1 having status 2</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span><span class="SpellE">pool_flush_it</span>: flush size: 41</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span><span class="SpellE">pool_read</span>: read 13 bytes from backend 1</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>authenticate kind = 5</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span><span class="SpellE">pool_write</span>: to backend: 1 <span class="SpellE">
kind:p</span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span><span class="SpellE">pool_flush_it</span>: flush size: 41</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span><span class="SpellE">pool_flush_it</span>: flush size: 0</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span><span class="SpellE">pool_read</span>: read 321 bytes from backend 1</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>authenticate kind = 0</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>authenticate backend: key data received</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>authenticate backend: transaction state: I</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span>persistent DB connection to backend node 1 having status 2 is successful</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
2020-07-15 17:12:05: <span class="SpellE">pid</span> 25729: DEBUG:<span style="">
</span><span class="SpellE">pool_write</span>: to backend: 1 <span class="SpellE">
kind:X</span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
<span class="SpellE"><br>
</span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
<span class="SpellE"><br>
</span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
<br>
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
</p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">
</p>
</div>
::DISCLAIMER:: E-mail communication is confidential and intended solely for the addressee(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you believe the e-mail message has been sent to you in error, please notify the sender by
replying to the e-mail transmission and delete the message without disclosing it. Thank you.
</body>
</html>