In Python, Psycopg2 module is usually used to connect to PostgreSQL. When I used it, I got the below exception when access a connection has been idle for a while:
OperationalError: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
After searching for a while, I found there seems to be 2 reasons that could possibly cause it. And the 2nd reason is seldom mentioned:
It is a bug of Psycopg2 prior version 2.8.1, and it has been reported and fixed
Parameters about keeping alive connections were not used when connections were created.
Please see below codes:
conn = psycopg2.connect(database="DBname",
I didn’t include the last 4 parameters at the beginning (so got the exception), and after I added them the problem went away.
Those parameters actually define how a connection should be kept alive.
Keepalives=1, this makes the connection trying to stay alive, in a way defined by the rest 3 parameters;
keepalives_idle, defines how long a keep-alive message should be sent to the server, after the connection have been in idle;
keepalives_interval, defines how long the module should retry if the keep-alive message is not acknowledged by the server;
keepalives_count, defines how many times the module should retry if previous keep-alive messages was not successful before it shoud give up resending.
I didn’t see the psycopg2 documents introduced these keep-alive parameters, but psycopg2 doc does mention that:
“Any other connection parameter supported by the client library/server can be passed either in the connection string or as a keyword. The PostgreSQL documentation contains the complete list of the "https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS" supported parameters. Also note that the same parameters can be passed to the client library using "https://www.postgresql.org/docs/current/static/libpq-envars.html" environment variables. ”
So those keywords are actually defined and accepted by the underlying PostgreSQL client library. You can find the keep-alive and also more parameters in the document of the PostgreSQL library