Auto close idle connections for specific user (not by pg_cancel command)

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Auto close idle connections for specific user (not by pg_cancel command)

Emi Lu-2
Hello,

In psql, may I know is there a way/config that idle connections could be auto closed (but not by
pg_cancel_backend command) for specific user(s) please?

For example, 
select  usename, waiting, query_start  from pg_stat_activity 
where usename like 'connUser1' and
           current_query='<IDLE>';

All connections from connUser1, if idle more than 1 day, psql auto close them.

Thanks a lot.
Reply | Threaded
Open this post in threaded view
|

Re: Auto close idle connections for specific user (not by pg_cancel command)

Pavel Stehule
Hi


út 5. 2. 2019 v 18:47 odesílatel Emi <[hidden email]> napsal:
Hello,
 
In psql, may I know is there a way/config that idle connections could be auto closed (but not by
pg_cancel_backend command) for specific user(s) please?

For example, 
select  usename, waiting, query_start  from pg_stat_activity 
where usename like 'connUser1' and
           current_query='<IDLE>';

All connections from connUser1, if idle more than 1 day, psql auto close them.

What I know, there is not any timeout for this purpose.

Maybe pgbouncer can do this https://pgbouncer.github.io/config.html

client_idle_timeout

Regards

Pavel

Thanks a lot.
Reply | Threaded
Open this post in threaded view
|

Re: Auto close idle connections for specific user (not by pg_cancel command)

Thomas Poty
Hi,
Maybe pgterminator could help you
Regards 
Thomas 

Le mar. 5 févr. 2019 à 18:52, Pavel Stehule <[hidden email]> a écrit :
Hi


út 5. 2. 2019 v 18:47 odesílatel Emi <[hidden email]> napsal:
Hello,
 
In psql, may I know is there a way/config that idle connections could be auto closed (but not by
pg_cancel_backend command) for specific user(s) please?

For example, 
select  usename, waiting, query_start  from pg_stat_activity 
where usename like 'connUser1' and
           current_query='<IDLE>';

All connections from connUser1, if idle more than 1 day, psql auto close them.

What I know, there is not any timeout for this purpose.

Maybe pgbouncer can do this https://pgbouncer.github.io/config.html

client_idle_timeout

Regards

Pavel

Thanks a lot.
Reply | Threaded
Open this post in threaded view
|

Re: Auto close idle connections for specific user (not by pg_cancel command)

Thomas Poty
Or you could use
Parameter idle_in_transaction_session_timeout


And use alter role in database set idle_in_transaction_session_timeout to...


Regards 

Le mar. 5 févr. 2019 à 18:59, Thomas Poty <[hidden email]> a écrit :
Hi,
Maybe pgterminator could help you
Regards 
Thomas 

Le mar. 5 févr. 2019 à 18:52, Pavel Stehule <[hidden email]> a écrit :
Hi


út 5. 2. 2019 v 18:47 odesílatel Emi <[hidden email]> napsal:
Hello,
 
In psql, may I know is there a way/config that idle connections could be auto closed (but not by
pg_cancel_backend command) for specific user(s) please?

For example, 
select  usename, waiting, query_start  from pg_stat_activity 
where usename like 'connUser1' and
           current_query='<IDLE>';

All connections from connUser1, if idle more than 1 day, psql auto close them.

What I know, there is not any timeout for this purpose.

Maybe pgbouncer can do this https://pgbouncer.github.io/config.html

client_idle_timeout

Regards

Pavel

Thanks a lot.
Reply | Threaded
Open this post in threaded view
|

Re: Auto close idle connections for specific user (not by pg_cancel command)

Thomas Poty
You are right. So it is not suitable for your case. Sorry 

Le mar. 5 févr. 2019 à 19:27, Ravi Krishna <[hidden email]> a écrit :
>
> https://www.cybertec-postgresql.com/en/idle_in_transaction_session_timeout-terminating-idle-transactions-in-postgresql/
>
> And use alter role in database set idle_in_transaction_session_timeout to...
>
> https://www.postgresql.org/docs/10/sql-alterrole.html

But this works only for those sessions which has started a transaction and not committed within the session_timeout period, right ?