Postgresql – how to prevent the postgres connection from timing out? (python, psycopg2)

connectivitypostgresqlpython

very quick one from me: I have a python script that runs continuously, keeping an eye on a postgres database. I've set up a listening channel and everytime a relevant change is made, the script gets a notification and does something accordingly.

the problem I'm having is that sometimes there are hours between notifications and my connection simply "drops" after a while. that means even though a change is taking place, the connection is not active anymore, hence python won't get a notification and everything technically stops.

is there a parameters that can be used to prevent that from happening? when I was using php/mysql, there was a persistent connection option. does postgres have something similar? this is what I currently use to connect:

try:
    conn = psycopg2.connect('postgresql://username:password@localhost:5432')
    conn.set_session(autocommit = True)
    cursor = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
    print('Database connection OK\n')
except:
    print('Database connection error\n')
    sys.exit()

Best Answer

PostgreSQL doesn't drop idle connections. It must be something else, probably a misconfigured firewall.

To defend against that, set the parameter tcp_keepalives_idle on the server to something less than the default 2 hours.

There is also a corresponding parameter keepalives_idle on the client side that you could set when you connect with psycopg2.

Both should do the trick.

Related Question