<div dir="ltr"><div><div><div><div><div><div><div><div><div><div><div><div><div>it's done now.<br><br></div>My problem now is that i am in a prod ambient... so i can play very much...<br><br></div>i have:<br></div>node1, primary pgpool (with virtual ip) and primary database<br>
</div>node2, standby node (database and pgpool)<br><br></div><div>i add a node (2nd standby in standby server) /datastb in pgpool.conf (bouth nodes)<br></div><div><br></div>from node1:<br># pcp_node_info 10 localhost 9898 postgres postgres 0<br>
prod 5432 <b>3</b> 1.000000<br># pcp_node_info 10 localhost 9898 postgres postgres 1<br>replica 5432 <b>1</b> 0.000000<br># pcp_node_info 10 localhost 9898 postgres postgres 2<br>replica 5444 3 0.000000<br><br></div>why node 0 (local) is donw!?<br>
<br></div><div>it is not. i can connect directly (psql) or via pgpool (psql -h virtual_ip)<br><br></div><div>I can't recovery the node either.<br><br># pcp_recovery_node 10 localhost 9898 postgres postgres 2<br>BackendError</div>
<br><br>in pgpool.log:<br><br>Mar 11 11:39:30 postgresql pgpool[14723]: starting recovering node 2<br>Mar 11 11:39:30 postgresql pgpool[14723]: starting recovery command: "SELECT pgpool_recovery('basebackup.sh', 'replica', '/var/lib/pgsql/9.0/datastb')"<br>
Mar 11 11:39:30 postgresql pgpool[14723]: exec_recovery: basebackup.sh command failed at 1st stage <br><br><br></div>in the standby node,<br><br># pcp_node_info 10 localhost 9898 postgres postgres 0<br>prod 5432 <b>1</b> 1.000000<br>
# pcp_node_info 10 localhost 9898 postgres postgres 1<br>replica 5432<b> 1</b> 0.000000<br># pcp_node_info 10 localhost 9898 postgres postgres 2<br>replica 5444 3 0.000000<br><br># pcp_recovery_node 10 localhost 9898 postgres postgres 2<br>
<br></div>and the recovery starts ....<br></div>but... database is quite big, so i see <br> new structure in standby server /datastb and subdirectories<br></div> rsync command in master node...<br><br></div>
any clue why pcp commands are not working in mster node!!?<br><br><br><br></div>tks!<br><div><div><div><div><br><div><br></div></div></div></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Feb 19, 2014 at 7:50 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"><div class="">On Fri, 14 Feb 2014 18:39:10 -0200<br>
Gonzalo Gil <<a href="mailto:gonxalo2000@gmail.com">gonxalo2000@gmail.com</a>> wrote:<br>
<br>
> I changed it and works fine.<br>
> just one more thing.<br>
> when one node gets down (pgpool process, not database), it takes one minute<br>
> and a half to the other to make itself primary...<br>
> i change some parameters but it still takes 1,5 minutes to set maste pgpool<br>
> node<br>
<br>
</div>It is possible depending on parameter configuration, but I can't identify the cause.<br>
Could you please send your pgpool.conf and logs?<br>
<div class="HOEnZb"><div class="h5"><br>
><br>
><br>
> is it possible?<br>
> how so?<br>
><br>
> tanks again<br>
><br>
><br>
> On Thu, Feb 13, 2014 at 11:26 AM, Gonzalo Gil <<a href="mailto:gonxalo2000@gmail.com">gonxalo2000@gmail.com</a>> wrote:<br>
><br>
> > Great!<br>
> ><br>
> ><br>
> > On Thu, Feb 13, 2014 at 12:23 PM, Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>> wrote:<br>
> ><br>
> >> On Thu, 13 Feb 2014 11:59:20 -0200<br>
> >> Gonzalo Gil <<a href="mailto:gonxalo2000@gmail.com">gonxalo2000@gmail.com</a>> wrote:<br>
> >><br>
> >> > YES! it works!<br>
> >><br>
> >> I'm glad to hear that.<br>
> >><br>
> >> ><br>
> >> > i will install heartbear.... but i'm testing instalation and i take the<br>
> >> > easy way...<br>
> >> > i let you know when i got it running<br>
> >><br>
> >> You don't need to install heartbeat (Pacemaker). Watchdog's heartbeat<br>
> >> mode is<br>
> >> pgpool-II's built-in functionality. For the most simple configuration,<br>
> >> what<br>
> >> you need to do is:<br>
> >><br>
> >> wd_lifecheck_method = 'heartbeat'<br>
> >><br>
> >> wd_heartbeat_port = 9694<br>
> >> wd_heartbeat_keepalive = 2<br>
> >> wd_heartbeat_deadtime = 30<br>
> >><br>
> >> heartbeat_destination0 = 'tad2' <= 'tad1' in tad2 server<br>
> >> heartbeat_destination_port0 = 9694<br>
> >> heartbeat_device0 = ''<br>
> >><br>
> >><br>
> >> ><br>
> >> > tks a lot!!<br>
> >> ><br>
> >> ><br>
> >> > On Thu, Feb 13, 2014 at 8:47 AM, Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> >> wrote:<br>
> >> ><br>
> >> > > Hi,<br>
> >> > ><br>
> >> > > On Wed, 12 Feb 2014 12:05:56 -0200<br>
> >> > > Gonzalo Gil <<a href="mailto:gonxalo2000@gmail.com">gonxalo2000@gmail.com</a>> wrote:<br>
> >> > ><br>
> >> > > > i think it does not work...<br>
> >> > ><br>
> >> > > I'm sorry for jumping to a wring conclusion. load_balance_mode is<br>
> >> > > irrelevant.<br>
> >> > > The problem is that, pgpool-II considers myself as down before<br>
> >> failover is<br>
> >> > > done completely. Before failover completed, pgpool-II's child process<br>
> >> > > doesn't<br>
> >> > > know the backend server is down, hence lifecheck query 'SELECT 1'<br>
> >> fails,<br>
> >> > > and<br>
> >> > > pgpool-II consider itself in down status.<br>
> >> > ><br>
> >> > > To avoid this, health check should be done more frequently, or,<br>
> >> lifecheck<br>
> >> > > interval should be larger. In your configuration,<br>
> >> health_check_max_retries<br>
> >> > > = 3<br>
> >> > > and helth_check_retry_delay = 10. So, it takes more than 30 seconds to<br>
> >> > > detect<br>
> >> > > backend DB's down and start failover. However, wd_interval = 5 and<br>
> >> > > wd_life_point = 3.<br>
> >> > > So, it is about 15 to 20 seconds before pgpool-II decide to go to down<br>
> >> > > status.<br>
> >> > ><br>
> >> > > Could you please try edit pgpool.conf? For example:<br>
> >> > ><br>
> >> > > health_check_max_retries = 2<br>
> >> > > health_check_retry_delay = 5<br>
> >> > > wd_interval = 10<br>
> >> > > wd_life_point = 3;<br>
> >> > ><br>
> >> > > In fact, I recommend to use heartbeat mode instead of query mode.<br>
> >> This mode<br>
> >> > > doesn't issue query like 'SELECT 1' for checking pgpool status. So,<br>
> >> this<br>
> >> > > avoids<br>
> >> > > the kind of problem.<br>
> >> > ><br>
> >> > > ><br>
> >> > > ><br>
> >> > > > <a href="http://172.16.62.141/status.php" target="_blank">http://172.16.62.141/status.php</a><br>
> >> > > > IP Address Port Status Weight<br>
> >> > > ><br>
> >> > > > node 0 tad1 5432 Up. Connected. Running as<br>
> >> > > primary<br>
> >> > > > server postgres: Up 0.500 |<br>
> >> > > > node 1 tad2 5432 Up. Connected. Running as<br>
> >> > > standby<br>
> >> > > > server postgres: Up 0.500 |<br>
> >> > > ><br>
> >> > > > <a href="http://172.16.62.142/status.php" target="_blank">http://172.16.62.142/status.php</a><br>
> >> > > > IP Address Port Status Weight<br>
> >> > > ><br>
> >> > > > node 0 tad1 5432 Up. Connected. Running as<br>
> >> > > primary<br>
> >> > > > server postgres: Up 0.500 |<br>
> >> > > > node 1 tad2 5432 Up. Connected. Running as<br>
> >> > > standby<br>
> >> > > > server postgres: Up 0.500 |<br>
> >> > > ><br>
> >> > > > shutdown 141, node0, tad1...<br>
> >> > > ><br>
> >> > > ><br>
> >> > > ><br>
> >> > > > i attach logs....<br>
> >> > > ><br>
> >> > > ><br>
> >> > > > this was the final result....<br>
> >> > > > ---><br>
> >> > > > IP Address Port Status Weight<br>
> >> > > ><br>
> >> > > > node 0 tad1 5432 Down postgres: Down<br>
> >> > > > 0.500 |<br>
> >> > > > node 1 tad2 5432 Up. Connected. Running as<br>
> >> > > standby<br>
> >> > > > server postgres: Up 0.500 |<br>
> >> > > > <---<br>
> >> > > ><br>
> >> > > ><br>
> >> > > ><br>
> >> > > > On Wed, Feb 12, 2014 at 4:11 AM, Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> >> > > wrote:<br>
> >> > > ><br>
> >> > > > > Hi,<br>
> >> > > > ><br>
> >> > > > > Thanks for sending confs & logs.<br>
> >> > > > ><br>
> >> > > > > I found that this problem occurs when load_balance_mode = off.<br>
> >> > > > > Could you please try with load_balance_mode = on?<br>
> >> > > > ><br>
> >> > > > > I'll continue to analyze the detailed reason.<br>
> >> > > > ><br>
> >> > > > > On Mon, 10 Feb 2014 11:40:41 -0200<br>
> >> > > > > Gonzalo Gil <<a href="mailto:gonxalo2000@gmail.com">gonxalo2000@gmail.com</a>> wrote:<br>
> >> > > > ><br>
> >> > > > > > i send the message but it was too long.<br>
> >> > > > > > i'll attach the files....<br>
> >> > > > > ><br>
> >> > > > > > it happens again, even when node 2 was the postgres standby<br>
> >> node.<br>
> >> > > > > ><br>
> >> > > > > > after i put the logs here, i shutdown node 1 (it has the primary<br>
> >> > > > > database)<br>
> >> > > > > > and it happens the same thing. node 2 lost ip and no failover<br>
> >> > > happens.<br>
> >> > > > > ><br>
> >> > > > > ><br>
> >> > > > > > TKS!<br>
> >> > > > > ><br>
> >> > > > > ><br>
> >> > > > > ><br>
> >> > > > > ><br>
> >> > > > > > On Mon, Feb 10, 2014 at 5:23 AM, Yugo Nagata <<br>
> >> <a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> >> > > > > wrote:<br>
> >> > > > > ><br>
> >> > > > > > > Hi,<br>
> >> > > > > > ><br>
> >> > > > > > > This is odd that pgpool-1 losts VIP when server2 goes down.<br>
> >> For<br>
> >> > > > > analysis,<br>
> >> > > > > > > could you please send pgpool.conf and log output (of both<br>
> >> pgpool1<br>
> >> > > and<br>
> >> > > > > > > pgpool2)?<br>
> >> > > > > > ><br>
> >> > > > > > > On Tue, 4 Feb 2014 13:38:16 -0200<br>
> >> > > > > > > Gonzalo Gil <<a href="mailto:gonxalo2000@gmail.com">gonxalo2000@gmail.com</a>> wrote:<br>
> >> > > > > > ><br>
> >> > > > > > > > Hello Tatsuo Ishii. I send some query mails to<br>
> >> > > > > > > > pgpool-general@pgpool.netbut i don't get my own messagese.<br>
> >> But<br>
> >> > > i do<br>
> >> > > > > > > > recieve other mails from the<br>
> >> > > > > > > > forum.<br>
> >> > > > > > > ><br>
> >> > > > > > > > Can you answer me some questions or forward them to the<br>
> >> forum!?<br>
> >> > > > > > > ><br>
> >> > > > > > > ><br>
> >> > > > > > > > I'm runing pgpool with streaming replication: pgpool1 - db<br>
> >> > > postgres1<br>
> >> > > > > > > > (server 1) and pgpool2 - db postgres 2 (server 2).<br>
> >> > > > > > > > I'm using watchdog with a virtual ip and life_check_query.<br>
> >> > > > > > > ><br>
> >> > > > > > > > It's all configured and working .... more or less....<br>
> >> > > > > > > ><br>
> >> > > > > > > > INIT: I start my system: postgres1 is standby database and<br>
> >> > > postgres2<br>
> >> > > > > > > > is master (streaming replication).<br>
> >> > > > > > > > pgpool1 has the virtual ip.(and pgpool2 no, obviously)<br>
> >> > > > > > > ><br>
> >> > > > > > > > i connect to database via pgpool and everithing is ok.<br>
> >> > > > > > > > i stop postgres1 and nothing happens because i check<br>
> >> new_master<br>
> >> > > <><br>
> >> > > > > > > > old_master (no master failure).<br>
> >> > > > > > > > i start postgres1 again (and returning it with pgpoolAdmin)<br>
> >> or<br>
> >> > > call a<br>
> >> > > > > > > > recovery and it works great.<br>
> >> > > > > > > ><br>
> >> > > > > > > > I stop postgres2 and failover fires ... and i get postgres1<br>
> >> as<br>
> >> > > the<br>
> >> > > > > new<br>
> >> > > > > > > > primary.<br>
> >> > > > > > > > and so on...<br>
> >> > > > > > > ><br>
> >> > > > > > > > this works fine.<br>
> >> > > > > > > ><br>
> >> > > > > > > ><br>
> >> > > > > > > > i go back to INIT again....<br>
> >> > > > > > > > and i do in server2<br>
> >> > > > > > > > reboot -h now<br>
> >> > > > > > > ><br>
> >> > > > > > > > i see in the server1 (pgpool1) log that pgpool2 is down...ok<br>
> >> > > > > > > > watching the log, i see pgpool1 lost the virtual ip address<br>
> >> > > > > (!?)....and<br>
> >> > > > > > > > tell me to restart pgpool....(!?)<br>
> >> > > > > > > ><br>
> >> > > > > > > > i restart it and i see that failover fires ... but in the<br>
> >> > > failover<br>
> >> > > > > > > script i<br>
> >> > > > > > > > get new_master_node = old_master_node ...and thus i do not<br>
> >> make<br>
> >> > > > > touch and<br>
> >> > > > > > > > postgres1 keeps as a standby...<br>
> >> > > > > > > ><br>
> >> > > > > > > ><br>
> >> > > > > > > > I change failover.sh (and the command in the pgpool.conf). i<br>
> >> > > include<br>
> >> > > > > all<br>
> >> > > > > > > > parameters to see it's values when failover.sh start....<br>
> >> > > > > > > ><br>
> >> > > > > > > > Then, i restart serve2 and "return" database to pgpool....<br>
> >> > > > > > > ><br>
> >> > > > > > > > again, pgpool1 has the virtual ip.<br>
> >> > > > > > > > i stop database in node 2 and failover fires.... but<br>
> >> pgpool2 does<br>
> >> > > > > > > it....and<br>
> >> > > > > > > > pgpool1 too (!?)<br>
> >> > > > > > > > i check network activity and saw that pgpool2 connects to<br>
> >> > > server1 and<br>
> >> > > > > > > make<br>
> >> > > > > > > > the touch and i did see log from pgpool1 firing the failover<br>
> >> > > command<br>
> >> > > > > > > too....<br>
> >> > > > > > > ><br>
> >> > > > > > > ><br>
> >> > > > > > > ><br>
> >> > > > > > > > Cuestions....<br>
> >> > > > > > > > 1. why pgpool1 lost virtual ip and ask me to restart!?<br>
> >> > > > > > > > 2. why pgpool2 fires failover? i thought just the "primary"<br>
> >> > > pgpool<br>
> >> > > > > (the<br>
> >> > > > > > > one<br>
> >> > > > > > > > with the virtual ip) fires it.<br>
> >> > > > > > > ><br>
> >> > > > > > > ><br>
> >> > > > > > > > i hope you understand mr.<br>
> >> > > > > > > > tks a lot for your time..<br>
> >> > > > > > > > sorry for my english.<br>
> >> > > > > > ><br>
> >> > > > > > ><br>
> >> > > > > > > --<br>
> >> > > > > > > Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> >> > > > > > ><br>
> >> > > > ><br>
> >> > > > ><br>
> >> > > > > --<br>
> >> > > > > Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> >> > > > ><br>
> >> > ><br>
> >> > ><br>
> >> > > --<br>
> >> > > Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> >> > ><br>
> >><br>
> >><br>
> >> --<br>
> >> Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> >><br>
> ><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>