Connection pooling for differing databases?

Em qui, 7 de mar de 2019 às 16:10, Arjun Ranade <ranade@nodalexchange.com>
escreveu:

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.

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?

Yeap, pgbouncer do that. See "databases" configuration section [1]https://pgbouncer.github.io/config.html#section-databases.

Regards,

[1]: https://pgbouncer.github.io/config.html#section-databases

--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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

I'm looking at pgbouncer and it does most of what I need. I'm wondering
about clients connecting via pgadmin, is there a way for users using
pgadmin or another tool to see all the databases that are part of the
configs?
Thanks,
Arjun

On Thu, Mar 7, 2019 at 2:39 PM Moreno Andreo <moreno.andreo@evolu-s.it>
wrote:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Il 07/03/2019 21:19, Arjun Ranade ha
scritto:<br>
</div>
<blockquote type="cite"
cite="mid:CANrrCRzHGZ8+Fq0erKhEfNyZ7y_g7FDmRvU_FrSpVt1RGnPaew@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<div>I'm looking at pgbouncer and it does most of what I need. 
I'm wondering about clients connecting via pgadmin, is there a
way for users using pgadmin or another tool to see all the
databases that are part of the configs?</div>
</div>
</blockquote>
<p>It's an issue I ran into when I set up my pgbouncer architecture,
but since all servers are reachable by the same private network
pgAdmin host is, there's no security issue in connecting directly
to them, instead of passing through pgbouncer, so I did not spend
time (that I hadn't :-)) in investigating.</p>
<p>If you resolve this (or someone has already done so), sharing the
solution would be much appreciated.</p>
<p>Cheers,</p>
<p>Moreno.-<br>
</p>
<blockquote type="cite"
cite="mid:CANrrCRzHGZ8+Fq0erKhEfNyZ7y_g7FDmRvU_FrSpVt1RGnPaew@mail.gmail.com">
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Thu, Mar 7, 2019 at 2:39 PM
Moreno Andreo &lt;<a href="mailto:moreno.andreo@evolu-s.it"
moz-do-not-send="true">moreno.andreo@evolu-s.it</a>&gt;
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Il
07/03/2019 20:27, Arjun Ranade ha scritto:<br>
&gt; Hi all,<br>
&gt;<br>
&gt; I'm wondering if there's a tool like pgpool that can
provide a single <br>
&gt; origin point (host/port) that will proxy/direct
connections to the <br>
&gt; specific servers that contain the db needing to be
accessed.<br>
Yes, I think there are many, but I'm encouraging you to take a
look at <br>
pgbouncer<br>
<br>
<a href="https://pgbouncer.github.io/&quot; rel="noreferrer"
target="_blank" moz-do-not-send="true">https://pgbouncer.github.io/&lt;/a&gt;&lt;br&gt;
<br>
in pgbouncer.ini you enter database configuration values like<br>
<br>
database = host=hostname port=xyzk, like<br>
mydb1 = host=cluster1 port=6543 or<br>
mydb2 = host=cluster1 port=9876<br>
mydb3 = host=cluster2 port=6543<br>
<br>
but there many other parameters to refine your config (like
"proxying" <br>
database names, so if you share names across clusters you can
easily <br>
avoid conflicts)<br>
<br>
Pgbouncer should be installed on the same server as the
databases or in <br>
another and listens on a different port than Postgres' (say
5431 while <br>
postgres is on 5432)<br>
I'm actively using in my environment with 2 clusters and about
500 <br>
databases, works flawlessly.<br>
<br>
One thing you have to consider, if under heavy workload (say
100's of <br>
connections) is to raise kernel value of maximum open files<br>
<br>
Cheers<br>
<br>
Moreno.-<br>
<br>
<br>
<br>
</blockquote>
</div>
</blockquote>
<p><br>
</p>
</body>
</html>