topic PostgreSQLserver connections left open in Administration and Deployment

PostgreSQLserver connections left open

goitzy — Wed, 08 Feb 2017 22:03:48 GMT

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

Re: PostgreSQLserver connections left open

JuanS_OCS — Thu, 09 Feb 2017 09:39:02 GMT

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.

Re: PostgreSQLserver connections left open

goitzy — Fri, 17 Feb 2017 18:11:11 GMT

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.

Re: PostgreSQLserver connections left open

JuanS_OCS — Mon, 20 Feb 2017 09:54:02 GMT

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.

Re: PostgreSQLserver connections left open

goitzy — Mon, 20 Feb 2017 18:13:12 GMT

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.

Re: PostgreSQLserver connections left open

dpage — Mon, 20 Feb 2017 18:52:49 GMT

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.

Re: PostgreSQLserver connections left open

goitzy — Mon, 20 Feb 2017 18:56:48 GMT

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

Re: PostgreSQLserver connections left open

dpage — Mon, 20 Feb 2017 18:59:51 GMT

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.