<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Lou,<div> I have thought about going down this path a few times... but I keep staying away from it because of the KISS method. Its too damn complicated, too likely to fail at the worst possible time. As it is, I have yet to find a doc that clearly explains exactly how and when all of the *_command options in the config file for PGPool get run, and with what arguments. I've been working on our PGPool auto-failover scripts for weeks, and still do not feel confident enough to turn them on in production.</div><div><br></div><div> I strongly believe that PGPool needs a "pure load balancing" mode.. one where it pays attention to which server is the master, disables traffic to downed (or behind-in-replication) slaves, but does not interfere with any of the operations that a database/Ops team may be doing on the servers themselves. There are too many cases where we do not want auto-failover yet, and its simply not controllable in PGPool.</div><div><br></div><div> As an example ... if I issue a "restart" to Postgres, I do not want PGPool to initiate a failover! A 5 second outage while I restart the master is FAR better than the much longer outage of re-coordinating and syncing our slaves!</div><div><br></div><div> Another example is the one you have been discussing, where we want more than 1 PGPool server. A single PGPool server is too much of a single point of failure for us — so we have two. The second one needs to be "dumb" and just bounce traffic around. Nothing else.</div><div><br></div><div>—Matt</div><div><br></div><div><div><div>On Apr 20, 2012, at 6:41 AM, Lou Kamenov wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div bgcolor="#FFFFFF"><div>Hey there, </div><div><br></div><div>My plan was to ideally put two of pgpool instances under a stateless load balancer, the problem with the failover on which pgpool I believe can be handled by a well crafted pgpool failover command by introducing some synchronization mechanism through it. </div>
<div><br></div><div>Eg pgpool1 detects the failing master and runs the cmd, before that it flocks a file on the next backed which and touches the trigger file, after this happens it touches another file say 'compled-failover' containing the name of the new backed. </div>
<div><br></div><div>Now the second pgpool detects the failing master and executes the failover command but is waiting on the lock to be released after which it checks the existence of the completed-failover file and makes a decision if it should touch the trigger or not. </div>
<div><br></div><div>It's a bit clunky and it doesn't handle a scenario of a cascading failure where backend1 could fail as well. </div><div><br></div><div>Possibly the solution could be just to keep a standby pgpool server and switch ips between with the failing pgpool. </div>
<div><br></div><div>I'm just throwing ideas right now. </div><div><br></div><div>Thanks</div><div>Lou</div><div><br><div><br></div></div><div><br>On 2012-04-20, at 9:10, Matt Wise <<a href="mailto:matt@nextdoor.com">matt@nextdoor.com</a>> wrote:<br>
<br></div><div></div><blockquote type="cite"><div><base href="x-msg://8062/">Ludwig,<div> The problem with that (and that was our original idea) DISALLOW does not function in the way we want. When DISALLOW is set, if any server in your pool of 4 DB servers goes down, access to all of them is hung. Worse yet, if the master changes from say DB1 to DB2 (and you bring DB1 back up as a slave of DB2), the non-controlling PGPool in DISALLOW mode will never notice.</div>
<div><br></div><div>—Matt</div><div><br><div><div>On Apr 19, 2012, at 10:48 PM, Ludwig Adam wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><span class="Apple-style-span" style="border-collapse:separate;font-family:Helvetica;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:-webkit-auto;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;font-size:medium"><div>
<div style="font-family:Verdana,Arial,Helvetica,sans-serif;font-size:small;color:black"><span style="font-family:Verdana,Arial,Helvetica,sans-serif;font-size:small;color:black">Dear Lou, why would you use multiple pgpool instances to control failover?<br>
<br>Perhaps it would be a solution to have one instance of pgpool to set to FAILOVER and the others to DISALLOW...?<span class="Apple-converted-space"> </span><br><br>Ludwig<span class="Apple-converted-space"> </span><br>
<br>Mobil gesendet.<br><br><span style="color:black">-----Original Message-----<span class="Apple-converted-space"> </span><br><b>From:</b><span class="Apple-converted-space"> </span>Lou Kamenov [<a href="mailto:kamenovl@defx.org">kamenovl@defx.org</a>]<br>
<b>Received:</b><span class="Apple-converted-space"> </span>Freitag, 20 Apr. 2012, 3:54<br><b>To:</b><span class="Apple-converted-space"> </span>Matt Wise [<a href="mailto:matt@nextdoor.com">matt@nextdoor.com</a>]<br><b>CC:</b><span class="Apple-converted-space"> </span><a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><span class="Apple-converted-space"> </span>[<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>]<br>
<b>Subject:</b><span class="Apple-converted-space"> </span>[pgpool-general: 360] Re: Multiple pgpool servers failover<br><br></span></span></div><font><span style="font-size:10pt"><div class="PlainText">On Thu, Apr 19, 2012 at 9:32 PM, Matt Wise <<a href="mailto:matt@nextdoor.com">matt@nextdoor.com</a>> wrote:<br>
[..]<br>> If DISALLOW_TO_FAILOVER is set and ..<br>> a) one of the slaves fails: that slave is taken out of rotation until its<br>> back up<br>> b) the master fails: all connections hang until the master is back, OR a<br>
> new master is detected. pgpool goes into a loop looking for new masters.<br><br>I was thinking more about section B, my problem is essentially an<br>atomic fail-over,<br>where we are ensured that this is triggered only once.<br>
<br>I will give it a shot and post back my findings.<br><br>If anyone else has any ideas, please send them over ;)<br><br>cheers,<br>Lou<br>_______________________________________________<br>pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br><a href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br></div></span></font></div>
</span></blockquote></div><br></div></div></blockquote></div>
</blockquote></div><br></div></body></html>