how to close a old connection between psycopg2 and postgresql?

Question

I have been working using psycopg2 to control a local postgresql server in a multiple modules model (of my PhD thesis).

After some time I got a error in the model and it keep a ghost connection, which have been cause trouble when I run the model with the new connection to postgresql server, it call other modules of the model.

At my computer is shown multiples connections of postgresql at same time, ten in total. The older connection has in the properties the last modification 35 days ago.

I uninstall the python, the postgresql and delete the database, after that I re install everything again, and the problem still there.

If there are any guest or help, I appreciate.

score 3 · Accepted Answer · answered Dec 23 '17 at 23:25

If you are a superuser, you can close existing connections as described in the answer here.

Depending on your application, you could also look at modifying how your application is connecting to the DB. Creating a file called mydb.py with something like:

import psycopg2
import psycopg2.pool
from contextlib import contextmanager

dbpool = psycopg2.pool.ThreadedConnectionPool(host=<<YourHost>>,
                                          port=<<YourPort>>,
                                          dbname=<<YourDB>>,
                                          user=<<YourUser>>,
                                          password=<<yourpassword>>,
                                          )

@contextmanager
def db_cursor():
    conn = dbpool.getconn()
    try:
        with conn.cursor() as cur:
            yield cur
            conn.commit()
    except:
        conn.rollback()
        raise
    finally:
        dbpool.putconn(conn)

Then your code could use:

import mydb

def myfunction():
    with mydb.db_cursor() as cur:
        cur.execute("""Select * from blahblahblah...""")

how to close a old connection between psycopg2 and postgresql?

1 Answers1