Thread: Pgbouncer

Pgbouncer

From
Rajesh Kumar
Date:
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?

Re: Pgbouncer

From
Erik Wienhold
Date:
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



Re: Pgbouncer

From
Achilleas Mantzios - cloud
Date:


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

Re: Pgbouncer

From
Rajesh Kumar
Date:
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

Re: Pgbouncer

From
Erik Wienhold
Date:
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



Re: Pgbouncer

From
Rajesh Kumar
Date:
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

Re: Pgbouncer

From
Erik Wienhold
Date:
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



Re: Pgbouncer

From
Rajesh Kumar
Date:
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

Re: Pgbouncer

From
Erik Wienhold
Date:
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