How to automatically delete idle client connections?
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?
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 wrote: > On 6/21/21 5:17 AM, David Rowley wrote: > > On Mon, 21 Jun 2021 at 21:59, Yu Watanabe > 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
Memory activities to monitor in statistics collector?
Dear community I would like to ask a question regarding system monitoring. I am sorry if this question was asked before. My postgresql is running on Azure database for Postgresql (ver 12) which runs on cloud platform. My goal is to observe memory statistics to see if there are any memory suspicious statistics that might be the reason for query OOM . https://pganalyze.com/docs/log-insights/server/S5 The guide Microsoft has is too general to detect query OOM. https://azure.microsoft.com/ja-jp/blog/best-practices-for-alerting-on-metrics-with-azure-database-for-postgresql-monitoring/ Thus I was thinking if there are any statistics I can monitor. "/proc/*/smaps" seems to be one option but it's not a choice for me. https://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/ Would there be any statistics or any combination of stat items which can lead to query OOM ? https://www.postgresql.org/docs/12/monitoring-stats.html Best Regards, Yu Watanabe -- Yu Watanabe linkedin: www.linkedin.com/in/yuwatanabe1/ twitter: twitter.com/yuwtennis
