Thread: Pgbouncer
Hi all
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?
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. [1] https://www.pgbouncer.org/usage.html#admin-console -- Erik
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
I have already created postgres_fdw extension. But I am getting error during pgbouncer extension.
On Thu, 16 Nov 2023, 17:47 Achilleas Mantzios - cloud, <a.mantzios@cloud.gatewaynet.com> wrote:
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
On 2023-11-22 09:20 +0100, Rajesh Kumar wrote: > I have already created postgres_fdw extension. But I am getting error > during pgbouncer extension. What statements do you execute and what error do you get? -- Erik
I am using openshift env and postgres 15.2.
I want to monitor pgbouncer which is deployed as separate pod. And postgres instances are deployed as separate pod.
On Wed, 22 Nov 2023, 18:03 Erik Wienhold, <ewie@ewie.name> wrote:
On 2023-11-22 09:20 +0100, Rajesh Kumar wrote:
> I have already created postgres_fdw extension. But I am getting error
> during pgbouncer extension.
What statements do you execute and what error do you get?
--
Erik
On 2023-11-22 16:07 +0100, Rajesh Kumar wrote: > I am using openshift env and postgres 15.2. > I want to monitor pgbouncer which is deployed as separate pod. And postgres > instances are deployed as separate pod. This doesn't answer my question on what statements do you execute and what error do you get. -- Erik
Lets forget the errors...how to configure to monitor connections coming from pgbouncer
On Wed, 22 Nov 2023, 21:11 Erik Wienhold, <ewie@ewie.name> wrote:
On 2023-11-22 16:07 +0100, Rajesh Kumar wrote:
> I am using openshift env and postgres 15.2.
> I want to monitor pgbouncer which is deployed as separate pod. And postgres
> instances are deployed as separate pod.
This doesn't answer my question on what statements do you execute and
what error do you get.
--
Erik
On 2023-11-22 18:02 +0100, Rajesh Kumar wrote: > Lets forget the errors...how to configure to monitor connections coming > from pgbouncer Did you follow the setup described in pgbouncer_fdw's readme[1]? The pgbouncer connections are in view pgbouncer_servers. How to query that view depends on your monitoring tool. [1] https://github.com/CrunchyData/pgbouncer_fdw/blob/main/README.md -- Erik