Thread: Connection pooling for differing databases?

Il 07/03/2019 20:27, Arjun Ranade ha scritto:
> 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.
Yes, I think there are many, but I'm encouraging you to take a look at 
pgbouncer

https://pgbouncer.github.io/

in pgbouncer.ini you enter database configuration values like

database = host=hostname port=xyzk, like
mydb1 = host=cluster1 port=6543 or
mydb2 = host=cluster1 port=9876
mydb3 = host=cluster2 port=6543

but there many other parameters to refine your config (like "proxying" 
database names, so if you share names across clusters you can easily 
avoid conflicts)

Pgbouncer should be installed on the same server as the databases or in 
another and listens on a different port than Postgres' (say 5431 while 
postgres is on 5432)
I'm actively using in my environment with 2 clusters and about 500 
databases, works flawlessly.

One thing you have to consider, if under heavy workload (say 100's of 
connections) is to raise kernel value of maximum open files

Cheers

Moreno.-

Arjun Ranade <ranade@nodalexchange.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.consulting@comcast.net