Re: Question: Multiple pg clusters on one server can be reached with the standard port.

Hi

Am 16.06.23 um 11:40 schrieb Brainmue:
> Hello all,
>
> I am currently looking for a solution similar to Oracle Listener.
>
> Current situation:
> I have a system with multiple PostgreSQL clusters for different databases.
> Each cluster is running on the same physical machine and is accessed through its own DNS alias and corresponding
port.
> I only have one single IP address available, and there is no possibility of obtaining additional addresses.
>
> Here's an example:
>
>     DNS ALIAS               Host
> ─> pgs1.server.net:5432 ─> PG_Cluster1@192.168.0.1:5432
> ─> pgs2.server.net:5433 ─> PG_Cluster2@192.168.0.1:5433
> ─> pgs3.server.net:5434 ─> PG_Cluster3@192.168.0.1:5434
> ─> pgs4.server.net:5435 ─> PG_Cluster4@192.168.0.1:5435
> ─> pgs5.server.net:5436 ─> PG_Cluster5@192.168.0.1:5436
>
>
> Desired solution:
> I still want to use the same system with different PostgreSQL clusters for the various databases.
> These clusters are internally accessible through different ports.
> However, I would like a service on the server to receive all external requests on port 5432 and forward them to the
correspondinginternal cluster based on the DNS alias.
 
> It would also be desirable if this service could provide additional features like connection pooling or other
functionalities.
> Similar to a reverse proxy.
>
> Here's an example:
>
>     DNS ALIAS                 HOST
> ─> pgs1.server.net:5432 ─┐   ┌──────────────────┬──> PG_Cluster1@localhost:5433
> ─> pgs2.server.net:5432 ─┤   │                  ├──> PG_Cluster2@localhost:5434
> ─> pgs3.server.net:5432 ─┼─>─┤ 192.168.0.1:5432 ├──> PG_Cluster3@localhost:5435
> ─> pgs4.server.net:5432 ─┤   │                  ├──> PG_Cluster4@localhost:5436
> ─> pgs5.server.net:5432 ─┘   └──────────────────┴──> PG_Cluster5@localhost:5437
>
>
> Is there a solution for this, and what are the advantages or limitations that arise from it?
>
> Thank you in advance for your suggestions and help.
>
> Regards,
>     Michael
>
>

possible solutions:
* set up a firewall rule to forward connection
* use a tcp proxy (nginx can do that)
* check pg_bouncer

best regards
Thomas