<div dir="ltr"><div class="gmail_extra">Thanks for looking into this Yugo. Sorry for the long message.<br><br>Start with node01 primary, node02 standby<br><br>-- pgpool.log --<br>2014-07-17 02:05:53 LOG: pid 18498: wd_chk_setuid all commands have setuid bit<br>
2014-07-17 02:05:53 LOG: pid 18498: watchdog might call network commands which using setuid bit.<br>2014-07-17 02:05:53 LOG: pid 18498: wd_create_send_socket: connect() reports failure (Connection refused). You can safely ignore this while starting up.<br>
2014-07-17 02:05:53 LOG: pid 18498: send_packet_4_nodes: packet for node02:9000 is canceled<br>2014-07-17 02:05:56 LOG: pid 18498: wd_escalation: escalating to master pgpool<br>2014-07-17 02:05:58 LOG: pid 18498: wd_escalation: escalated to master pgpool successfully<br>
2014-07-17 02:05:58 LOG: pid 18498: wd_init: start watchdog<br>2014-07-17 02:05:58 LOG: pid 18498: pgpool-II successfully started. version 3.3.2 (tokakiboshi)<br>2014-07-17 02:05:59 LOG: pid 18507: wd_create_hb_recv_socket: set SO_REUSEPORT<br>
2014-07-17 02:05:59 LOG: pid 18508: wd_create_hb_send_socket: set SO_REUSEPORT<br>2014-07-17 02:05:59 LOG: pid 18509: wd_create_hb_recv_socket: set SO_REUSEPORT<br>2014-07-17 02:05:59 LOG: pid 18510: wd_create_hb_send_socket: set SO_REUSEPORT<br>
<br>...<br><br>Stop postgres on node01 to trigger failover to node02<br><br>2014-07-17 02:27:10 LOG: pid 18599: connection closed. retry to create new connection pool.<br>2014-07-17 02:27:10 ERROR: pid 18599: connect_inet_domain_socket: getsockopt() detected error: Connection refused<br>
2014-07-17 02:27:10 ERROR: pid 18599: connection to node01(5432) failed<br>2014-07-17 02:27:10 ERROR: pid 18599: new_connection: create_cp() failed<br>2014-07-17 02:27:10 LOG: pid 18599: degenerate_backend_set: 0 fail over request from pid 18599<br>
2014-07-17 02:27:10 LOG: pid 18498: wd_start_interlock: start interlocking<br>2014-07-17 02:27:10 LOG: pid 18498: wd_assume_lock_holder: become a new lock holder<br>2014-07-17 02:27:11 LOG: pid 18498: starting degeneration. shutdown host node01(5432)<br>
2014-07-17 02:27:11 LOG: pid 18498: Restart all children<br>2014-07-17 02:27:11 LOG: pid 18498: execute command: /usr/bin/sudo -u postgres /var/lib/postgresql/failover_cmd.sh node01 node02<br>2014-07-17 02:27:11 LOG: pid 18498: wd_end_interlock: end interlocking<br>
2014-07-17 02:27:12 LOG: pid 18498: failover: set new primary node: -1<br>2014-07-17 02:27:12 LOG: pid 18498: failover: set new master node: 1<br>2014-07-17 02:27:12 LOG: pid 18498: failover done. shutdown host node01(5432)<br>
2014-07-17 02:27:12 LOG: pid 18546: worker process received restart request<br>2014-07-17 02:27:13 LOG: pid 18545: pcp child process received restart request<br>2014-07-17 02:27:13 LOG: pid 18498: PCP child 18545 exits with status 256 in failover()<br>
2014-07-17 02:27:13 LOG: pid 18498: fork a new PCP child pid 18745 in failover()<br>2014-07-17 02:27:13 LOG: pid 18498: worker child 18546 exits with status 256<br>2014-07-17 02:27:13 LOG: pid 18498: fork a new worker child pid 18746<br>
<br>...<br><br>Failover to node02 is successful.<br>node01 gets a backup of node02 postgresql and start replicating.<br>pgpool still think node01 is shutdown. However, if pcp_attach_node is run here then<br>pgpool will make node01 postgresql primary, but it is standby to node02 right now.<br>
pcp_attach_node is not run.<br><br>Stop postgres on node02 to trigger failover to node01<br><br>2014-07-17 02:37:12 LOG: pid 18742: connection closed. retry to create new connection pool.<br>2014-07-17 02:37:12 ERROR: pid 18742: connect_inet_domain_socket: getsockopt() detected error: Connection refused<br>
2014-07-17 02:37:12 ERROR: pid 18742: connection to node02(5432) failed<br>2014-07-17 02:37:12 ERROR: pid 18742: new_connection: create_cp() failed<br>2014-07-17 02:37:12 LOG: pid 18742: degenerate_backend_set: 1 fail over request from pid 18742<br>
2014-07-17 02:37:12 LOG: pid 18498: wd_start_interlock: start interlocking<br>2014-07-17 02:37:12 LOG: pid 18498: wd_assume_lock_holder: become a new lock holder<br>2014-07-17 02:37:13 LOG: pid 18498: starting degeneration. shutdown host node02(5432)<br>
2014-07-17 02:37:13 ERROR: pid 18498: failover_handler: no valid DB node found<br>2014-07-17 02:37:13 LOG: pid 18498: Restart all children<br>2014-07-17 02:37:13 LOG: pid 18498: execute command: /usr/bin/sudo -u postgres /var/lib/postgresql/failover_cmd.sh node02 <br>
<br>tail log show logging stopped here so failover is not completed. Note the failover command is incomplete. It should be ... failover_cmd.sh node02 node01<br><br>Listing pgpool processes show all with <defunct> tag. This makes sense since pgpool doesn't know node01 is now standby and died. It thinks there are no "good" nodes left in the cluster.<br>
<br>The remaining log entries are logged when I exited my shell session and closing open jobs.<br><br>2014-07-17 02:45:42 LOG: pid 18498: wd_end_interlock: end interlocking<br>2014-07-17 02:45:42 LOG: pid 18498: failover: set new primary node: -1<br>
2014-07-17 02:45:43 LOG: pid 18498: failover done. shutdown host node02(5432)<br>2014-07-17 02:45:44 LOG: pid 18498: PCP child 18745 exits with status 0 in failover()<br>2014-07-17 02:45:44 LOG: pid 18498: fork a new PCP child pid 18977 in failover()<br>
2014-07-17 02:45:44 LOG: pid 18498: received smart shutdown request<br>2014-07-17 02:45:45 LOG: pid 18511: wd_IP_down: ifconfig down succeeded<br>-- /pgpool.log --<br><br><br>I think the real question is how to re-attach node01 as standby so pgpool will know about it.<br>
<br>On a related note, Muhammad Usama had pointed out in another thread that pgpool looks for specific conditions to determine if a node is primary. I think satisfying these conditions may help.<br>
<br></div><div class="gmail_extra">Long<br><br></div><div class="gmail_extra"><br></div></div>