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 1qA81U-0002qh-8e for pgsql-general@arkaria.postgresql.org; Fri, 16 Jun 2023 11:53:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qA81S-0004SQ-Vd for pgsql-general@arkaria.postgresql.org; Fri, 16 Jun 2023 11:53:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qA81S-0004SC-LA for pgsql-general@lists.postgresql.org; Fri, 16 Jun 2023 11:53:46 +0000 Received: from prater.isola.red ([2a0a:4cc0:1:4e:2442::2]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qA81P-002luk-Vf for pgsql-general@lists.postgresql.org; Fri, 16 Jun 2023 11:53:46 +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 E507DC0A3F; Fri, 16 Jun 2023 13:53:41 +0200 (CEST) MIME-Version: 1.0 Date: Fri, 16 Jun 2023 11:53:41 +0000 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable From: "Brainmue" Message-ID: 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 based 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 so much. Regards, Michael