How to see unclosed PostgreSQL connection while using psycopg2? [closed]

Question

I run python code:

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

Then I try:

ps auxf | grep postgres

or

SELECT * FROM pg_stat_activity

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

Answer 1

Python answer

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

import psycopg2

conn = psycopg2.connect(database="my_database", 
                        user="username", 
                        password=password, 
                        host="localhost", 
                        port="5432")

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

conn.closed

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:

closed

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

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

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;

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

conn.close()

conn
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:

help(conn)

Notice

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

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, 
       client_hostname,
       client_port,
       application_name,
       backend_start,
       state,
       state_change
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.

But...

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   | 127.0.0.1      |                 |       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 127.0.0.1(59218) 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.

Notice

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   127.0.0.1:59136           127.0.0.1:5432            ESTABLISHED
postgres postgres             14663    tcp4   127.0.0.1:5376            127.0.0.1:59218           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.