<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:924344301;
mso-list-type:hybrid;
mso-list-template-ids:-224209390 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;}
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">With the last version of pgPool (4.1.2) we have noticed a strange behavior while using the OmniDB client application. Firstly – we are running pgPool in a native replication mode
in this configuration:<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">PgPool servers (coordinated by the Watchdog process):<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">AISGPGP01 – primary pgpool server with a virtual IP for client connections.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">AISGPGP02 – standby pgpool server.<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">PostgreSQL servers (DML + DDL commands are replicated via pgPool to both, SELECTs are load balanced on one of them):<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">AISGDBD01 – DB node 0.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">AISGDBD02 – DB node 1.<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">Our problem is following – when closing a window with editable SQL code in OmniDB application (effectively closing the session under which the window is connecting to the database),
it causes a failover of the DB node as if the database on one of that node went down. The result is that one DB node is down, while other is still up. If it happens a second time (someone closes the connection from OmniDB), pgPool degenerates the last node
and both databases are effectively down (PostgreSQL processes on AIGSDBD servers run fine) until they are manually brought up by PCP commands. The log from pgPool during the event is following:<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">May 26 12:07:59 AISGPGP01 pgpool[29612]: [2067-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: new connection received<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[29612]: [2067-2] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |DETAIL: connecting host=nb197.cca.cz
port=58065<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[29612]: [2068-1] 2020-05-26 12:07:59 ::: APP - OmniDB :A: DB - aisgdvyv :D: USER - aisg :U: |LOG: found the pg_terminate_backend request for backend
pid:32254 on backend node:0<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[29612]: [2068-2] 2020-05-26 12:07:59 ::: APP - OmniDB :A: DB - aisgdvyv :D: USER - aisg :U: |DETAIL: setting the connection flag<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[29820]: [2340-1] 2020-05-26 12:07:59 ::: APP - OmniDB :A: DB - aisgdvyv :D: USER - aisg :U: |LOG: reading and processing packets<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[29820]: [2340-2] 2020-05-26 12:07:59 ::: APP - OmniDB :A: DB - aisgdvyv :D: USER - aisg :U: |DETAIL: postmaster on DB node 0 was shutdown by administrative
command<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[29820]: [2341-1] 2020-05-26 12:07:59 ::: APP - OmniDB :A: DB - aisgdvyv :D: USER - aisg :U: |LOG: received degenerate backend request for node_id:
0 from pid [29820]<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [131-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: new IPC connection
received<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [132-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: watchdog received the
failover command from local pgpool-II on IPC interface<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [133-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: watchdog is processing
the failover command [DEGENERATE_BACKEND_REQUEST] received from local pgpool-II on IPC interface<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [134-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: we do not need quorum
to hold to proceed with failover<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [134-2] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |DETAIL: proceeding with
the failover<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [134-3] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |HINT: failover_when_quorum_exists
is set to false<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22478]: [2354-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: Pgpool-II parent process
has received failover request<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [135-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: new IPC connection
received<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [136-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: received the failover
indication from Pgpool-II on IPC interface<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [137-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: watchdog is informed
of failover start by the main process<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22478]: [2355-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: starting degeneration.
shutdown host aisgdbd01.cca.cz(5432)<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22478]: [2356-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |WARNING: All the DB nodes
are in down status and skip writing status file.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22478]: [2357-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: failover: no valid
backend node found<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22478]: [2358-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: Restart all children<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22478]: [2359-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: failover: set new
primary node: -1<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [138-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: new IPC connection
received<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [139-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: received the failover
indication from Pgpool-II on IPC interface<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[29662]: [2327-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: worker process received
restart request<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22480]: [140-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: watchdog is informed
of failover end by the main process<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">May 26 12:07:59 AISGPGP01 pgpool[22478]: [2360-1] 2020-05-26 12:07:59 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: failover done. shutdown
host aisgdbd01.cca.cz(5432)<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">The log on the DB node 0 (that is being degenerated by pgPool) during that time is following:<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">2020-05-26 12:07:59.265 CEST ::: APP - OmniDB :A: DB - aisgdvyv :D: USER - aisg :U: HOST - 172.20.15.64 :H: CMD - SELECT :C: |LOG: AUDIT: SESSION,1,1,READ,SELECT,,,select pg_terminate_backend(32254),<not
logged><o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">2020-05-26 12:07:59.265 CEST ::: APP - OmniDB :A: DB - aisgdvyv :D: USER - aisg :U: HOST - 172.20.15.64 :H: CMD - idle :C: |FATAL: terminating connection due to administrator command<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">2020-05-26 12:07:59.355 CEST ::: APP - OmniDB :A: DB - aisgdvyv :D: USER - aisg :U: HOST - 172.20.15.64 :H: CMD - idle in transaction :C: |LOG: unexpected EOF on client connection
with an open transaction<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">We have not noticed this behavior while running previous versions of the pgPool (4.1.1 and older) and even tested the same scenario on an environment with 4.1.1 without the nodes
being degenerated, so it seems to be an issue of the new version, but I obviously cannot confirm that. I have also tried to change
</span></span><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">the <span style="color:black;background:white">
failover_on_backend_error parameter, but it has no effect in this particular situation.<o:p></o:p></span></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">I am sending our pgPool configuration file as an attachment. Please, let me know, if there’s a way to find out, what is actually happening between pgPool and OmniDB and how to prevent
this behavior, or if you need to some additional information to solve this issue. Thank you for your time.<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,</span><o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702">Vladimír Láznička, CCA Group a. s.<o:p></o:p></span></p>
</div>
</body>
</html>