<html><head><meta http-equiv="Content-Type" content="text/html charset=iso-2022-jp"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div>$BD9ED$5$s(B</div><div><br></div><div>$B$$$D$b$*@$OC$K$J$C$F$*$j$^$9!#(B</div><div>$B%"%$!&%(%9!&%S!<0p3@$G$9!#(B</div><div><br></div><div>$BDI2C$N<ALd$465<x$"$j$,$H$&$4$6$$$^$$$7$?!#(B</div><div>$B$G$O(Bfailback$B$O(Bpgpool-II$B4IM}2<$KAH$_9~$`$@$1$G!"(B</div><div>$B$5$i$K(BPrimary$B>uBV$KLa$9$h$&$J9M$(J}$G$O$J$$$N$G$9$M!#(B</div><div><br></div><div>$B8=>u$G$O(Bdb01$B"*(Bdb02$B"*(Bdb03$B$H=g$K%U%'%$%k%*!<%P$7$?>l9g$K(B</div><div>db03$B"*(Bdb01 or db02$B$K%U%'%$%k%*!<%P$G$-$J$$>u67$G$9!#(B</div><div><br></div><div>$B$3$A$i$O(Bpgpool-II$B$N;EMM$J$s$G$7$g$&$+!)(B</div><div>db03$B$+$iI|5l$7$?(Bdb01 or db02$B$K%U%'%$%k%*!<%P$9$k$h$&(B</div><div>$B@_Dj$9$k$3$H$O2DG=$G$7$g$&$+!#(B</div><div><br></div><div>$BN)$FB3$1$N<ALd$K$J$C$F$7$^$$?=$7Lu$4$6$$$^$;$s!#(B</div><div><br></div><br><div><div>2013/11/11 15:54$B!"(BYugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>> $B$N%a!<%k!'(B</div><br class="Apple-interchange-newline"><blockquote type="cite"><div style="font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;">$B0p3@$5$s(B<br><br>$BD9ED$G$9!#(B<br><br>On Thu, 7 Nov 2013 23:09:45 +0900<br>InagakiTadashi <<a href="mailto:tadashi.1027@gmail.com">tadashi.1027@gmail.com</a>> wrote:<br><br><blockquote type="cite">$BD9ED$5$s(B<br><br>$B$$$D$b$*@$OC$K$J$C$F$*$j$^$9!#(B<br>$B%"%$!&%(%9!&%S!<0p3@$G$9!#(B<br><br>$B@_Dj$r0J2<$K$7!"(Bfailover-stream.sh$B$NFbMF$b$4;XE&$N(B<br>$B$b$N$KJQ99$7$?$i$&$^$/9T$-$^$7$?!#(B<br>$B$I$&$b$"$j$,$H$&$4$6$$$^$7$?!#(B<br></blockquote><br>$B$4O"Mm$"$j$,$H$&$4$6$$$^$9!#(B<br>$B$&$^$/$$$C$F$h$+$C$?$G$9!*(B<br><br><blockquote type="cite"><br>$B$^$?!"DI2C$N<ALd$K$J$C$F$$$^$$$^$9$,!"(Bpgpool-II$B$G$O(B<br>db03$B$,(BPrimary$B$N>uBV$G(Bdb01$B!"(Bdb02$B$H%*%s%i%$%s%j%+%P%j$GI|5l$7!"(B<br>$B$=$N8e(Bdb03$B$K>c32H/@8$7$?>l9g$N@Z$jBX$($r(Bfailback$B$H(B<br>$B$$$&$s$G$7$g$&$+!#(B<br>failback$B$,$o$+$j$E$i$+$C$?$N$G<ALd$5$;$F$$$?$@$-$^$7$?!#(B<br></blockquote><br>$B@$4V0lHL$N8@$&!V<g7O!&BT5!7O$NLr3d$r>c32$NA0$N>uBV$KLa$9$3$H!W$N0UL#$H$O(B<br>$B0c$&0UL#$GMQ$$$F$$$k$N$G:.Mp$5$l$k$+$b$7$l$^$;$s!#(B<br><br>pgpool-II$B$G$O(B fail back$B$r!V0lEY$G(Bpgpool-II$B4IM}2<$+$i@Z$jN%$7$?%N!<%I(B<br>($BC"$7%@%&%s>uBV$G$O$J$$(B)$B$r!":F$S(Bpgpool-II$B4IM}2<$KI|5"$5$;$kA`:n!W$N0U(B<br>$BL#$G;H$C$F$$$^$9!#(B<br><br><blockquote type="cite"><br>[postgresql.conf]<br>========================================================================<br>ailover_command = $B!H(Bfailover-stream.sh %d %P %H /tmp/trigger_file0"<br><br>[failover-stream.sh]<br>========================================================================<br>#! /bin/sh<br>failed_node=$1<br>old_primary=$2<br>new_master=$3<br>trigger_file=$4<br><br>if [ $failed_node -eq $old_primary ]; then<br> su - postgres -c "/usr/bin/ssh -T $new_master /bin/touch $trigger_file"<br>fi<br><br>exit 0<br><br>[$B<B9T7k2L(B]<br>========================================================================<br>[postgres@db03 ~]$ psql -h 192.168.100.98 -p 9999<br>psql (9.2.4)<br>Type "help" for help.<br><br>postgres=# SHOW POOL_NODES;<br>node_id | hostname | port | status | lb_weight | role <br>---------+----------------+------+--------+-----------+---------<br>0 | 192.168.100.34 | 5432 | 2 | 0.333333 | primary<br>1 | 192.168.100.35 | 5432 | 2 | 0.333333 | standby<br>2 | 192.168.100.36 | 5432 | 2 | 0.333333 | standby<br>(3 rows)<br><br>postgres=# SHOW POOL_NODES;<br>The connection to the server was lost. Attempting reset: ^[[ASucceeded.<br>postgres=# SHOW POOL_NODES;<br>node_id | hostname | port | status | lb_weight | role <br>---------+----------------+------+--------+-----------+---------<br>0 | 192.168.100.34 | 5432 | 3 | 0.333333 | standby<br>1 | 192.168.100.35 | 5432 | 2 | 0.333333 | primary<br>2 | 192.168.100.36 | 5432 | 2 | 0.333333 | standby<br>(3 rows)<br><br>postgres=# SHOW POOL_NODES;<br>The connection to the server was lost. Attempting reset: Succeeded.<br>postgres=# SHOW POOL_NODES;<br>node_id | hostname | port | status | lb_weight | role <br>---------+----------------+------+--------+-----------+---------<br>0 | 192.168.100.34 | 5432 | 3 | 0.333333 | standby<br>1 | 192.168.100.35 | 5432 | 3 | 0.333333 | standby<br>2 | 192.168.100.36 | 5432 | 2 | 0.333333 | primary<br>(3 rows)<br><br>postgres=#<span class="Apple-converted-space"> </span><br><br>========================================================================<br><br><br><br>2013/11/06 19:15$B!"(BYugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>> $B$N%a!<%k!'(B<br><br><blockquote type="cite">$B0p3@$5$s(B<br><br>$BD9ED$G$9!#(B<br><br>$BJV?.$,CY$/$J$C$F$7$^$$?=$7Lu$"$j$^$;$s!#(B<br><br>$BE:IUD:$$$?(B failover-stream.sh $B$rGR8+$7$?$H$3$m(B<br>db01 $B$H(B db03 $B$G$OF1$8FbMF!"(Bdb02 $B$G$O0c$&FbMF$H$J$C$F$$$^$7$?!#(B<br><br>db01/db03 $B$G$O!V%@%&%s$7$?%N!<%I$N(BID$B$,(B 0 $B$N>l9g$K%U%'!<%k%*!<%P!J(Btrigger<span class="Apple-converted-space"> </span><br>$B%U%!%$%k$r(B touch$B!K!W$H$J$C$F$*$j!"(Bdb02 $B$G$O!V%@%&%s$7$?%N!<%I(BID $B$,(B 2 $B$N(B<br>$B>l9g$K%U%'!<%k%*!<%P!W$H$J$C$F$$$^$7$?!#!J0J2<H4?h!K(B<br><br>($failed_node $B$K$O%@%&%s$7$?%N!<%I(BID (%d) $B$,F~$C$F$$$k!K(B<br># Do nothing if standby goes down.<br>if [ $failed_node != 0 ]; then<br> exit 0; ~~ $B$3$l$,(B db02 $B$G$O(B 2<br>fi<br><br>$B$3$N@_Dj$G$9$H%N!<%I(BID = 1 $B$N>l9g$K$O%U%'%$%k%*!<%P$,H/@8$;$:!"(Bdb02 $B$,(B<br>$BMn$A$?>l9g$O$3$l$KAjEv$9$k8=>]$H;W$o$l$^$9!#(B<br><br><blockquote type="cite">db01$B!"(Bdb02$B!"(Bdb03$B$N$I$N%5!<%P$,(BPrimary$B$G$"$C$F$b!"(BStandby$B$K@Z$jBX$o$k$h$&(B<br>$B@_Dj$7$?$$$H;W$C$F$*$j$^$9(B<br></blockquote><br>$B%9%/%j%W%H$NCf$G!V0JA0$N(B Primary $B%N!<%I(BID$B!W$rMQ$$$k$K$O!"(B<a href="http://pgpool.co">pgpool.co</a>nf $B$N(B<br>failover_command $B$NCf$G(B %P $B$r;XDj$7$^$9!#(B<br>$B!J;29M!'(B<a href="http://www.pgpool.net/docs/latest/pgpool-ja.html#FAILBACK_COMMAND">http://www.pgpool.net/docs/latest/pgpool-ja.html#FAILBACK_COMMAND</a>$B!K(B<br><br>$BNc$($P(B<br><br>failover_command = "failover.sh %d %P %H"<br><br>$B$H;XDj$9$k$H!"(Bfailover.sh $B$K$O0J2<$,EO$5$l$^$9!#(B<br><br>- $B%@%&%s$7$?%5!<%P$N%N!<%I(BID<br>- $B5l%W%i%$%^%j%5!<%P$N%N!<%I(BID<br>- $B?7$7$$%^%9%?!J(B<a href="http://pgpool.co">pgpool.co</a>nf $B$K;XDj$7$?%P%C%/%(%s%I$NFb!"@8$-$F$$$FHV9f$,(B<br> $B:G$b<c$$$b$N!K$N%[%9%HL>(B<br><br>$B$3$N@_Dj$G(B failover.sh $B$r0J2<$N$h$&$K$9$k$H!V!J5l!K%W%i%$%^%j$,Mn$A$?>l9g$K$O(B<br>$B?7$7$$%^%9%?!<$r?7%W%i%$%^%j$K>:3J$5$;$k!W$H$$$&=hM}$,2DG=$G$9!#(B<br>$B!JC1=c$K?7%^%9%?!<$r?7%W%i%$%^%j$K$G$-$k>l9g$N=hM}$G$9!#!K(B<br><br>failed_node_id = $1<br>old_primary_id = $2<br>new_master_host = $3<br><br>if [ $filed_node_id = $old_primary_id ];then<br> su - postgres -c "/usr/bin/ssh -T $new_master_host /bin/touch $trigger_file"<br>fi<br><br><br>On Tue, 29 Oct 2013 19:31:03 +0900<br>$B0p3@(B <<a href="mailto:tadashi.1027@gmail.com">tadashi.1027@gmail.com</a>> wrote:<br><br><blockquote type="cite">$B$$$D$b$*@$OC$K$J$C$F$*$j$^$9!#(B<br>$B%"%$!&%(%9!&%S!<0p3@$G$9!#(B<br><br>$B%P%C%/%(%s%I(BDB3$BBf9=@.;~$N%U%'%$%k%*!<%PH/@8;~$K@Z$jJQ$o$k$H$-$H!"(B<br>$B@Z$jBX$o$i$J$$$H$-$,$"$j$^$9!#(B<br>db01$B$,Mn$A$?$H$-$O(Bdb02$B$K@Z$jBX$o$k3NN)$,9b$$$G$9$,!"(B<br>db02$B$,Mn$A$?$H$-$OI,$:@Z$jBX$o$j$^$;$s!#(B<br>$B@Z$jBX$o$i$J$$>e$K(Bfailover: set new primary node: -1$B$K$J$C$F$7$^$$$^$9!#(B<br><br>db01$B!"(Bdb02$B!"(Bdb03$B$N$I$N%5!<%P$,(BPrimary$B$G$"$C$F$b!"(BStandby$B$K@Z$jBX$o$k$h$&(B<br>$B@_Dj$7$?$$$H;W$C$F$*$j$^$9!#(B<br><br>$B$*K;$7$$$H$3$mBgJQ<:Ni$$$?$7$^$9$,!"$465<x$N$[$I59$7$/$*4j$$CW$7$^$9!#(B<br><br>$BE:IU%U%!%$%k$O(Bdb01$B!A(Bdb03$B$^$G$N(B<a href="http://pgpool.co">pgpool.co</a>nf$B$H(Bfailover-stream.sh<<a href="http://pgpool.xn--conffailover-stream-ei4r.sh">http://pgpool.xn--conffailover-stream-ei4r.sh</a>><br>$B$K$J$j$^$9!#(B<br>$B9=@.!"%P!<%8%g%s!"%U%'%$%k%*!<%P;~$N%m%0$O0J2<$N$h$&$K$J$C$F$*$j$^$9!#(B<br><br>$B!Z9=@.![(B<br>pgpool-II$B$H(BPostgreSQL$B$rF15o$5$;$?%5!<%P(B3$BBf9=@.$G$9!#(B<br><br>host ipaddress<br>db01 192.168.100.34<br>db02 192.168.100.35<br>db03 192.168.100.36<br><br>$B!Z%P!<%8%g%s![(B<br>pgpool-II version 3.3.1 (tokakiboshi)<br>psql (PostgreSQL) 9.2.4<br><br>$B!Z%U%'%$%k%*!<%P;~$N%m%0![(B<br>Oct 29 18:46:23 m2m-pdbs01 pgpool-II[48029]: connect_inet_domain_socket:<br>getsockopt() detected error: Connection refused<br>Oct 29 18:46:23 m2m-pdbs01 pgpool-II[48029]: make_persistent_db_connection:<br>connection to 172.16.64.35(5432) failed<br>Oct 29 18:46:23 m2m-pdbs01 pgpool-II[48029]: health check failed. 1 th host<br>172.16.64.35 at port 5432 is down<br>Oct 29 18:46:23 m2m-pdbs01 pgpool-II[48029]: set 1 th backend down status<br>Oct 29 18:46:23 m2m-pdbs01 pgpool-II[48029]: wd_start_interlock: start<br>interlocking<br>Oct 29 18:46:23 m2m-pdbs01 pgpool-II[48044]: wd_send_response: failover<br>request from other pgpool is canceled because it's while switching<br>Oct 29 18:46:23 m2m-pdbs01 pgpool-II[48044]: wd_send_response:<br>WD_STAND_FOR_LOCK_HOLDER received but lock holder exists already<br>Oct 29 18:46:24 m2m-pdbs01 pgpool-II[48029]: starting degeneration.<br>shutdown host 172.16.64.35(5432)<br>Oct 29 18:46:24 m2m-pdbs01 pgpool-II[48029]: Restart all children<br>Oct 29 18:46:24 m2m-pdbs01 pgpool-II[48029]: find_primary_node_repeatedly:<br>waiting for finding a primary node<br>Oct 29 18:46:32 m2m-pdbs01 pgpool-II[31414]: connect_inet_domain_socket:<br>getsockopt() detected error: Connection refused<br>Oct 29 18:46:32 m2m-pdbs01 pgpool-II[31414]: make_persistent_db_connection:<br>connection to 172.16.64.35(5432) failed<br>Oct 29 18:46:32 m2m-pdbs01 pgpool-II[31414]: check_replication_time_lag:<br>could not connect to DB node 1, check sr_check_user and sr_check_password<br>Oct 29 18:46:34 m2m-pdbs01 pgpool-II[48029]: wd_end_interlock: end<br>interlocking<br>Oct 29 18:46:35 m2m-pdbs01 pgpool-II[48029]: failover: set new primary<br>node: -1<br>Oct 29 18:46:35 m2m-pdbs01 pgpool-II[48029]: failover: set new master node:<br>0<br></blockquote><br><br>--<span class="Apple-converted-space"> </span><br>Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br></blockquote><br></blockquote><br><br>--<span class="Apple-converted-space"> </span><br>Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>></div></blockquote></div><br></body></html>