<div dir="ltr"><div>Hi Yugo,<br><br></div>I am using Centos 6.3x64 version.<br><div><div><br>[root@server1 ~]# uname -a<br>Linux server1 2.6.32-279.el6.x86_64 #1 SMP Fri Jun 22 12:19:21 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux<br>
<br></div><div>After I shut down pgpool, i output the process (server1) to the db.log, i don't see any process left over.<br></div><div>I also output the netstat on both server to the db.log, that you may find interesting about.<br>
</div><div><br></div><div>Thanks~<br>Ning<br></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Fri, Mar 8, 2013 at 5:44 AM, Yugo Nagata <span dir="ltr"><<a href="mailto:nagata@sraoss.co.jp" target="_blank">nagata@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">Hi ning,<br>
<br>
Thanks for detailed information. I'll try to reproduce the problem.<br>
<br>
In addition ,could you please provide me some more information?<br>
<br>
1. What's OS version? (just to be sure)<br>
<br>
2. After shutdown of the pgpool on server1, are there any pgpool process left?<br>
In the log of server1, I see port 9999 is still opend to listen while port<br>
9898 is closed. It might mean there are some problem in exiting the pgpool.<br>
<br>
> Mar 7 23:57:42 server1 pgpool[2555]: received smart shutdown request<br>
> Mar 7 23:57:42 server1 pgpool[2555]: watchdog_pid: 2558<br>
> Mar 7 23:57:49 server1 pgpool[4407]: wd_chk_sticky: ifup[/sbin/ip] doesn't have > sticky bit<br>
> Mar 7 23:57:49 server1 pgpool[4408]: bind(:) failed. reason: Success<br>
> Mar 7 23:57:49 server1 pgpool[4408]: unlink(/tmp/.s.PGSQL.9898) failed: No such file or directory<br>
<div class="im">> tcp 0 0 <a href="http://0.0.0.0:9999" target="_blank">0.0.0.0:9999</a> 0.0.0.0:* LISTEN<br>
</div>> tcp 8 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.153:34048" target="_blank">172.16.6.153:34048</a> ESTABLISHED<br>
> tcp 0 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.153:33924" target="_blank">172.16.6.153:33924</a> TIME_WAIT<br>
> tcp 0 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.154:36458" target="_blank">172.16.6.154:36458</a> TIME_WAIT<br>
> tcp 0 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.154:36514" target="_blank">172.16.6.154:36514</a> TIME_WAIT<br>
> tcp 0 0 <a href="http://172.16.6.154:44297" target="_blank">172.16.6.154:44297</a> <a href="http://172.16.6.153:9999" target="_blank">172.16.6.153:9999</a> TIME_WAIT<br>
> tcp 9 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.153:34008" target="_blank">172.16.6.153:34008</a> CLOSE_WAIT<br>
> tcp 0 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.154:36486" target="_blank">172.16.6.154:36486</a> TIME_WAIT<br>
> unix 2 [ ACC ] STREAM LISTENING 15867 /tmp/.s.PGSQL.9999<br>
<br>
<br>
On Fri, 8 Mar 2013 00:28:20 -0600<br>
<div class="HOEnZb"><div class="h5">ning chan <<a href="mailto:ninchan8328@gmail.com">ninchan8328@gmail.com</a>> wrote:<br>
<br>
> Hi Yugo,<br>
> Thanks for looking at the issue, here is the exact steps i did to get in to<br>
> the problem.<br>
> 1) make sure replication is setup and pgpool on both server have the<br>
> backend value set to 2<br>
> 2) shutdown postgresql on the primary, this will promote the<br>
> standby(server1) to become new primary<br>
> 3) execute pcp_recovery on server1 which will recover the failed node<br>
> (server0) and connect to the new primary (server1), check backend status<br>
> value<br>
> 4) shudown postfresql on the server1 (new Primary), this should promote<br>
> server0 to become primary again<br>
> 5) execute pcp_recovery on server0 which will recover the failed node<br>
> (server1) and connect to the new primary (server0 again), check backend<br>
> status value<br>
> 6) go to server1, shutdown pgpool, and start it up again, pgpool at the<br>
> point will not be able to start anymore, server reboot is required in order<br>
> to bring pgpool online.<br>
><br>
> I attached you the db-server0 and db-server1.log which i redirected all the<br>
> command (search for 'Issue command') I executed in above steps to the log<br>
> file as well, you should be able to follow it very easily.<br>
> I also attached you my postgresql and pgpool conf files as well as my<br>
> basebackup.sh and remote start script just in case you need them for<br>
> reproduce.<br>
><br>
> Thanks~<br>
> Ning<br>
><br>
><br>
> On Thu, Mar 7, 2013 at 6:01 AM, Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>> wrote:<br>
><br>
> > Hi ning,<br>
> ><br>
> > I tried to reproduce the bind error by repeatedly starting/stopping pgpools<br>
> > with both watchdog enabled. But I cannot see the error.<br>
> ><br>
> > Can you tell me a reliable way to to reproduce it?<br>
> ><br>
> ><br>
> > On Wed, 6 Mar 2013 11:21:01 -0600<br>
> > ning chan <<a href="mailto:ninchan8328@gmail.com">ninchan8328@gmail.com</a>> wrote:<br>
> ><br>
> > > Hi Tatsuo,<br>
> > ><br>
> > > Do you need any more data for your investigation?<br>
> > ><br>
> > > Thanks~<br>
> > > Ning<br>
> > ><br>
> > ><br>
> > > On Mon, Mar 4, 2013 at 4:08 PM, ning chan <<a href="mailto:ninchan8328@gmail.com">ninchan8328@gmail.com</a>> wrote:<br>
> > ><br>
> > > > Hi Tatsuo,<br>
> > > > I shutdown one watchdog instead of both, I can't reproduce the problem.<br>
> > > ><br>
> > > > Here is the details:<br>
> > > > server0 pgpool watchdog is disabled<br>
> > > > server1 pgpool watchdog is enabled and it is a primary database for<br>
> > > > streaming replication, failover & failback works just fine; except<br>
> > that the<br>
> > > > virtual ip will not be migrated to the other pgpool server because<br>
> > > > watchdog on server0 is not running.<br>
> > > ><br>
> > > > FYI: as i reported on the other email thread, running watchdog on both<br>
> > > > server will not allow me to failover & failback more than once which I<br>
> > am<br>
> > > > still looking for root cause.<br>
> > > ><br>
> > > > 1) both node shows pool_nodes as state 2<br>
> > > > 2) shutdown database on server1, then cause the DB to failover to<br>
> > server0,<br>
> > > > server0 is now primary<br>
> > > > 3) execute pcp_recovery on server0 to bring the server1 failed database<br>
> > > > back online and connects to server0 as a standby; however, pool_nodes<br>
> > on<br>
> > > > server1 shows the following:<br>
> > > > [root@server1 data]# psql -c "show pool_nodes" -p 9999<br>
> > > > node_id | hostname | port | status | lb_weight | role<br>
> > > > ---------+----------+------+--------+-----------+---------<br>
> > > > 0 | server0 | 5432 | 2 | 0.500000 | primary<br>
> > > > 1 | server1 | 5432 | 3 | 0.500000 | standby<br>
> > > > (2 rows)<br>
> > > ><br>
> > > > As shows, server1 pgpool think itself as in state 3.<br>
> > > > Replication however is working fine.<br>
> > > ><br>
> > > > 4) i have to execute pcp_attach_node on server1 to bring its pool_nodes<br>
> > > > state to 2, however, server0 pool_nodes info about server1 becomes 3.<br>
> > see<br>
> > > > below for both servers output:<br>
> > > > [root@server1 data]# psql -c "show pool_nodes" -p 9999<br>
> > > > node_id | hostname | port | status | lb_weight | role<br>
> > > > ---------+----------+------+--------+-----------+---------<br>
> > > > 0 | server0 | 5432 | 2 | 0.500000 | primary<br>
> > > > 1 | server1 | 5432 | 2 | 0.500000 | standby<br>
> > > ><br>
> > > > [root@server0 ~]# psql -c "show pool_nodes" -p 9999<br>
> > > > node_id | hostname | port | status | lb_weight | role<br>
> > > > ---------+----------+------+--------+-----------+---------<br>
> > > > 0 | server0 | 5432 | 2 | 0.500000 | primary<br>
> > > > 1 | server1 | 5432 | 3 | 0.500000 | standby<br>
> > > ><br>
> > > ><br>
> > > > 5) execute the following command on server1 will bring the server1<br>
> > status<br>
> > > > to 2 on both node:<br>
> > > > /usr/local/bin/pcp_attach_node 10 server0 9898 pgpool [passwd] 1<br>
> > > ><br>
> > > > [root@server1 data]# psql -c "show pool_nodes" -p 9999<br>
> > > > node_id | hostname | port | status | lb_weight | role<br>
> > > > ---------+----------+------+--------+-----------+---------<br>
> > > > 0 | server0 | 5432 | 2 | 0.500000 | primary<br>
> > > > 1 | server1 | 5432 | 2 | 0.500000 | standby<br>
> > > ><br>
> > > > [root@server0 ~]# psql -c "show pool_nodes" -p 9999<br>
> > > > node_id | hostname | port | status | lb_weight | role<br>
> > > > ---------+----------+------+--------+-----------+---------<br>
> > > > 0 | server0 | 5432 | 2 | 0.500000 | primary<br>
> > > > 1 | server1 | 5432 | 2 | 0.500000 | standby<br>
> > > ><br>
> > > > Please advise the next step.<br>
> > > ><br>
> > > > Thanks~<br>
> > > > Ning<br>
> > > ><br>
> > > ><br>
> > > > On Sun, Mar 3, 2013 at 6:03 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
> > wrote:<br>
> > > ><br>
> > > >> > Mar 1 10:45:31 server1 pgpool[3339]: bind(:) failed. reason:<br>
> > Success<br>
> > > >><br>
> > > >> This error messge seems pretty strange. ":" should be something like<br>
> > > >> "/tmp/.s.PGSQL.9898". Also it's weired because 2failed. reason:<br>
> > > >> Success". To isolate the problem, can please disable watchdog and try<br>
> > > >> again?<br>
> > > >> --<br>
> > > >> Tatsuo Ishii<br>
> > > >> SRA OSS, Inc. Japan<br>
> > > >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
> > > >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
> > > >><br>
> > > >><br>
> > > >> > Hi All,<br>
> > > >> > After upgrade to pgPool-II 3.2.3 and I tested my failover/ failback<br>
> > > >> setup,<br>
> > > >> > and start / stop pgpool mutlip times, I see one of the pgpool goes<br>
> > in<br>
> > > >> to an<br>
> > > >> > unrecoverable state.<br>
> > > >> ><br>
> > > >> > Mar 1 10:45:25 server1 pgpool[3007]: received smart shutdown<br>
> > request<br>
> > > >> > Mar 1 10:45:25 server1 pgpool[3007]: watchdog_pid: 3010<br>
> > > >> > Mar 1 10:45:31 server1 pgpool[3338]: wd_chk_sticky: ifup[/sbin/ip]<br>
> > > >> doesn't<br>
> > > >> > have sticky bit<br>
> > > >> > Mar 1 10:45:31 server1 pgpool[3339]: bind(:) failed. reason:<br>
> > Success<br>
> > > >> > Mar 1 10:45:31 server1 pgpool[3339]: unlink(/tmp/.s.PGSQL.9898)<br>
> > > >> failed: No<br>
> > > >> > such file or directory<br>
> > > >> ><br>
> > > >> ><br>
> > > >> > netstat shows the following:<br>
> > > >> > [root@server1 ~]# netstat -na |egrep "9898|9999"<br>
> > > >> > tcp 0 0 <a href="http://0.0.0.0:9898" target="_blank">0.0.0.0:9898</a> 0.0.0.0:*<br>
> > > >> > LISTEN<br>
> > > >> > tcp 0 0 <a href="http://0.0.0.0:9999" target="_blank">0.0.0.0:9999</a> 0.0.0.0:*<br>
> > > >> > LISTEN<br>
> > > >> > tcp 0 0 <a href="http://172.16.6.154:46650" target="_blank">172.16.6.154:46650</a> <a href="http://172.16.6.153:9999" target="_blank">172.16.6.153:9999</a><br>
> > > >> > TIME_WAIT<br>
> > > >> > tcp 9 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.153:51868" target="_blank">172.16.6.153:51868</a><br>
> > > >> > CLOSE_WAIT<br>
> > > >> > tcp 9 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.153:51906" target="_blank">172.16.6.153:51906</a><br>
> > > >> > CLOSE_WAIT<br>
> > > >> > tcp 0 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.154:50624" target="_blank">172.16.6.154:50624</a><br>
> > > >> > TIME_WAIT<br>
> > > >> > tcp 9 0 <a href="http://172.16.6.154:9999" target="_blank">172.16.6.154:9999</a> <a href="http://172.16.6.153:51946" target="_blank">172.16.6.153:51946</a><br>
> > > >> > CLOSE_WAIT<br>
> > > >> > unix 2 [ ACC ] STREAM LISTENING 18698<br>
> > > >> /tmp/.s.PGSQL.9898<br>
> > > >> > unix 2 [ ACC ] STREAM LISTENING 18685<br>
> > > >> /tmp/.s.PGSQL.9999<br>
> > > >> ><br>
> > > >> > Is this a known issue?<br>
> > > >> ><br>
> > > >> > I will have to reboot the server in order to start pgpool back<br>
> > online.<br>
> > > >> ><br>
> > > >> > My cluster has two servers (server0 & server1) which each of them<br>
> > are<br>
> > > >> > running pgpool, and postgreSQL with streaming Replication setup.<br>
> > > >> ><br>
> > > >> > Thanks~<br>
> > > >> > Ning<br>
> > > >><br>
> > > ><br>
> > > ><br>
> ><br>
> ><br>
> > --<br>
> > Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> ><br>
<br>
<br>
</div></div><span class="HOEnZb"><font color="#888888">--<br>
Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
</font></span></blockquote></div><br></div>