From: Thomas Markus <t.markus@proventis.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: Question: Multiple pg clusters on one server can be reached with the standard port.
Date: Fri, 16 Jun 2023 12:04:05 +0200
Message-ID: <0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net> (raw)
In-Reply-To: <71943c04e05c3c13d5cd2b287909f351@weiller.eu>
References: <71943c04e05c3c13d5cd2b287909f351@weiller.eu>
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 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@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
Attachments:
OpenPGP_0x9794716335E9B5AF.asc (application/pgp-keys, 3.1K) download
OpenPGP_signature (application/pgp-signature, 839B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: t.markus@proventis.net
Subject: Re: Question: Multiple pg clusters on one server can be reached with the standard port.
In-Reply-To: <0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net>
References: <71943c04e05c3c13d5cd2b287909f351@weiller.eu> <0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox