0

As recently, I keep getting "org.postgresql.util.PSQLException: FATAL: sorry, too many clients already" on my pgadmin4 (at the same time my application hang/not responsive). Upon studying around and read from another thread in here about pg_stat_activity to troubleshoot this issue I still couldnt find the exact query that perhaps didnot close the connection properly. Here the screenshot after I extract the data from pg_stat_activity. I also combine with pg_stat_statements but still unable to find it.

Is there anyway I can get the actual query so that I find the connection that didnt close properly? Sorry im not backend dev but just junior helping on troubleshooting server issue. enter image description here

I looking from other thread; Survey few postgresql monitoring tools. CREATE EXTENSION pg_stat_statements;

Ahmad Raimi Jasmi
  • 1
  • 2
  • Have a look here -> https://stackoverflow.com/questions/2757549/org-postgresql-util-psqlexception-fatal-sorry-too-many-clients-already – iLuvLogix Nov 05 '22 at 15:21
  • @iLuvLogix Hi, that what im looking at previously. I dont want to increase the max connection but I want to find the problematic query. – Ahmad Raimi Jasmi Nov 05 '22 at 15:32
  • 2
    You are kind of asking the wrong question. Connection closes are not done by queries, so no query is responsible for doing it. The actual query (or statement, I suppose) which was run just before going idle **is** 'COMMIT'. It might not be very informative, but that is the query. There is no easy way to get the last query before that one. You could just terminate all those connections, and the errors generated on the client side should tell you where in the client code those commits came from. – jjanes Nov 05 '22 at 15:48
  • 1
    If you set `log_statement = 'all'` for a period of time while this is happening, you can likely review the database server's log file to see part of your code is misbehaving. – jjanes Nov 05 '22 at 15:51
  • @jjanes Hi!, Sorry if I'm asking the wrong question. Almost all of these thing kinda new (im not database background student). At the moment I unable to simply terminate all those connections ( afraid of data loss ). I understand the last query/statement run before idle is 'Commit' but from what I know there are some query connection that not close properly. I was hoping to find that root problem/connection in production. – Ahmad Raimi Jasmi Nov 05 '22 at 15:57
  • You should not be closing database connections, you should be using a connection pool. – Laurenz Albe Nov 06 '22 at 05:26

0 Answers0