I run python code:

con = psycopg2.connect(some_dsn)
cur = con.cursor()
res = cur.fetchone()
#con.close() <-- connection is not closed

Then I try:

ps auxf | grep postgres


SELECT * FROM pg_stat_activity

both does not display correspond information. How to see that unclosed connection?

1 Answer 1


Python answer

Given a psycopg2 connection object called conn, initialized by, e.g.:

import psycopg2

conn = psycopg2.connect(database="my_database", 

If I'd like to see if conn is closed or not, what I usually do in python 3 is;


If the .closed method returns 1, then the connection is closed.
If the .closed method returns 0, then the connection is still opened.

As stated in the documentation:


Read-only integer attribute: 0 if the connection is open, nonzero if it is closed or broken.


You can also see that by just typing in the name of your connection in a python console and watch at the closed key at the very end;

Out[4]: <connection object at 0x7fe3d042e2b8; dsn: 'user=postgres 
        password=xxx dbname=my_database host=localhost port=5432', closed: 0>


Out[6]: <connection object at 0x7fe3d042e2b8; dsn: 'user=postgres 
        password=xxx dbname=my_database host=localhost port=5432', closed: 1>

You can have more help on the connector methods by using:



It's really important to close a connection once you are done with your changes. Do not forget to commit() them before, of course, otherwise your changes would be lost as if you performed a rollback as stated in the documentation of psycopg2:


Close the connection now (rather than whenever del is executed). The connection will be unusable from this point forward; an InterfaceError will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection. Note that closing a connection without committing the changes first will cause any pending change to be discarded as if a ROLLBACK was performed (unless a different isolation level has been selected: see set_isolation_level()).

Source: https://www.psycopg.org/docs/connection.html

psql and system tools answer

From psql, you can also list connections.

For example by using this command:

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
from pg_stat_activity;

or as you simply stated select * from pg_stat_activity;.
The interesting thing to notice here is the value of client_port.

The former command being modified after: https://dataedo.com/kb/query/postgresql/list-database-sessions

In any cases, the question and answer given here: What exactly is Psycopg2? may be helping understanding that it's barely impossible to differentiate a psycopg2 connection from a standard connection from outside Python using psql; a connection open with psycopg2 will look like any other opened connections.


Now, if you open a psycopg2 connection and you carefully watch at the output of the aforementioned psql command, you will of course see an extra line while the connection is opened (compared to a closed psycopg2 connection).
In an example on my system, the line which corresponds to the connection open by psycogp2 looks like:

 process_id | username | database_name | client_address | client_hostname | client_port |     application_name     |         backend_start         | state  |         state_change          
      12693 | postgres | my_database   |      |                 |       59218 |                          | 2020-03-22 10:58:12.143291+01 | idle   | 2020-03-22 10:58:12.149866+01

It opened a connection on client_port 59218.
As I said, from here, you cannot differentiate from any other connections.

Then, if I look at the output of ps -ef | grep "PID\|59218" (59218 being the client_port of the previous psql output) I have:

$ ps -ef | grep "PID\|59218"

UID PID PPID C STIME TTY TIME CMD postgres 14663 31224 0 10:59 ? 00:00:00 postgres: 10/main: postgres my_database idle

Again, this looks exactly the same as any other connection, so I cannot know which application has opened this connection.

But you can further explore the outputs of netstat:

$ sudo netstat -ap | grep -i "Proto\|59218"
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 localhost:59218         localhost:postgresql    ESTABLISHED 859/python3         
tcp        0      0 localhost:postgresql    localhost:59218         ESTABLISHED 14663/postgres: 10/ 

Where localhost:postgresql corresponds to localhost:5432 (this can be seen by activating/deactivating the -P flag of lsof.) Here you can also see the same postgres PID as the one in the ps output above.
So postgresql is ready to accept IO operations with python3 on the client port 59218 which was opened. This port closes when the psycopg2 connection is closed.

So you can try to figure out that client_port for the connection between python3 and postgresql, e.g. by:

sudo netstat -ap | grep -i "python3" | grep "postgres"

and then grep for its value in the output of either psql and ps.


I also tried to use the sockstat tool:

$ sudo sockstat | grep -i "PID\|5432\|59218"
USER     PROCESS              PID      PROTO  SOURCE ADDRESS            FOREIGN ADDRESS           STATE
username python3              859      tcp4             ESTABLISHED
postgres postgres             14663    tcp4             ESTABLISHED

Here, I unfortunately don't know what is this 59136 port used by Python itself in the source address, nor the 5376 port used by postgres as both doesn't match the default port specified by my pscopg2 connection which is 5432. There is probably underlying protocols/connections being used in socket connections and I'm not an expert in networking to explain those.

I also found this thread: https://serverfault.com/questions/128284/how-to-see-active-connections-and-current-activity-in-postgresql-8-4

It also mentions an interesting tool called pg_top` but which doesn't completely answer your question.

  • Thanks for exhaustive answer, @s.k. I'm aware about .closed, it is useful method on python side. Сlarification: I expected to see bunch of rows in pg_stat_activity or postgres processes with some correspond state(idle for example). Probably I dont understand nature of database connection.
    – MapUser
    Mar 21, 2020 at 10:58
  • Oh, I misunderstood. So, short; you can try to use netstat and grep both on pythonX and postgres to see the active connection between these two processes. Details in the updated answer. This would obviously need further investigations, especially if many connections are open from Python side. Hope it helps.
    – s.k
    Mar 22, 2020 at 12:05

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