[pgpool-general: 8918] Re: How to identify pgpool connections to PostgreSQL

[Bo Peng]

Hi,

> 1) Is there a way to identify the database connections originating from
> pgpool, within PostgreSQL? ie pg_stat_activity?

If your applications and pgpool nodes are running on different servers,
I think you can use "pg_stat_activity.client_addr" to check the connection
if it is connected by pgpool.

On Wed, 9 Aug 2023 15:24:41 -0600
Steeve Boulanger <sboulanger29 at gmail.com> wrote:

> Greetings,
> 
> Question:
> 1) Is there a way to identify the database connections originating from
> pgpool, within PostgreSQL? ie pg_stat_activity?
> 
> Use case:
> we currently have a few hundreds micro-services, among which some are
> connecting to pgpool, and some are still connecting directly to the
> database. The ultimate goal is to have all applications connecting to
> pgpool, but first, we have to fix an issue with idle connections. For this
> reason, I need a way to be able to identify the pgpool vs non-pgpool
> connections , from within PostgreSQL.
> 
> If there's no other way than using "show pool_pools",  then I have a couple
> more questions / suggestions:
> 
> 2) Is there a way to "query"  the "show pool_pools" results? In other
> words, is it possible to join the "show pool_pools" results with
> pg_stat_activity for example?
> 
> 3) Assuming none of the above is currently possible, it would be a great
> feature if we could define a prefix that could be appended to the
> "appication_name" value coming from the client perhaps.
> Example:
> client connection:         psql -d 'dbname=postgres application_name=mypsql'
> pgpool param (new):   application_name_prefix  = 'pgp-'
> pg_stat_activity:           application_name shows "pgp-mypsql'
> 
> Regards,
> Steeve


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/