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 1qBM86-00016G-4Z for pgsql-general@arkaria.postgresql.org; Mon, 19 Jun 2023 21:09:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qBM84-0003a9-Up for pgsql-general@arkaria.postgresql.org; Mon, 19 Jun 2023 21:09:40 +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 1qBM84-0003a0-IZ for pgsql-general@lists.postgresql.org; Mon, 19 Jun 2023 21:09:40 +0000 Received: from mail-vk1-xa2a.google.com ([2607:f8b0:4864:20::a2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qBM81-0037ep-WB for pgsql-general@lists.postgresql.org; Mon, 19 Jun 2023 21:09:39 +0000 Received: by mail-vk1-xa2a.google.com with SMTP id 71dfb90a1353d-4718ddce780so777722e0c.1 for ; Mon, 19 Jun 2023 14:09:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1687208977; x=1689800977; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=lytijszik7SCUeleKT8XzXMR0BM+s4fFO+DMkiqIofQ=; b=ZRXm5q81EpOSj4vUQ5C5WHQNgkL1iWdcNnV90A6/KXf35tiAmyIhMgw6vjihnQGvo0 fFfOlxy6vk0wDieiinSE4Sm8wlhFZS6qPtVw0HSCQF7BcciMLx28pzB0hb2YWL5XVQ2C oqJw40U7pf800sMRoAnQGTAAxnZ7YuZfnUf1OZuncnc1nodaxJvPbiY7ykIUkYIpplH2 P11D3WUX9JWEQezU5AAf+5L1ECj6etpZrQ1AYW56q8zVn9eJlf2v0BC89zkkoKq6YD8y FlhCetGWNVVFRJRpxYwwFAUTEMVHHfK2sOhLSBghzjFKqv/q7A1AbSqnfSnfLKB0mPIe ANZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687208977; x=1689800977; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=lytijszik7SCUeleKT8XzXMR0BM+s4fFO+DMkiqIofQ=; b=Z6PBZ0I9Lltd8PWfx8iTXk+pMF908vI2Y640qkzrbnYZ5c0Uy7rdrk/u5mMaiGvy/Q ACgVjTjwZyFIFzcr9bvUF8Xa8VsTBmZeA6bQmpbA1l2+Y8d5tmHjHSupvdlzrULN6Wja 0UxJymQRekSx8CwSClLwENZc4sm/aCQcCaflSWS7ph12dlmx3GulDkk4tfMiV2/POEiO bWGbhX2CNIpJmRjuNptRtdLXWjl8BPh41n8Bt6QwwUkRgrFWrEyuTvo2GN19Iu/IVFB8 YcHbIkLSWN/oKrMKAaMBBmvTKbl5vfyjHUcA1RR8TAimHXEeogtqaQfiqNno7cPBzkrV 1/ng== X-Gm-Message-State: AC+VfDzYB8x2PBVp5yKJ252BRQryjtMNTZUnN/l3qWpyf9jYRISHnrYv yt0kwgvnpvfW+CvabxI462jugmvZwgE= X-Google-Smtp-Source: ACHHUZ4nUvUqlntR99wMbbk7A3o+DDRsqB5gydx3RSLDr7QW9ZVlsuDnUtipLsoTC/YfGoeUIGy+dA== X-Received: by 2002:a1f:5c08:0:b0:457:d0f:37bf with SMTP id q8-20020a1f5c08000000b004570d0f37bfmr3439116vkb.16.1687208976588; Mon, 19 Jun 2023 14:09:36 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:e18f:d5cd:212b:24c8? ([2600:1700:bd2c:b810:e18f:d5cd:212b:24c8]) by smtp.googlemail.com with ESMTPSA id w17-20020a814911000000b0056feaba6c40sm142557ywa.128.2023.06.19.14.09.36 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 19 Jun 2023 14:09:36 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------atgJWUlpxaQEfttC2qeWy2BU" Message-ID: <47751285-8c76-c31f-97a4-5c6e2e7db72e@gmail.com> Date: Mon, 19 Jun 2023 16:09:34 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Question: Multiple pg clusters on one server can be reached with the standard port. Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <0c2dd853-08ec-dbed-00c3-a680ae711d89@proventis.net> <71943c04e05c3c13d5cd2b287909f351@weiller.eu> <20230619103335.xyagd43uhneducxz@hjp.at> <0f467ff1-8cdb-2460-75d6-0e0e4497890f@gmail.com> <20230619171536.2kcrdor36rqchicq@hjp.at> From: Ron In-Reply-To: <20230619171536.2kcrdor36rqchicq@hjp.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------atgJWUlpxaQEfttC2qeWy2BU Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 6/19/23 12:15, Peter J. Holzer wrote: > On 2023-06-19 07:49:49 -0500, Ron wrote: >> On 6/19/23 05:33, Peter J. Holzer wrote: >>> As Francisco already pointed out, this can't work with nginx either. The >>> client resolves the alias and the TCP packets only contain the IP >>> address, not the alias which was used to get that address. So nginx >>> simply doesn't have that information and therefore can't act on it. > [...] >>> So (again, as Francisco already wrote) the best way is probably to write >>> a simple proxy which uses the database (not DNS) name for routing. I >>> seem to remember that nginx has a plugin architecture for protocols so >>> it might make sense to write that as an nginx plugin instead of a >>> standalone server, but that's really a judgement call the programmer has >>> to make. Another possibility would of course be to extend pgbouncer to >>> do what the OP needs. >> How would this work with JDBC clients? > Same as with any other client, I guess. Any reason why it should be > different? That goes to my ultimate point: /why/ would this work, when the point of a database client is to connect to a database instance on a specific port like 5432, not connect to a web server. Obviously it does/should work, but I wouldn't know where to look to lean why. -- Born in Arizona, moved to Babylonia. --------------atgJWUlpxaQEfttC2qeWy2BU Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit On 6/19/23 12:15, Peter J. Holzer wrote:
On 2023-06-19 07:49:49 -0500, Ron wrote:
On 6/19/23 05:33, Peter J. Holzer wrote:
As Francisco already pointed out, this can't work with nginx either. The
client resolves the alias and the TCP packets only contain the IP
address, not the alias which was used to get that address. So nginx
simply doesn't have that information and therefore can't act on it.
[...]
So (again, as Francisco already wrote) the best way is probably to write
a simple proxy which uses the database (not DNS) name for routing. I
seem to remember that nginx has a plugin architecture for protocols so
it might make sense to write that as an nginx plugin instead of a
standalone server, but that's really a judgement call the programmer has
to make. Another possibility would of course be to extend pgbouncer to
do what the OP needs.
How would this work with JDBC clients?
Same as with any other client, I guess. Any reason why it should be
different?

That goes to my ultimate point: why would this work, when the point of a database client is to connect to a database instance on a specific port like 5432, not connect to a web server.

Obviously it does/should work, but I wouldn't know where to look to lean why.

--
Born in Arizona, moved to Babylonia.
--------------atgJWUlpxaQEfttC2qeWy2BU--