Thread: Proxy for postgres
Hi list! I'm searching for something to use as a proxy for different instances of postgres. I've found pl/proxy, but it seems to work with (correct me if i'm wrong) functions, and some hard work would be needed in our applications in order to make them use those functions replacing direct access, an I'm trying to avoid this. Another option is pgpool, but I see too much lock in tables at transactional queries, and I'm pretty sure this will make my life a lot harder. So the question is, what can I use as a proxy queries to different instances of postgres? Cheers Martin
> I'm searching for something to use as a proxy for different instances of
> postgres.
>
> I've found pl/proxy, but it seems to work with (correct me if i'm wrong)
> functions, and some hard work would be needed in our applications in
> order to make them use those functions replacing direct access, an I'm
> trying to avoid this.
>
> Another option is pgpool, but I see too much lock in tables at
> transactional queries, and I'm pretty sure this will make my life a lot
> harder.
>
> So the question is, what can I use as a proxy queries to different
> instances of postgres?
>
You can use dblink. But could interference the performance
and is not really what you want.
--
Emanuel Calvo Franco
Database consultant at:
www.siu.edu.ar
www.emanuelcalvofranco.com.ar
Have you tried sql relay? They support may platforms: http://sqlrelay.sourceforge.net/ -Said Martin Spinassi wrote: > Hi list! > > > I'm searching for something to use as a proxy for different instances of > postgres. > > I've found pl/proxy, but it seems to work with (correct me if i'm wrong) > functions, and some hard work would be needed in our applications in > order to make them use those functions replacing direct access, an I'm > trying to avoid this. > > Another option is pgpool, but I see too much lock in tables at > transactional queries, and I'm pretty sure this will make my life a lot > harder. > > So the question is, what can I use as a proxy queries to different > instances of postgres? > > > Cheers > > > Martin > >
Martin Spinassi wrote: > I'm searching for something to use as a proxy for different instances of > postgres. > ... > So the question is, what can I use as a proxy queries to different > instances of postgres? > perhaps if you better defined what you mean by 'proxy queries'... a 'proxy' is simply something authorized to act for another, and can mean many different things in computing. if your app needs to query different databases, it can simply open different connections to each of them. if your app needs to execute queries that join data stored in different databases, then there's really no solution that performs very well.
On Fri, 2009-08-14 at 10:03 -0700, John R Pierce wrote: > Martin Spinassi wrote: > > I'm searching for something to use as a proxy for different instances of > > postgres. > > ... > > So the question is, what can I use as a proxy queries to different > > instances of postgres? > > > > perhaps if you better defined what you mean by 'proxy queries'... a > 'proxy' is simply something authorized to act for another, and can mean > many different things in computing. > > if your app needs to query different databases, it can simply open > different connections to each of them. > > if your app needs to execute queries that join data stored in different > databases, then there's really no solution that performs very well. > John, Basically, what I'm searching for is something that relay sql to others postgres instances. The main goal is to have this proxy balancing, and better if it can figure out when a postgres server is down. It would give me better performance and will help mitigating the single point of failure, as I can use HA for the proxy only. The final idea is big, but I wanted to know what are the tools available, end then start with it. Cheers Martin
> Basically, what I'm searching for is something that relay sql to others > postgres instances. > > The main goal is to have this proxy balancing, and better if it can > figure out when a postgres server is down. It would give me better > performance and will help mitigating the single point of failure, as I > can use HA for the proxy only. > > The final idea is big, but I wanted to know what are the tools > available, end then start with it. > if these databases are all mirrors of each other, how do you plan on handling transactional updates?
On Fri, 2009-08-14 at 11:04 -0700, John R Pierce wrote:
> Martin Spinassi wrote:
> > I've been playing around with slony, and as far it didn't make me jump
> > of happiness, I think is my better option to use slony for that,
> > updating just the master, let slony do his job, but allowing the proxy
> > to read from all. Anyway, I'm open to suggestions.
> >
>
> them, yeah, something like pg_pool2 to handle the reads.
So, what you suggest is:
|---------> Master <---|
pg_pool2 ---| |----- Slony
|---------> Slave <---|
Can't figure out if this is gonna work, configuring pg_pool2 to read
both, making slony replicate, and app directly to Master (or pg_pool2
can be configured to write just in one of both?).
Cheers
Martin
Martin Spinassi wrote: > So, what you suggest is: > > |---------> Master <---| > pg_pool2 ---| |----- Slony > |---------> Slave <---| > > > Can't figure out if this is gonna work, configuring pg_pool2 to read > both, making slony replicate, and app directly to Master (or pg_pool2 > can be configured to write just in one of both?). > something like that. see the pgpool-II tutorial at http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html note it also can do replication, but it does it at the front end rather than the back end like slony. each has its advantages and disadvantages. (I've also heard, repeatedly, that pgpool-II's replication isn't that great)
On Fri, 2009-08-14 at 11:36 -0700, John R Pierce wrote: > Martin Spinassi wrote: > > So, what you suggest is: > > > > |---------> Master <---| > > pg_pool2 ---| |----- Slony > > |---------> Slave <---| > > > > > > Can't figure out if this is gonna work, configuring pg_pool2 to read > > both, making slony replicate, and app directly to Master (or pg_pool2 > > can be configured to write just in one of both?). > > > > something like that. > > see the pgpool-II tutorial at > http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html > > note it also can do replication, but it does it at the front end rather > than the back end like slony. each has its advantages and disadvantages. > (I've also heard, repeatedly, that pgpool-II's replication isn't that great) > Thanks John. I'll try sqlrelay as mentioned by sramirez (sorry, no sign at the email), and pg_pool2 as you suggest. Couldn't find any good documentation of dblink, but think I can read the code if the first two options doesn't work as expected. Once again, thanks @all for you answers! Cheers Martin