<div dir="ltr">Hello Bo<div class="gmail_extra"><br><div class="gmail_quote">On Tue, Oct 24, 2017 at 2:59 AM, Bo Peng <span dir="ltr"><<a href="mailto:pengbo@sraoss.co.jp" target="_blank">pengbo@sraoss.co.jp</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>
> > > Then, Postgresql service is started in node 0.<br>
> > > node 0 is standby, with status 3<br>
> > > node 1 is primary, with status 2<br>
<br>
How did you start the node0 in this step?<br>
I think the "recovery" step is missed by you.<br>
<br></blockquote><div><br></div><div>Excuse me, I think my explanation is not complete.</div><div>In this step I start node0 with command "systemctl start postgresql-9.6".</div><div>Previously I have not recovered node0 because I have started replication between both nodes. I know that in a production environment it is necessary a replication between both nodes, but I did not make this replication because I wanted to check that pcp_attach_command will fail and node0 will continue with status 3 because no replication between nodes.</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
> I have checked these steps but attaching node1 (after failover without<br>
> recovery) instead of node 0, and I can't reproduce this situation with node<br>
> 1. Do you know if this behaviour is by desing of Pgpool? Why is it<br>
> necessary to use pcp_recovery_node instead of pcp_attach_node?<br>
<br>
If you recover the node as a standby and then attach it to pgpool,<br>
"pcp_recovery_node" is not necessary.<br>
<br>
<br>
Let's confirm the scenario of doing failover and recover downed backend node as a standby.<br>
<br>
1. Star node0 and node1<br>
<br>
node0 : primay<br>
node1 : standby<br>
<br>
2. Stop node0, and failover occurs<br>
<br>
node0 : down<br>
node1 : primary <= failover<br>
<br>
3. Recover node0 as standby<br>
<br>
node0 : standby<br>
node1 : primary<br>
<br>
There are two ways to recover the downed node.<br>
<br>
(1) Recover node0 as standby by using "pcp_recovery_node".<br>
<br>
"pcp_recovery_node" will recover the downed node and attach it to pgpool.<br>
But to use the commad,you need configure 'recovery_1st_stage_command' parameter.<br>
<br>
Please see the following document for more details about configuring Pgpool-II online recovery.<br>
<br>
<a href="http://www.pgpool.net/docs/latest/en/html/example-cluster.html" rel="noreferrer" target="_blank">http://www.pgpool.net/docs/<wbr>latest/en/html/example-<wbr>cluster.html</a><br>
<br>
(2) Recover node0 as a standby by using such as "pg_basebackup" command,<br>
then attach the node to pgpool. Because pgpool has already dettach the<br>
node, you need attach the node to pgpool again, to let pgpool know the node.<br>
Without attach node, the status of the node will be "down", even if it is running as standby.<br>
<br>
If you just start the downed PostgreSQL node by using "pg_ctl start" without recovery,<br>
the node will be started as a primary.<br>
<br></blockquote><div><br></div><div>Hello Bo.</div><div>Thank you for your explanation. </div><div>I use way number 2, with a shell script with Postgresql pg_basebackup command</div><div>For this test, I did not use any recovery way, only I started Postgresql database service, and I run pcp_attach_command on node0. I know it is incorrect. Only I want to check that I run pcp_attach_command on any node and previously I don't have a replication between both node, then pgpool will fail during attach process. I don't understand why pcpool make node0 as primary and node1 as standby if previously node0 is standby and node1 is primary and there is no replication between nodes.</div><div>Excuse me because I think my first email is not enough clear</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
On Mon, 23 Oct 2017 22:13:44 +0200<br>
Lucas Luengas <<a href="mailto:lucasluengas@gmail.com">lucasluengas@gmail.com</a>> wrote:<br>
<br>
> Hello Bo.<br>
> Thank you for your answer.<br>
><br>
> I have checked these steps but attaching node1 (after failover without<br>
> recovery) instead of node 0, and I can't reproduce this situation with node<br>
> 1. Do you know if this behaviour is by desing of Pgpool? Why is it<br>
> necessary to use pcp_recovery_node instead of pcp_attach_node?<br>
><br>
> Kind regards.<br>
><br>
> On Sat, Oct 21, 2017 at 1:30 AM, Bo Peng <<a href="mailto:pengbo@sraoss.co.jp">pengbo@sraoss.co.jp</a>> wrote:<br>
><br>
> > Hello,<br>
> ><br>
> > If you want to start node0 (old primary) as standby,<br>
> > you should use pcp_recovery_node to recovery node0 as standby.<br>
> ><br>
> > If you just restart node0 after failover without recovery,<br>
> > it will run as primary.<br>
> ><br>
> > On Fri, 20 Oct 2017 18:41:36 +0200<br>
> > Lucas Luengas <<a href="mailto:lucasluengas@gmail.com">lucasluengas@gmail.com</a>> wrote:<br>
> ><br>
> > > Hello<br>
> > > I am testing Pgpool 3.4.13 with Postgresql-9.6, with streaming<br>
> > replication<br>
> > > and watchdog, on Centos 7. I have two server. Every server has installed<br>
> > > Pgpool and Postgresql. I have installed pgpool from yum repository.<br>
> > ><br>
> > > Node 0 is primary, with status 2<br>
> > > Node 1 is standby, with status 2<br>
> > ><br>
> > > If Postgresql service is stopped in node 0, then:<br>
> > > node 0 is standby, with status 3<br>
> > > node 1 is primary, with status 2. (failover)<br>
> > ><br>
> > > Then, Postgresql service is started in node 0.<br>
> > > node 0 is standby, with status 3<br>
> > > node 1 is primary, with status 2<br>
> > ><br>
> > > Then, I attach node 0 using pcp_attach_node command.<br>
> > > node 0 is primary, with status 2.<br>
> > > node 1 is standby, with status 2.<br>
> > > Node 0 was changed to primary and node 1 was changed to standby. Why ?<br>
> > Do I<br>
> > > have any error in my setup?<br>
> > > I think the correct result should be:<br>
> > > node 0 is standby, with status 2<br>
> > > node 1 is primary, with status 2<br>
> > ><br>
> > > I have repeated previous steps with pgpool 3.4.12, 3,4.11, 3.4.10 and<br>
> > 3.4.9<br>
> > > with same configuration and same server. I get same results.<br>
> > > Also, I have repeated step with pgpool 3.6.6 and I get same results.<br>
> > ><br>
> > > Some log lines during fallback<br>
> > ><br>
> > > Oct 20 13:41:03 localhost pgpool[9687]: [128-1] 2017-10-20 13:41:03: pid<br>
> > > 9687: LOG: received failback request for node_id: 0 from pid [9687]<br>
> > > Oct 20 13:41:03 localhost pgpool[4913]: [255-1] 2017-10-20 13:41:03: pid<br>
> > > 4913: LOG: watchdog notifying to start interlocking<br>
> > > Oct 20 13:41:03 localhost pgpool[4913]: [256-1] 2017-10-20 13:41:03: pid<br>
> > > 4913: LOG: starting fail back. reconnect host 192.168.0.136(5432)<br>
> > > Oct 20 13:41:03 localhost pgpool[4913]: [257-1] 2017-10-20 13:41:03: pid<br>
> > > 4913: LOG: Node 1 is not down (status: 2)<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [258-1] 2017-10-20 13:41:04: pid<br>
> > > 4913: LOG: Do not restart children because we are failbacking node id 0<br>
> > > host: 192.168.0.136 port: 5432 and we are in streaming replication mode<br>
> > and<br>
> > > not all backends were down<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [259-1] 2017-10-20 13:41:04: pid<br>
> > > 4913: LOG: find_primary_node_repeatedly: waiting for finding a primary<br>
> > node<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [260-1] 2017-10-20 13:41:04: pid<br>
> > > 4913: LOG: find_primary_node: checking backend no 0<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [260-2]<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [261-1] 2017-10-20 13:41:04: pid<br>
> > > 4913: LOG: find_primary_node: primary node id is 0<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [262-1] 2017-10-20 13:41:04: pid<br>
> > > 4913: LOG: watchdog notifying to end interlocking<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [263-1] 2017-10-20 13:41:04: pid<br>
> > > 4913: LOG: failover: set new primary node: 0<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [264-1] 2017-10-20 13:41:04: pid<br>
> > > 4913: LOG: failover: set new master node: 0<br>
> > > Oct 20 13:41:04 localhost pgpool[4913]: [265-1] 2017-10-20 13:41:04: pid<br>
> > > 4913: LOG: failback done. reconnect host 192.168.0.136(5432)<br>
> > > Oct 20 13:41:04 localhost pgpool[9688]: [194-1] 2017-10-20 13:41:04: pid<br>
> > > 9688: LOG: worker process received restart request<br>
> > > Oct 20 13:41:05 localhost pgpool[9687]: [129-1] 2017-10-20 13:41:05: pid<br>
> > > 9687: LOG: restart request received in pcp child process<br>
> > > Oct 20 13:41:05 localhost pgpool[4913]: [266-1] 2017-10-20 13:41:05: pid<br>
> > > 4913: LOG: PCP child 9687 exits with status 256 in failover()<br>
> > > Oct 20 13:41:05 localhost pgpool[4913]: [267-1] 2017-10-20 13:41:05: pid<br>
> > > 4913: LOG: fork a new PCP child pid 10410 in failover()<br>
> > > Oct 20 13:41:05 localhost pgpool[4913]: [268-1] 2017-10-20 13:41:05: pid<br>
> > > 4913: LOG: worker child process with pid: 9688 exits with status 256<br>
> > > Oct 20 13:41:05 localhost pgpool[4913]: [269-1] 2017-10-20 13:41:05: pid<br>
> > > 4913: LOG: fork a new worker child process with pid: 10411<br>
> > > Oct 20 13:41:10 localhost pgpool[9692]: [202-1] 2017-10-20 13:41:10: pid<br>
> > > 9692: LOG: selecting backend connection<br>
> > > Oct 20 13:41:10 localhost pgpool[9692]: [202-2] 2017-10-20 13:41:10: pid<br>
> > > 9692: DETAIL: failback event detected, discarding existing connections<br>
> > ><br>
> > > Kind regards<br>
> ><br>
> ><br>
> > --<br>
> > Bo Peng <<a href="mailto:pengbo@sraoss.co.jp">pengbo@sraoss.co.jp</a>><br>
> > SRA OSS, Inc. Japan<br>
> ><br>
> ><br>
<span class="HOEnZb"><font color="#888888"><br>
<br>
--<br>
Bo Peng <<a href="mailto:pengbo@sraoss.co.jp">pengbo@sraoss.co.jp</a>><br>
SRA OSS, Inc. Japan<br>
<br>
</font></span></blockquote></div><br></div></div>