<html><head><meta http-equiv="Content-Type" content="text/html charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">Hi all,<div class=""><br class=""></div><div class="">I'm having an issue with online recovery.</div><div class=""><br class=""></div><div class=""><u class="">My pgpool config</u>:</div><div class=""><ul class="MailOutline"><li class="">two servers cara0 and cara1 (virtual ip resolves to caracara - we name all our servers with orange names, so this was a perfect name :-)</li><li class="">both run pgpool on port 5432 with watchdog - failover works great</li><li class="">both have postgresql on port 54321 as backend nodes</li><li class="">pgpool is configured with replication and load balancing</li><li class="">I'm using the pgpool package from the epel repo on CentOS 7 </li><ul class=""><li class="">postgresql-pgpool-II-3.4.3-3.el7.x86_64</li><li class="">postgresql-pgpool-II-extensions-3.4.3-3.el7.x86_64</li></ul></ul><div class=""><br class=""></div></div><div class=""><u class="">Issue</u>:</div><blockquote style="margin: 0 0 0 40px; border: none; padding: 0px;" class=""><div class="">I've been running pgpool for a few months now with my less crucial db's and everything has been fine. Recently, I moved the remaining, mission critical and busier db's onto the cluster. Something happened that made the cluster drop one backend node (cara1) and I have been unable to do an online recovery. Once I have the recovery process working properly, I will continue to debug my setup to see what caused the degradation of backend node cara1.</div><div class=""><br class=""></div></blockquote>Online recovery has worked for me in the past, but in the last couple of days (early mornings) I have attempted a recovery and it always times out at stage 2.<div class=""><br class=""><blockquote style="margin: 0 0 0 40px; border: none; padding: 0px;" class=""><div style="margin: 0px; line-height: normal; font-family: Monaco; color: rgb(245, 245, 245); background-color: rgb(0, 0, 0);" class=""><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:42:41 cara0 pgpool[548]: [617-1] 2015-10-27 07:42:41: pid 548: LOG: fork a new PCP child process with pid: 23500</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:42:41 cara0 pgpool: 2015-10-27 07:42:41: pid 548: LOG: fork a new PCP child process with pid: 23500</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool[23500]: [617-1] 2015-10-27 07:49:21: pid 23500: LOG: starting recovering node 1</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool: 2015-10-27 07:49:21: pid 23500: LOG: starting recovering node 1</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool[23500]: [618-1] 2015-10-27 07:49:21: pid 23500: LOG: node recovery, CHECKPOINT in the 1st stage done</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool[23500]: [619-1] 2015-10-27 07:49:21: pid 23500: LOG: executing recovery</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool[23500]: [619-2] 2015-10-27 07:49:21: pid 23500: DETAIL: starting recovery command: "SELECT pgpool_recovery('pgpool-recovery-base.sh', 'cara1', '/var/lib/pgsql/data', '54321')"</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool[23500]: [620-1] 2015-10-27 07:49:21: pid 23500: LOG: executing recovery</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool[23500]: [620-2] 2015-10-27 07:49:21: pid 23500: DETAIL: disabling statement_timeout</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool: 2015-10-27 07:49:21: pid 23500: LOG: node recovery, CHECKPOINT in the 1st stage done</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool: 2015-10-27 07:49:21: pid 23500: LOG: executing recovery</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool: 2015-10-27 07:49:21: pid 23500: DETAIL: starting recovery command: "SELECT pgpool_recovery('pgpool-recovery-base.sh', 'cara1', '/var/lib/pgsql/data', '54321')"</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool: 2015-10-27 07:49:21: pid 23500: LOG: executing recovery</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:49:21 cara0 pgpool: 2015-10-27 07:49:21: pid 23500: DETAIL: disabling statement_timeout</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:59:16 cara0 pgpool[23500]: [621-1] 2015-10-27 07:59:16: pid 23500: LOG: node recovery, 1st stage is done</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:59:16 cara0 pgpool[23500]: [622-1] 2015-10-27 07:59:16: pid 23500: LOG: node recovery, starting 2nd stage</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:59:16 cara0 pgpool: 2015-10-27 07:59:16: pid 23500: LOG: node recovery, 1st stage is done</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 07:59:16 cara0 pgpool: 2015-10-27 07:59:16: pid 23500: LOG: node recovery, starting 2nd stage</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool[23500]: [623-1] 2015-10-27 08:02:19: pid 23500: LOG: wait_connection_closed: existing connections did not close in 180 sec.</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool[23500]: [624-1] 2015-10-27 08:02:19: pid 23500: ERROR: node recovery failed, waiting connection closed in the other pgpools timeout</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool[23500]: [625-1] 2015-10-27 08:02:19: pid 23500: FATAL: unable to read from client</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool[23500]: [625-2] 2015-10-27 08:02:19: pid 23500: DETAIL: pcp_read failed with error : "Success"</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool[548]: [618-1] 2015-10-27 08:02:19: pid 548: LOG: PCP child process with pid: 23500 exits with status 256</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool: 2015-10-27 08:02:19: pid 23500: LOG: wait_connection_closed: existing connections did not close in 180 sec.</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool: 2015-10-27 08:02:19: pid 23500: ERROR: node recovery failed, waiting connection closed in the other pgpools timeout</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool: 2015-10-27 08:02:19: pid 23500: FATAL: unable to read from client</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool: 2015-10-27 08:02:19: pid 23500: DETAIL: pcp_read failed with error : "Success"</div><div style="margin: 0px; line-height: normal;" class="">Oct 27 08:02:19 cara0 pgpool: 2015-10-27 08:02:19: pid 548: LOG: PCP child process with pid: 23500 exits with status 256</div></div></blockquote><div class=""><div class=""><br class="webkit-block-placeholder"></div><div class="">Is it possible to get a list of open connections to help me sort out what is refusing to drop its connection?</div><div class=""><br class=""></div><div apple-content-edited="true" class=""><span style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; orphans: 2; widows: 2;" class="">Cheers,</span><br style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; orphans: 2; widows: 2;" class=""><span style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; orphans: 2; widows: 2;" class="">Gervais</span><br style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; orphans: 2; widows: 2;" class=""><span style="color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; orphans: 2; widows: 2; font-size: 11px;" class="">------------------------------------<br class="">Gervais de Montbrun</span><div class=""><span style="text-align: start; text-indent: 0px; orphans: 2; widows: 2;" class=""><span style="font-size: 11px;" class="">Systems Administrator</span><br class=""><span style="font-size: 11px;" class="">GTalk: <a href="mailto:gervais@silverorange.com" class="">gervais@silverorange.com</a></span></span></div>
</div>
<br class=""></div></div></body></html>