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.

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).

8
  • Connections with this idle select are from PHP. PHP should close connection at the end of the script. – Martin Perry Aug 7 '19 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 '19 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 '19 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 '19 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 '19 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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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