Re: Connection pooling for differing databases?

Arjun Ranade <ranade(at)nodalexchange(dot)com> writes:

> Hi all,
>
> I'm wondering if there's a tool like pgpool that can provide a single
> origin point (host/port) that will proxy/direct connections to the
> specific servers that contain the db needing to be accessed.
>
> For example... lets say we had two databases: db1.company.com:5432
> and db2.company.com:5433
>
> Db1 has the database: env1
> Db2 has the database: env2
>
> Is there a tool that will accept connections, so that when users
> connect they see there are two databases they can go to: env1 and
> env2. 

No, not at least the "they can see 2 DBs" portion of your req.

PgBouncer can route traffic on behalf of multiple server/DBs by
configuration but AFAIK even administrator access to the special
endpoint 'pgbouncer' does *not* list all possible DBs unless they are in
use and/or recently enough used to be still shown by 'show databases'.

Direct consultation of the INI file would be required... ergo, there is
nothing equivalent to psql -l.

Disclaimer: I do *not* have recent experience with PgPool as-if to
weigh-in there, but likely someone else will.

HTH

>
> If they choose to connect to the env1 db, it routes all traffic to
> db1.company.com:5432 and if they choose env2 it routes them to
> db2.company.com:5433
>
> Of course there would have to be some requirement such as the
> databases on any given server cannot have name collisions with
> database names on another server, etc.  Is there a way to do
> something like this?
>
> Thanks,
> Arjun
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net