Help with PostgreSQL too many clients already

Databases
1

Hello all,

Recently I started having trouble connecting to my Ubuntu Linux/PostgreSQL server. Looking at phpPgAdmin or Webmin, I found the error too many clients already. The only way to fix it, has been to restart Postgre, or the server. The server is not open to the outside, other than through a web app, and a Deamon server app - both written in RS2012 R2.1. The web app has been disabled, so I know it is not that. The problem appears to be from the Console/Daemon app.

My issues are many, likely many also that I am not yet aware of. So to start, has anyone else had this, or have any suggestions to find the cause and stop it. The Daemon server app closes the Database connection and kills sets the object to NIL whenever a connection fails (or is disconnected for any reason). So there should never be too many clients.

I can recreate this easily. I run another program, also created in RS2012 R2.1 that connects to the server briefly, then disconnects. It does take a little time, but is fairly predictable. What I cannot do, is to look at the server, and see when a new db client is added, and removed. Does anyone know of tools that will show these things? The PostgreSQL server is, I think stock. It was installed when Ubuntu was installed. There are no other users (it is in my house!). As I found out the other day, outside connection attempts to its port 5432, are not accepted either.

Also, and equally worrisome, is that the operation of the remote and the server side Daemon/Server has not changed - to be clear, some of the code has changed, both to try to stop this and to expand the functionality of the apps - but the basic operation has not changed. This behavior has, for some reason, just recently started. It could even be from Ubuntu, PHP or other Dist updates/upgrades.

Further, I have found that if the Daemon process is killed, I am able to log in with phpPgAdmin, indicating that the PostgreSQL client count is reduced to a level allowing normal operation.

Any help, direction or ideas would be really really appreciated!
Thank you,
Tim

2

You can increase the maximum number of allowed connections. You might also make sure whatever applications you have using the database are closing their connections properly.

3

Hello Phillip,
The allowed number of connection is 100. From what I saw in google searches, this is high/normal count. I do not think it raising the count will solve the problem. I really need to find the issue…

Your second idea, is where I need a tool to check and verify that all of the connections are actually closing. I am searching the app to see if I missed anything. Entirely possible and probable! But a bit elusive at this point.

Thank you for your idea and reply! Much appreciated Phillip.
Tim

4

Does your pgaccess admin tool have a pgmonitor menu item? Newer versions do and will show you stats on all open connections.

5

Alternately, dump the contents of the pg_stat_activity table.

6

I know you’re on PostgreSQL, but MySQL on my server had a problem when it could not resolve the names of the IP addresses connecting. I had to turn this name-resolution off since they would remain logged in and reach the limit. Maybe PostgreSQL has a similar issue?

7

Try setting log_connections = on in postgresql.conf and reload (make sure that log_min_messages is
“fatal” or lower).

Also query the actual connections when you get a problem (SELECT count(*) FROM pg_stat_activity) and check if that’s close to max_connections on your conf.

bench testing can help too

PostgreSQL bench

8

Thank you for the tips David and Lee!
I will try these out in addition to other measures and advise. I hope to have some resolution or at least direction today!
Tim