<div dir="ltr"><div>Thank you Pierre:</div><div><br></div><div>My approach to turn Pg Backend B into active mode was wrong, I was restarting it during failover.</div><div><br></div><div>Instead, now I'm using pg_ctl promote. This way I successfully promote PG Backend without losing connectivity.</div><div><br></div><div>Regards.<br></div></div><br><div class="gmail_quote"><div dir="ltr">On Thu, Dec 13, 2018 at 2:25 PM Pierre Timmermans <<a href="mailto:ptim007@yahoo.com">ptim007@yahoo.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div class="gmail-m_-6098952070359640369ydpb4235fbeyahoo-style-wrap" style="font-family:Helvetica Neue,Helvetica,Arial,sans-serif;font-size:16px"><div><div>Everything looks of from a pgpool point of view</div><div><br></div><div>It looks like the postgres failover is also executed, which is good, but in the logs there are some errors and then after there is a second failover operation but this time on the new primary pgnode02. That's clearly not normal. And so both databases are marked down.</div><div><br></div><div><span><div>Dec 13 13:26:56 emkioblph04 pgpool: 2018-12-13 13:26:56: pid 48073: LOG: starting degeneration. shutdown host <a href="http://pgnode02.mydomain.com" target="_blank">pgnode02.mydomain.com</a>(5433)</div><div>Dec 13 13:26:56 emkioblph04 pgpool: 2018-12-13 13:26:56: pid 48073: WARNING: All the DB nodes are in down status and skip writing status file.</div><div>Dec 13 13:26:56 emkioblph04 pgpool: 2018-12-13 13:26:56: pid 48073: LOG: failover: no valid backend node found</div><div>Dec 13 13:26:56 emkioblph04 pgpool: 2018-12-13 13:26:56: pid 48073: LOG: Restart all children</div><div>Dec 13 13:26:56 emkioblph04 pgpool: 2018-12-13 13:26:56: pid 48073: LOG: execute command: /etc/pgpool-II/failover.sh 0 1 "" ""</div><div>Dec 13 13:26:56 emkioblph04 pgpool: 2018-12-13 13:26:56: pid 48073: LOG: execute command: /etc/pgpool-II/failover.sh 1 1 "" ""</div><div>Dec 13 13:26:56 emkioblph04 systemd-logind: New session 3992 of user postgres.</div><div><br></div><div><span><div style="color:rgb(0,0,0);font-family:Helvetica Neue,Helvetica,Arial,sans-serif;font-size:16px">Did you try to do a failover of postgres only, without doing at the same time a failover of pgpool ? So stop postgres on server A but keep pgpool running. This is a simpler case and so you can make sure that your failover script is correct and also make sense of the various errors reported in your log. Normally a failover is a simple operation in postgres, there is no need to stop the surviving database to do the failover. I use the scripts from repmgr but I believe that with the trigger file it is also easy and should not take long</div><div style="color:rgb(0,0,0);font-family:Helvetica Neue,Helvetica,Arial,sans-serif;font-size:16px"><br></div></span></div></span></div><div><br></div><div class="gmail-m_-6098952070359640369ydpb4235fbesignature">Pierre</div></div>
<div><br></div><div><br></div>
</div><div id="gmail-m_-6098952070359640369ydp7fad8c59yahoo_quoted_5503155520" class="gmail-m_-6098952070359640369ydp7fad8c59yahoo_quoted">
<div style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;font-size:13px;color:rgb(38,40,42)">
<div>
On Thursday, December 13, 2018, 9:01:55 PM GMT+1, Juan Carlos Michaca <<a href="mailto:jcmichaca@vinkos.com" target="_blank">jcmichaca@vinkos.com</a>> wrote:
</div>
<div><br></div>
<div><br></div>
<div><div id="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr">Pgpool on server B detects primary pgpool is lost, so Pgpool B asumes master role as you can see in following log entry<br clear="none"><br clear="none"> Dec 13 13:26:36 emkioblph04 pgpool: 2018-12-13 13:26:36: pid 48074: LOG: I am announcing my self as master/coordinator watchdog node<br clear="none"><br clear="none"><br clear="none">And Pgpool B aquires successfuly virtual IP, as shown in following log entry</div><div dir="ltr"><br clear="none"></div><div style="margin-left:40px">Dec 13 13:26:45 emkioblph04 pgpool: 2018-12-13 13:26:45: pid 48380: LOG: successfully acquired the delegate IP:"10.101.21.99"</div><div dir="ltr"><br clear="none">And if I run ifconfig command y validate server B gets successfully virtual IP.<br clear="none"><br clear="none">After failover script runs PG Backend B is able to accept read and write queries. I tested it by connect directly to PG Backend B and run CREATE DATABASE query.</div><div dir="ltr"></div><div dir="ltr"><br clear="none"></div><div dir="ltr">In pgpool log I can see pgpool B keeps looking for a backend node; I have a lot of entries like:</div><div dir="ltr"><br clear="none"></div><div style="margin-left:40px">Dec 13 13:26:57 emkioblph04 pgpool: 2018-12-13 13:26:57: pid 48073: LOG: find_primary_node_repeatedly: waiting for finding a primary node<br clear="none">Dec 13 13:26:57 emkioblph04 pgpool: 2018-12-13 13:26:57: pid 48073: LOG: find_primary_node: checking backend no 0<br clear="none">Dec 13 13:26:57 emkioblph04 pgpool: 2018-12-13 13:26:57: pid 48073: LOG: find_primary_node: checking backend no 1</div><div style="margin-left:40px">.....<br clear="none"></div><div style="margin-left:40px"><div>Dec 13 13:26:57 emkioblph04 pgpool: 2018-12-13 13:26:57: pid 48073: LOG: find_primary_node: checking backend no 0<br clear="none">Dec 13 13:26:57 emkioblph04 pgpool: 2018-12-13 13:26:57: pid 48073: LOG: find_primary_node: checking backend no 1</div></div><div dir="ltr"><br clear="none"></div><div>I'm unable to get watchdog info by running pcp_watchdog_info -h <a shape="rect" href="http://pgnode02.mydomain.com" rel="nofollow" target="_blank">pgnode02.mydomain.com</a> -p 19000 -U postgres -d. I got the error</div><div><br clear="none"></div><div style="margin-left:40px">ERROR: unable to read data from socket.<br clear="none"></div><div dir="ltr"><br clear="none"></div><div dir="ltr"></div><div dir="ltr">I share you detailed log file from server B, after I shut down Pgpool A.<br clear="none"><br clear="none">Thank you Pierre<br clear="none"></div></div></div></div></div></div></div><br clear="none"><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187yqt1818900014" id="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187yqt59172"><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail_quote"><div dir="ltr">On Thu, Dec 13, 2018 at 1:24 PM Pierre Timmermans <<a shape="rect" href="mailto:ptim007@yahoo.com" rel="nofollow" target="_blank">ptim007@yahoo.com</a>> wrote:<br clear="none"></div><blockquote class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp2f12b4dyahoo-style-wrap" style="font-family:Helvetica Neue,Helvetica,Arial,sans-serif;font-size:16px"><div><div>What is in the log of the pgpool on server B ? You should see in the log of pgpool on node B a lot of information, normally the watchdog detects that the primary pgpool is lost, then it logs about the new consensus and the election of a new leader. If the node B takes over the primary role, you will see that the VIP is acquired by node B,...Also because the primary postgres was also lost, in the log of on node B you should also see that the promotion of the standby database (on node B) to primary was done.<br clear="none"></div><div><br clear="none"></div><div>Maybe you can try the pcp_watchdog_info utility on the pgpool of server B, if it does not respond than indeed pgpool B might be stuck..</div><div><br clear="none"></div><div>Maybe you can post the log of the pgpool B at the time you shutted down the server A ?</div><div><br clear="none"></div><div><br clear="none"></div><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp2f12b4dsignature">Pierre</div></div>
<div><br clear="none"></div><div><br clear="none"></div>
</div><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yahoo_quoted" id="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yahoo_quoted_5297232085">
<div>
<div>
On Thursday, December 13, 2018, 8:08:01 PM GMT+1, Juan Carlos Michaca <<a shape="rect" href="mailto:jcmichaca@vinkos.com" rel="nofollow" target="_blank">jcmichaca@vinkos.com</a>> wrote:
</div>
<div><br clear="none"></div>
<div><br clear="none"></div>
<div><div id="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241"><div><div dir="ltr"><div dir="ltr"><div>Hi Pierre;</div><div><br clear="none"></div><div>Thanks for your reply.</div><div><br clear="none"></div><div>Currently I have just 2 connections in pg_stat_activity, and num_init_children is set to 32 in pgpool.conf.</div><div><br clear="none"></div><div>I set num_init_children to 50 and problem persists, I should restart pgpool force it to accept connections.</div><div><br clear="none"></div><div>I share my pgpool.conf<br clear="none"></div><div><br clear="none"></div><div>Regards.<br clear="none"></div><div><br clear="none"></div><div><br clear="none"></div></div></div><br clear="none"><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241yqt1984969528" id="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241yqt41911"><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241gmail_quote"><div dir="ltr">On Thu, Dec 13, 2018 at 8:33 AM Pierre Timmermans <<a shape="rect" href="mailto:ptim007@yahoo.com" rel="nofollow" target="_blank">ptim007@yahoo.com</a>> wrote:<br clear="none"></div><blockquote class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241gmail-m_-3769768005786647630ydped65669cyahoo-style-wrap" style="font-family:Helvetica Neue,Helvetica,Arial,sans-serif;font-size:16px"><div><div>Hi <br clear="none"></div><div><br clear="none"></div><div>The most common reason for that is when the parameter num_init_children is too low: when there are more connections to postgres than num_init_children then pgpool does not give an error but it refuses (or it queues it maybe) without giving an error.</div><div><br clear="none"></div><div>So count the number of connection (from pg_stat_activity) and if it is equal to num_init_children then you have the answer...<br clear="none"></div><div><br clear="none"></div><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241gmail-m_-3769768005786647630ydped65669csignature">Pierre</div></div>
<div><br clear="none"></div><div><br clear="none"></div>
</div><div class="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241gmail-m_-3769768005786647630ydp9a39d188yahoo_quoted" id="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241gmail-m_-3769768005786647630ydp9a39d188yahoo_quoted_5304813720">
<div>
<div>
On Thursday, December 13, 2018, 2:13:52 AM GMT+1, Juan Carlos Michaca <<a shape="rect" href="mailto:jcmichaca@vinkos.com" rel="nofollow" target="_blank">jcmichaca@vinkos.com</a>> wrote:
</div>
<div><br clear="none"></div>
<div><br clear="none"></div>
<div><div id="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241gmail-m_-3769768005786647630ydp9a39d188yiv9962263192"><div dir="ltr"><div dir="ltr"><p dir="ltr" id="gmail-m_-6098952070359640369ydp7fad8c59yiv4244123187gmail-m_1488764841062890967ydp8b82c644yiv9833616241gmail-m_-3769768005786647630ydp9a39d188yiv9962263192gmail-docs-internal-guid-104ef2e2-7fff-8d50-2e2a-ae3a9e8136d2" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Hi all,</span></p><br clear="none"><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">I am testing pgpool in this scenario</span></p><br clear="none"><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;margin-left:36pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Pgpool A(Master) + PG Backend A (primary)</span></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;margin-left:36pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Pgpool B(Standby) + PG Backend B (replica with streaming replication)</span></p><div><br clear="none"></div><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">At this point I'm able to run queries to pgpool from any address in my network.<br clear="none"></span></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"><br clear="none"></span></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">If I shut down Pgpool A(Master) and its PG Backend (primary), Pgpool B(now master) is not accepting any connections until I restart it. </span></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap"><br clear="none"></span></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">After restarting Pgpool B(now master) I can connect to pgpool from any host and run queries.</span></p><br clear="none"><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Please, can you help to figure out what's could be wrong?</span></p><br clear="none"><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent;font-weight:400;font-style:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap">Regards</span></p></div></div></div>_______________________________________________<br clear="none">pgpool-general mailing list<br clear="none"><a shape="rect" href="mailto:pgpool-general@pgpool.net" rel="nofollow" target="_blank">pgpool-general@pgpool.net</a><br clear="none"><a shape="rect" href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="nofollow" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br clear="none"></div>
</div>
</div></div></blockquote></div></div></div></div></div>
</div>
</div></div></blockquote></div></div></div></div></div>
</div>
</div></div></blockquote></div>