<div dir="ltr"><div>Hello.</div><div><br></div><div>I have not enabled health checking.</div><div><br></div><div>I have uninstalled pgpool version 3.4.7 (tataraboshi). After, I have installed 3.4.6 (tataraboshi) and failover command is executed on standby server.</div><div><br></div><div>Regards.</div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Sat, Jun 25, 2016 at 2:37 AM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">> Hello.<br>
><br>
> I am using "load_balance_mode = off".<br>
<br>
I think load_balance_mode is irrelevant here.<br>
<br>
> When postgresql service on standby node is down, pgpool log file on both<br>
> nodes shows every 10 second:<br>
><br>
> 2016-06-24 15:12:52: pid 5256: ERROR: Failed to check replication time lag<br>
> 2016-06-24 15:12:52: pid 5256: DETAIL: No persistent db connection for the<br>
> node 1<br>
> 2016-06-24 15:12:52: pid 5256: HINT: check sr_check_user and<br>
> sr_check_password<br>
> 2016-06-24 15:12:52: pid 5256: CONTEXT: while checking replication time lag<br>
> 2016-06-24 15:12:52: pid 5256: LOG: failed to connect to PostgreSQL server<br>
> on "<a href="http://172.16.0.2:5432" rel="noreferrer" target="_blank">172.16.0.2:5432</a>", getsockopt() detected error "Connection refused"<br>
> 2016-06-24 15:12:52: pid 5256: ERROR: failed to make persistent db<br>
> connection<br>
> 2016-06-24 15:12:52: pid 5256: DETAIL: connection to host:"<a href="http://172.16.0.2:5432" rel="noreferrer" target="_blank">172.16.0.2:5432</a>"<br>
> failed<br>
<br>
It's normal because:<br>
<br>
> 2016-06-24 15:12:52: pid 5256: DETAIL: No persistent db connection for the<br>
> node 1<br>
> 2016-06-24 15:12:52: pid 5256: HINT: check sr_check_user and<br>
<br>
means replicaton delay checking is failing (of course it fails).<br>
<br>
Have you enabled health checking? Otherwise pgpool doesn't notice that<br>
PostgreSQL goes down.<br>
<br>
> If I execute sql sentence "show pool nodes", then failover command is<br>
> executed on standby node.<br>
<br>
Even without health checking, if you try to execute an SQL command,<br>
that could trigger fail over.<br>
<br>
> 2016-06-24 15:14:48: pid 5329: LOG: received degenerate backend request<br>
> for node_id: 1 from pid [5329]<br>
> 2016-06-24 15:14:48: pid 5329: FATAL: failed to create a backend connection<br>
> 2016-06-24 15:14:48: pid 5329: DETAIL: executing failover on backend<br>
> 2016-06-24 15:14:48: pid 1096: LOG: watchdog notifying to start<br>
> interlocking<br>
> 2016-06-24 15:14:48: pid 1096: LOG: watchdog became a new lock holder<br>
> 2016-06-24 15:14:48: pid 1099: LOG: sending watchdog response<br>
> 2016-06-24 15:14:48: pid 1099: DETAIL: WD_STAND_FOR_LOCK_HOLDER received<br>
> but lock holder already exists<br>
> 2016-06-24 15:14:49: pid 1096: LOG: starting degeneration. shutdown host<br>
> 172.16.0.2(5432)<br>
> 2016-06-24 15:14:49: pid 1096: LOG: Restart all children<br>
> 2016-06-24 15:14:49: pid 1096: LOG: execute command:<br>
> /etc/pgpool-II/failover_stream.sh 1 172.16.0.1 /tmp/trigger_file0<br>
> 2016-06-24 15:14:49: pid 5307: LOG: child process received shutdown<br>
> request signal 3<br>
> 2016-06-24 15:14:49: pid 5363: LOG: child process received shutdown<br>
> request signal 3<br>
> 2016-06-24 15:14:49: pid 5314: LOG: child process received shutdown<br>
> request signal 3<br>
><br>
> (... more similar lines in log file ...)<br>
><br>
> 2016-06-24 15:14:49: pid 5328: LOG: child process received shutdown<br>
> request signal 3<br>
> 2016-06-24 15:14:49: pid 5093: LOG: child process received shutdown<br>
> request signal 3<br>
> 2016-06-24 15:14:49: pid 1096: LOG: watchdog notifying to end interlocking<br>
> 2016-06-24 15:14:50: pid 1096: LOG: failover: set new primary node: 0<br>
> 2016-06-24 15:14:50: pid 1096: LOG: failover: set new master node: 0<br>
> 2016-06-24 15:14:50: pid 5412: LOG: failback event detected<br>
> 2016-06-24 15:14:50: pid 5412: DETAIL: restarting myself<br>
> failover done. shutdown host 172.16.0.2(5432)2016-06-24 15:14:50: pid 1096:<br>
> LOG: failover done. shutdown host 172.16.0.2(5432)<br>
> 2016-06-24 15:14:50: pid 5256: ERROR: Failed to check replication time lag<br>
> 2016-06-24 15:14:50: pid 5256: DETAIL: No persistent db connection for the<br>
> node 1<br>
> 2016-06-24 15:14:50: pid 5256: HINT: check sr_check_user and<br>
> sr_check_password<br>
> 2016-06-24 15:14:50: pid 5256: CONTEXT: while checking replication time lag<br>
><br>
><br>
> I disable watchdog ("use_watchdog = off"), then failover command is<br>
> executed on standby server when postgresql service is down.<br>
><br>
> Is this working as expected?<br>
><br>
><br>
> On Fri, Jun 24, 2016 at 3:52 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>> wrote:<br>
><br>
>> Works for me (without watchdog). When I shutdown standy node, it<br>
>> triggers failover.<br>
>><br>
>> 2016-06-24 22:46:28: pid 24757: LOG: reading and processing packets<br>
>> 2016-06-24 22:46:28: pid 24757: DETAIL: postmaster on DB node 1 was<br>
>> shutdown by administrative command<br>
>> 2016-06-24 22:46:28: pid 24757: LOG: received degenerate backend request<br>
>> for node_id: 1 from pid [24757]<br>
>> 2016-06-24 22:46:28: pid 24740: LOG: starting degeneration. shutdown host<br>
>> /tmp(11003)<br>
>> 2016-06-24 22:46:28: pid 24740: LOG: Restart all children<br>
>> 2016-06-24 22:46:28: pid 24740: LOG: execute command:<br>
>> /home/t-ishii/work/pgpool-II/current/aaa/etc/failover.sh 1 /tmp 11003<br>
>> /home/t-ishii/work/pgpool-II/current/aaa/data1 0 0 /tmp 0 11002<br>
>> /home/t-ishii/work/pgpool-II/current/aaa/da<br>
>><br>
>> Best regards,<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> > Hello.<br>
>> > I have two nodes with postgresql with streaming replication and pgpool<br>
>> with<br>
>> > watchdog.<br>
>> ><br>
>> > Using pgpool version 3.4.7 (tataraboshi):<br>
>> > - If postgres service of standby server is down, then failover_command is<br>
>> > not executed (no lines showed in pgpool log file).<br>
>> > - If postgres service of primary server is down, then failover_command is<br>
>> > executed (it is showed in pgpool log file).<br>
>> ><br>
>> > Using pgpool version 3.4.6 (tataraboshi):<br>
>> > - If postgres service of standby server is down, then failover_command is<br>
>> > executed (it is showed in pgpool log file).<br>
>> > - If postgres service of primary server is down, then failover_command is<br>
>> > executed (it is showed in pgpool log file).<br>
>> ><br>
>> > Why does failover_command is not executed with version 3.4.7 when<br>
>> postgres<br>
>> > service in standby server is down?<br>
>> ><br>
>> > Thanks in advance.<br>
>><br>
</blockquote></div><br></div>