Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qAE9A-0001rp-Vh for pgsql-general@arkaria.postgresql.org; Fri, 16 Jun 2023 18:26:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qAE99-0003Di-AR for pgsql-general@arkaria.postgresql.org; Fri, 16 Jun 2023 18:26:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qA80q-000470-8n for pgsql-general@lists.postgresql.org; Fri, 16 Jun 2023 11:53:08 +0000 Received: from prater.isola.red ([2a0a:4cc0:1:4e:2442::2]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qA80k-002WaJ-6i for pgsql-general@lists.postgresql.org; Fri, 16 Jun 2023 11:53:05 +0000 Received: from wm.joel-hatsch.net (unknown [172.29.0.3]) (Authenticated sender: michael@weiller.eu) by prater.isola.red (Postfix) with ESMTPSA id 3659DC0A3F; Fri, 16 Jun 2023 13:52:56 +0200 (CEST) MIME-Version: 1.0 Date: Fri, 16 Jun 2023 11:52:56 +0000 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable From: "Michael Weiller" Message-ID: <6c3babfef3065d8e1d69b8618a1aeac3@weiller.eu> Subject: Re: Question: Multiple pg clusters on one server can be reached with the standard port. To: "Thomas Markus" Cc: pgsql-general@lists.postgresql.org In-Reply-To: <0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net> References: <0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net> <71943c04e05c3c13d5cd2b287909f351@weiller.eu> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 16. Juni 2023 12:04, "Thomas Markus" schrieb: > Hi >=20 >=20Am 16.06.23 um 11:40 schrieb Brainmue: >=20 >>=20Hello all, >>=20 >>=20I am currently looking for a solution similar to Oracle Listener. >>=20 >>=20Current situation: >> I have a system with multiple PostgreSQL clusters for different databa= ses. >> Each cluster is running on the same physical machine and is accessed t= hrough its own DNS alias and >> corresponding port. >> I only have one single IP address available, and there is no possibili= ty of obtaining additional >> addresses. >>=20 >>=20Here's an example: >>=20 >>=20DNS ALIAS Host >> =E2=94=80> pgs1.server.net:5432 =E2=94=80> PG_Cluster1@192.168.0.1:543= 2 >> =E2=94=80> pgs2.server.net:5433 =E2=94=80> PG_Cluster2@192.168.0.1:543= 3 >> =E2=94=80> pgs3.server.net:5434 =E2=94=80> PG_Cluster3@192.168.0.1:543= 4 >> =E2=94=80> pgs4.server.net:5435 =E2=94=80> PG_Cluster4@192.168.0.1:543= 5 >> =E2=94=80> pgs5.server.net:5436 =E2=94=80> PG_Cluster5@192.168.0.1:543= 6 >>=20 >>=20Desired 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 al= ias. >> It would also be desirable if this service could provide additional fe= atures like connection >> pooling or other functionalities. >> Similar to a reverse proxy. >>=20 >>=20Here's an example: >>=20 >>=20DNS ALIAS HOST >> =E2=94=80> pgs1.server.net:5432 =E2=94=80=E2=94=90 =E2=94=8C=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=AC=E2=94=80=E2=94=80> PG_Cluster1@localhost:5433 >> =E2=94=80> pgs2.server.net:5432 =E2=94=80=E2=94=A4 =E2=94=82 =E2=94=9C= =E2=94=80=E2=94=80> PG_Cluster2@localhost:5434 >> =E2=94=80> pgs3.server.net:5432 =E2=94=80=E2=94=BC=E2=94=80>=E2=94=80= =E2=94=A4 192.168.0.1:5432 =E2=94=9C=E2=94=80=E2=94=80> PG_Cluster3@local= host:5435 >> =E2=94=80> pgs4.server.net:5432 =E2=94=80=E2=94=A4 =E2=94=82 =E2=94=9C= =E2=94=80=E2=94=80> PG_Cluster4@localhost:5436 >> =E2=94=80> pgs5.server.net:5432 =E2=94=80=E2=94=98 =E2=94=94=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=B4=E2=94=80=E2=94=80> PG_Cluster5@localhost:5437 >>=20 >>=20Is there a solution for this, and what are the advantages or limitat= ions that arise from it? >>=20 >>=20Thank you in advance for your suggestions and help. >>=20 >>=20Regards, >> Michael >=20 >=20possible solutions: > * set up a firewall rule to forward connection > * use a tcp proxy (nginx can do that) > * check pg_bouncer >=20 >=20best regards > Thomas Hello Thomas, Thank you for your quick reply. With firewall you mean an additional software, right? Because with iptables or netfilter I can't forward TCP packets based on t= he DNS alias name. Or is that possible? I have the same problem with nginx. I just looked in the documentation ag= ain but I can't find a way to distinguish which cluster to forward to bas= ed on the DNS alias. Do you have an example for me? We have already looked at pgbouncer and it works with that but unfortunat= ely you have to do the authentication in pgbouncer. Which we don't like s= o much. Regards, Michael