Thread: libpq connectoin redirect
Hi,<br /><br />I want to load balance a postgres server on 4 physical machines, say 127.0.0.11-14. I can set up a pgbounceron 127.0.0.10 and connection pooling to my four boxes. However, the traffic from/to clients will go through anextra hop. Another way to do this, is to send the client an "redirect" message. When client connect to 127.0.0.10, insteadof accepting the connection, it can reply to client telling it to reconnect to one of the server on 127.0.0.11-14. <br /><br />I am planning to write/submit a patch to do that. I wonder if there is similar effort in extendinglibpq protocol, or, if you have better ideas on how to achieve this.<br /><br />Thank you,<br />Feng<br /><br /><br/><hr />The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. <a href="http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5" target="_new">Getbusy.</a>
feng tian wrote: > Hi, > > I want to load balance a postgres server on 4 physical machines, say > 127.0.0.11-14. I can set up a pgbouncer on 127.0.0.10 and connection > pooling to my four boxes. However, the traffic from/to clients will > go through an extra hop. Another way to do this, is to send the > client an "redirect" message. When client connect to 127.0.0.10, > instead of accepting the connection, it can reply to client telling it > to reconnect to one of the server on 127.0.0.11-14. > > I am planning to write/submit a patch to do that. I wonder if there > is similar effort in extending libpq protocol, or, if you have better > ideas on how to achieve this. how do you plan on maintaining consistency, transactional integrity and atomicity of updates across these 4 machines?
On Apr 20, 2010, at 10:03 PM, feng tian wrote: > Another way to do this, is to send the client an "redirect" message. When client connect to 127.0.0.10, instead of acceptingthe connection, it can reply to client telling it to reconnect to one of the server on 127.0.0.11-14. ISTM that this would be better handled at a higher-level. That is, given a server (127.0.0.10) that holds 127.0.0.11-14.Connect to that server and query for the correct target host.
feng tian wrote: > Hi, > > I want to load balance a postgres server on 4 physical machines, say 127.0.0.11-14. I can set up a pgbouncer on 127.0.0.10and connection pooling to my four boxes. However, the traffic from/to clients will go through an extra hop. Anotherway to do this, is to send the client an "redirect" message. When client connect to 127.0.0.10, instead of acceptingthe connection, it can reply to client telling it to reconnect to one of the server on 127.0.0.11-14. One common way to do that is to set up one DNS entry for those 4 IP addresses. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
While these can be handled at higher level, for example, by setting <br />up LDAP or as Hekki suggested, tricking DNS, theproblem is that <br />I don't have control of how the user connect to the server. They <br />may not use LDAP. Solutionlike pgbouncer has advantages. User<br />just get one ip/port and everything else happens automatically.<br /><br/>Thanks,<br /><br /><br />> Subject: Re: [HACKERS] libpq connectoin redirect<br />> From: lists@jwp.name<br />>Date: Wed, 21 Apr 2010 15:52:39 -0700<br />> CC: pgsql-hackers@postgresql.org<br />> To: ft_uw@hotmail.com<br/>> <br />> On Apr 20, 2010, at 10:03 PM, feng tian wrote:<br />> > Another way to do this,is to send the client an "redirect" message. When client connect to 127.0.0.10, instead of accepting the connection,it can reply to client telling it to reconnect to one of the server on 127.0.0.11-14. <br />> <br />> ISTMthat this would be better handled at a higher-level. That is, given a server (127.0.0.10) that holds 127.0.0.11-14. Connectto that server and query for the correct target host.<br /><br /><hr />Hotmail is redefining busy with tools for theNew Busy. Get more from your inbox. <a href="http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2" target="_new">Seehow.</a>
<pre style="margin: 0em;">Hi, John,<br /><br />The change will be on the libpq client side. I am not saying this is a general<br/>solution for the distributed transaction/scale out. However, in many cases, it<br />is very useful. For example,in my case, I have about 100 departments each has <br />it own database. The balance machine can just redirect tothe right box according<br />to database/user. The 4 boxes I have may not even get domain name or static IP.<br />Anotherscenario, if I have some kind of replication set up, I can send transaction<br />processing to master and analyticreporting query to slaves. <br /><br />Thanks,<br />Feng<br /><br />feng tian wrote:<br /></pre><blockquote style="border-left:0.2em solid rgb(85, 85, 238); margin: 0em; padding-left: 0.85em;"><pre style="margin: 0em;">Hi,<br /><br/></pre><tt>I want to load balance a postgres server on 4 physical machines, say </tt><tt>127.0.0.11-14. I can set upa pgbouncer on 127.0.0.10 and connection </tt><tt>pooling to my four boxes. However, the traffic from/to clients will </tt><tt>gothrough an extra hop. Another way to do this, is to send the </tt><tt>client an "redirect" message. When clientconnect to 127.0.0.10, </tt><tt>instead of accepting the connection, it can reply to client telling it </tt><tt>toreconnect to one of the server on 127.0.0.11-14. </tt><tt> </tt><tt>I am planning to write/submit a patch to dothat. I wonder if there </tt><tt>is similar effort in extending libpq protocol, or, if you have better </tt><tt>ideas onhow to achieve this. </tt></blockquote><tt><br />how do you plan on maintaining consistency, transactional integrity and</tt><tt>atomicity of updates across these 4 machines? </tt><br /><hr />The New Busy is not the too busy. Combine allyour e-mail accounts with Hotmail. <a href="http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4" target="_new">Getbusy.</a>