PostgreSQLserver connections left open

[goitzy]

Anyone have issues with conections left open? When a process failes there are left over process still active from time to time that you cant match to the corresponding folder in the work folder. However there a ton of posgreSqlServer processes that are left running that i can not identify thier source and if they are orphaned processes.

I am hoping someone can share how they deal with these orphaned connections.

thanks,
Lane

[JuanS_OCS]

Hello Lane, @goitzy,

I would need some additional information. Some questions that raise on my mind are:

• Your PostgreSQL database is the WIP database from SAS, or an external PostgreSQL database that you are connecting with SAS/ACCESS to PostreSQL or SAS/ACCESS to ODBC?
• And the open connections you mention, ydo you see them within the administration view within PostgreSQL/pgAdmin, or in the Operating System?

The last question, I am dropping it because one of the characteristics of PostreSQL, and some people get consused because of it, is that this is a multi-threaded database, with a pool of connections reflected on several running psql processes, which is the expected behaviour on a multi-threaded database.

[goitzy]

WIP database from SAS or SAS/ACCESS to ODBC (SQLServer) I am guessing.

We do not have any PostdreSQL in house so it would be all native to SAS.

I see the conections via OS task manager.

It could be connections for multithreaded application but the list never shrinks so processes leave either orphans or never close.

[JuanS_OCS]

Hello @goitzy,

in that case, that is perfectly normal. From the PostreSQL's manual ( https://www.postgresql.org/docs/current/static/tutorial-arch.html 😞

The PostgreSQL server can handle multiple concurrent connections from clients. 
To achieve this it starts ("forks") a new process for each connection.
From that point on, the client and the new server process communicate without intervention by the original postgres process. 
Thus, the master server process is always running, waiting for client connections, whereas client and associated server processes come and go.

[goitzy]

I find it disturbing that i have 153 prostgres connections using on average 2,644 k of memory per connection running at the moment. This is a waste of resources in my opinion and there seems to be no recycling of these connections.

[dpage]

The Web Application Servers have a connection pool they allocate and re-use connections from, is it possible that's what you're seeing? They should clean up when you shut down the web application server though. The way the postgres server works, you'll see a separate process created for each incoming connection, so they may appear orphaned on the database server side, but the java process that's using them is still running. 

[goitzy]

I am seeing these connections on the Compute server and the environment that im using does not have any users currently in it (staging) but we are running data loads via odbc using DI Studio. I would imaging a reboot would take care of most of them but knowing the reason they are they are persistent would make me feel better

[dpage]

Can you confirm the other endpoint of the connection? The database may be on the compute tier, but the initiant may be on another machine (so you'd see no corresponding local process for it besides the postgres process) The Web Application server keeps a pool of connections open at all times, user activity or not. I think if you shut the web application server down as an experiment you'd see them go away, but if that's what you're seeing, it's expected.