Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to automatically delete idle client connections?

How to automatically delete idle client connections?

From
Yu Watanabe
Date:
Dear community.

I would like to ask a question regarding the below error occurred when using psql client.
My environment is postgresql 12.3 docker container.
https://hub.docker.com/layers/postgres/library/postgres/12.3/images/sha256-23839ca029051ca19072dc7f40b252ae8cbcd7ef632f7b8e2da09ba3abc60214?context=explore

###
psql: error: could not connect to server: FATAL remaining connection slots are reserved for non-replication superuser connections
###

It seems that this is caused by unused psql client sessions. Which was caused by closing the terminal without exiting the psql session correctly.

###
postgres=# select usename,state,client_port from pg_stat_activity;
 usename  | state  | client_port
----------+--------+-------------
          |        |
 postgres |        |
 catseye  | idle   |          -1
 catseye  | idle   |       36718
 catseye  | idle   |          -1
 catseye  | idle   |       52960
 catseye  | idle   |       40854
...
###

I have tried setting  idle_in_transaction_session_timeout   , however, this does not delete idle sessions.

Would there be any expiry settings to delete client idle sessions automatically ?

My apology if this question was asked before but I appreciate it if someone gives me advice.

Best Regards,
Yu Watanabe

--
Yu Watanabe

linkedin: www.linkedin.com/in/yuwatanabe1/
twitter:   twitter.com/yuwtennis
   

Re: How to automatically delete idle client connections?

From
David Rowley
Date:
On Mon, 21 Jun 2021 at 21:59, Yu Watanabe <yu.w.tennis@gmail.com> wrote:
> I have tried setting  idle_in_transaction_session_timeout   , however, this does not delete idle sessions.
>
> Would there be any expiry settings to delete client idle sessions automatically ?

There is idle_session_timeout in PostgreSQL 14, but that's not much
use to you, since a) it's not out yet, and b) you're using 12.

I guess you could do something like:

SELECT pg_terminate_backend(pid) FROM pg_stat_Activity where state =
'idle' and state_change < NOW() - INTERVAL '1 hour';

Change '1 hour' to whatever you like and maybe set up a cron job to run that.

David

Re: How to automatically delete idle client connections?

From
Ron
Date:
On 6/21/21 5:17 AM, David Rowley wrote:
On Mon, 21 Jun 2021 at 21:59, Yu Watanabe <yu.w.tennis@gmail.com> wrote:

I have tried setting  idle_in_transaction_session_timeout   , however, this does not delete idle sessions.

Would there be any expiry settings to delete client idle sessions automatically ?

There is idle_session_timeout in PostgreSQL 14, but that's not much
use to you, since a) it's not out yet, and b) you're using 12.

I guess you could do something like:

SELECT pg_terminate_backend(pid) FROM pg_stat_Activity where state =
'idle' and state_change < NOW() - INTERVAL '1 hour';

Change '1 hour' to whatever you like and maybe set up a cron job to run that.

That's what we did.

--
Angular momentum makes the world go 'round.

Re: How to automatically delete idle client connections?

From
Yu Watanabe
Date:
Ron , David 

Thank you for the advice.

SQL statement had worked out in our environment and are also looking forward to the new release.

Best Regards,
Yu Watanabe


On Tue, Jun 22, 2021 at 6:05 AM Ron <ronljohnsonjr@gmail.com> wrote:
On 6/21/21 5:17 AM, David Rowley wrote:
On Mon, 21 Jun 2021 at 21:59, Yu Watanabe <yu.w.tennis@gmail.com> wrote:

I have tried setting  idle_in_transaction_session_timeout   , however, this does not delete idle sessions.

Would there be any expiry settings to delete client idle sessions automatically ?

There is idle_session_timeout in PostgreSQL 14, but that's not much
use to you, since a) it's not out yet, and b) you're using 12.

I guess you could do something like:

SELECT pg_terminate_backend(pid) FROM pg_stat_Activity where state =
'idle' and state_change < NOW() - INTERVAL '1 hour';

Change '1 hour' to whatever you like and maybe set up a cron job to run that.

That's what we did.

--
Angular momentum makes the world go 'round.


--
Yu Watanabe

linkedin: www.linkedin.com/in/yuwatanabe1/
twitter:   twitter.com/yuwtennis