pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (37+ messages)  latest in thread

Message-ID: <0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net>
Permalink:  ../0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net/
Also on:    postgresql.org/message-id/0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net

 · 

reply

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