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(at)192(dot)168(dot)0(dot)1:5432
> ─> pgs2.server.net:5433 ─> PG_Cluster2(at)192(dot)168(dot)0(dot)1:5433
> ─> pgs3.server.net:5434 ─> PG_Cluster3(at)192(dot)168(dot)0(dot)1:5434
> ─> pgs4.server.net:5435 ─> PG_Cluster4(at)192(dot)168(dot)0(dot)1:5435
> ─> pgs5.server.net:5436 ─> PG_Cluster5(at)192(dot)168(dot)0(dot)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 corresponding internal 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(at)localhost:5433
> ─> pgs2.server.net:5432 ─┤ │ ├──> PG_Cluster2(at)localhost:5434
> ─> pgs3.server.net:5432 ─┼─>─┤ 192.168.0.1:5432 ├──> PG_Cluster3(at)localhost:5435
> ─> pgs4.server.net:5432 ─┤ │ ├──> PG_Cluster4(at)localhost:5436
> ─> pgs5.server.net:5432 ─┘ └──────────────────┴──> PG_Cluster5(at)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