Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3bJG-004i2B-4y for pgsql-admin@arkaria.postgresql.org; Thu, 16 Nov 2023 12:17:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r3bJE-006aOc-9o for pgsql-admin@arkaria.postgresql.org; Thu, 16 Nov 2023 12:17:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3bJD-006aOJ-UT for pgsql-admin@lists.postgresql.org; Thu, 16 Nov 2023 12:17:23 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3bJB-006jV5-M3 for pgsql-admin@lists.postgresql.org; Thu, 16 Nov 2023 12:17:23 +0000 Content-Type: multipart/alternative; boundary="------------7R81L9kCsloizvj4pUiKN5wz" Message-ID: Date: Thu, 16 Nov 2023 14:17:18 +0200 MIME-Version: 1.0 Subject: Re: Pgbouncer Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Achilleas Mantzios - cloud In-Reply-To: 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. --------------7R81L9kCsloizvj4pUiKN5wz Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 11/16/23 13:59, Erik Wienhold wrote: > On 2023-11-16 06:43 +0100, Rajesh Kumar wrote: >> I am using postgres 15 in redhat openshift container. I am trying to >> configure pgbouncer. >> >> I want to know how to find in database whether the connections are from >> pgbouncer or direct connections? Or, is there any other way to monitor in >> this environment? > You can check column pg_stat_activity.client_addr if your pgbouncer and > the clients making direct connections are on different hosts. > > Otherwise you can list pgbouncer's connections with SHOW SERVERS in the > admin console[1] and cross-check with pg_stat_activity. Columns > local_addr and local_port of SHOW SERVERS matches client_addr and > client_port in pg_stat_activity. The remaining connections in > pg_stat_activity should be from other clients and backends. In addition to what Erik said, there is pgbouncer_fdw, which makes several pg_bouncer real time monitoring data available as foreign tables, so you can join pg_stat_activity with pgbouncer_servers and draw conclusions. > > [1]https://www.pgbouncer.org/usage.html#admin-console > --------------7R81L9kCsloizvj4pUiKN5wz Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

On 11/16/23 13:59, Erik Wienhold wrote:

On 2023-11-16 06:43 +0100, Rajesh Kumar wrote:

I am using postgres 15 in redhat openshift container. I am trying to
configure pgbouncer.

I want to know how to find in database whether the connections are from
pgbouncer or direct connections? Or, is there any other way to monitor in
this environment?

You can check column pg_stat_activity.client_addr if your pgbouncer and
the clients making direct connections are on different hosts.

Otherwise you can list pgbouncer's connections with SHOW SERVERS in the
admin console[1] and cross-check with pg_stat_activity.  Columns
local_addr and local_port of SHOW SERVERS matches client_addr and
client_port in pg_stat_activity.  The remaining connections in
pg_stat_activity should be from other clients and backends.

In addition to what Erik said, there is pgbouncer_fdw, which makes several pg_bouncer real time monitoring data available as foreign tables, so you can join pg_stat_activity with pgbouncer_servers
and draw conclusions.


[1] https://www.pgbouncer.org/usage.html#admin-console

--------------7R81L9kCsloizvj4pUiKN5wz--