Proxy for postgres

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:

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