how do I prevent my postgres connection from timing out? (python, psycopg2) 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 &quot;drops&quot; 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()
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.