<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div>Hi,</div><div><br></div><div>I think we cannot connect to pgpool. I will show you the output of my dbCheck script.<br></div><div><br></div><div><ul style="margin-top:0cm" type="disc">
<li class="gmail-MsoListParagraph" style="margin-left:0cm"><b>Pgpool without patch and backend1 as slave</b><b>:</b></li></ul>
<p class="MsoNormal" style="margin-left:35.4pt">[root@pg_client1 services]#
./dbcheck.sh $VIP_PGPOOL<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">psql: ERROR: do command
failed<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">DETAIL: backend error:
"SFATAL"<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">psql: ERROR: unable to read
data from DB node 1<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">DETAIL: socket read failed
with error "Connection reset by peer"</p>
<p class="MsoNormal" style="margin-left:35.4pt">psql: server closed the
connection unexpectedly<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">
This probably means the server terminated abnormally<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">
before or while processing the request.<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">psql: server closed the
connection unexpectedly<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">
This probably means the server terminated abnormally<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">
before or while processing the request.</p>
<p class="MsoNormal" style="margin-left:35.4pt">
This probably means the server terminated abnormally<span></span></p>
<p class="MsoNormal" style="margin-left:35.4pt">
before or while processing the request.</p>
<p class="MsoNormal"><b> </b></p>
<ul style="margin-top:0cm" type="disc">
<li class="gmail-MsoListParagraph" style="margin-left:0cm"><b>Pgpool with patch and backend1 as slave:</b><br></li></ul>
<p class="gmail-MsoListParagraph">psql: ERROR: unable to read message kind<span></span></p>
<p class="gmail-MsoListParagraph">DETAIL: kind does not match between main(52)</p><p class="MsoNormal"><br></p>
<ul style="margin-top:0cm" type="disc">
<li class="gmail-MsoListParagraph" style="margin-left:0cm"><b>Pgpool <b>with </b>patch and backend1 as master</b><b>:</b></li></ul>
<p class="gmail-MsoListParagraph">psql: ERROR: unable to read data from DB node<span></span></p>
<p class="gmail-MsoListParagraph">DETAIL: socket read failed with error
"Connection reset by peer"<span></span></p>
<p class="gmail-MsoListParagraph">server closed the connection unexpectedly<span></span></p>
<p class="gmail-MsoListParagraph"> This
probably means the server terminated abnormally<span></span></p>
<p class="gmail-MsoListParagraph"> before or
while processing the request.<span></span></p>
<p class="gmail-MsoListParagraph">connection to server was lost<span></span></p>
<p class="gmail-MsoListParagraph">server closed the connection unexpectedly<span></span></p>
<p class="gmail-MsoListParagraph"> This
probably means the server terminated abnormally<span></span></p>
<p class="gmail-MsoListParagraph"> before or
while processing the request.<span></span></p>
<p class="gmail-MsoListParagraph">connection to server was lost</p><p class="gmail-MsoListParagraph"><br></p><p class="gmail-MsoListParagraph">Anyway, with a client which uses ODBC, if it tries to access the database during failover (from slave node) the following error is displayed: "Driver Unable to Establish Connection with Data Source".</p></div></div></div></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">El vie, 12 may 2023 a las 9:40, Tatsuo Ishii (<<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>>) escribió:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">What do you mean by "database is not available"?<br>
<br>
1. You can connect to pgpool but pgpool does not reply back.<br>
<br>
2. You can cannect to pgpool but pgpool immediately disconnects.<br>
<br>
> Hi Tatsuo,<br>
> <br>
> I'm working with your patch but I continue facing a problem because the<br>
> database is not available during 1 second aprox (I have a script calling<br>
> select query every 0.1 seconds to check the time is not available the<br>
> database).<br>
> <br>
> I will explain two different cases:<br>
> <br>
> 1. Slave node (backend1 in pgpool.conf) is turn off. With your patch the<br>
> database is always available. Without your patch the database is not<br>
> available during 1 second.<br>
> 2. Master node (backend0) is turn off. Failover is done to promote<br>
> backend1. After that, I turn on again backend0, which is now slave node. If<br>
> I turn off this slave node (backend0), the database is not available during<br>
> 1 second (with or without your patch)<br>
> <br>
> Do you have any idea why is this behaviour?<br>
> <br>
> Thanks in advance.<br>
> <br>
> Best,<br>
> Jesús<br>
> <br>
> El vie, 14 abr 2023 3:41, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> escribió:<br>
> <br>
>> Hi Jesús,<br>
>><br>
>> > Hi Tatsuo,<br>
>> ><br>
>> > At first, thank you so much for your time to investigate this issue.<br>
>><br>
>> No problem.<br>
>><br>
>> > I have compiled pgpool 4.3.2 with your patch and the problem with pgbench<br>
>> > is solved.<br>
>> > I still need to test it in my environment.<br>
>> ><br>
>> > Anyway, I had a look your code and I have seen that the session is closed<br>
>> > only if failover is not completed in 30 seconds.<br>
>> > I have the following doubt related to this change. Is this session<br>
>> > operative during the failover? I mean, if failover spends 20 seconds, is<br>
>> > this session blocked during this time or this session can accept any<br>
>> > transaction?<br>
>><br>
>> It is likely the session is blocked. The reason for "likely" is the<br>
>> function which has the logic inside can be called frequently during<br>
>> session but it is not always. It is possible that a pgpool process<br>
>> already called the function by the time when failover starts, then<br>
>> proceeds and sends a query to backend.<br>
>><br>
>> > Let me another question. Should we add this issue as a bug?<br>
>><br>
>> No you don't need. Developers already recognize this a bug report.<br>
>><br>
>> > Thanks in advance.<br>
>> ><br>
>> > Best,<br>
>> > Jesús<br>
>> ><br>
>> ><br>
>> > El mié, 12 abr 2023 3:33, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> escribió:<br>
>> ><br>
>> >> > However a downside of this is, while failover clients cannot process<br>
>> >> > queries or at least slow down processing. Below is the log from<br>
>> >> > pgbench using "-P 1" option to show progress. As you can see from 170<br>
>> >> > s pgbench starts to slow down and recovers at 194 s. That is, the<br>
>> >> > slowdown continued for 24 seconds.<br>
>> >> ><br>
>> >><br>
>> >> After more research, I suspect the slow down is due to effect of<br>
>> >> checkpointing. If I add "-S" option to change the transaction time, I<br>
>> >> don't see the slow down anymore.<br>
>> >><br>
>> >> Best reagards,<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS LLC<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en/" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
>> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >><br>
>><br>
</blockquote></div>