0

I am running PSQL with PostGis extension. After the start of server, after about 3 weeks, I start getting fatal errors for connections with "too many clients already". When I restart the server, problem is gone, but is back again after aprox. 3 weeks.

If I run pg_stat_activity, I got

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

"active";2
"idle";944

And all idle queries are the same - SELECTs.

I cannot debug this problem and restarting server all over is not a real solution.

Improve this question

2 Answers 2

Reset to default
2

Your application has a connection leak. There is no way to fix this on the database side - you'll have to change the application so that it closes connections again.

I guess you set max_connections so high to work around this problem (1000 is too high).

Improve this answer
8
  • Connections with this idle select are from PHP. PHP should close connection at the end of the script.
    – Martin Perry
    Aug 7, 2019 at 8:37
  • @MartinPerry Perhaps it should, but the evidence indicates that it does not. So this is a PHP problem, not a PostgreSQL problem.
    – jjanes
    Aug 7, 2019 at 15:18
  • I have check the code, PHP is closing connections. The idle connections are all from a narrow time window (few minutes), several times a day. But the scripts that runs on the server are used whole day. From logs, there is also no special activity or errors at those "idle" times. Just to be sure, I have explicitely added $pq = null; to the end of each script (where $pq is my Psql wrapper class)
    – Martin Perry
    Aug 7, 2019 at 15:52
  • 1
    @MartinPerry Is there a storm of new script initiations at that point? What library are you using? "I have explicitely added $pq = null;" Does your library provide a specific method such as "$pq->pg_close()" ? That should be more reliable than hoping the garbage collectors gets around to it.
    – jjanes
    Aug 7, 2019 at 16:10
  • There is no difference in server scripts behaviour. I am using plain pg_connect and pg_close in my own wrapper. pg_close is called from class destructor.
    – Martin Perry
    Aug 7, 2019 at 16:12
-1

if it possible for you, you can kill the connection based on its idle time but its always better to fix it at app level.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.