PostgreSQL's OperationalError: server closed the connection unexpectedly

Updated:2022-04-20 22:31:59

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  "" supported parameters. Also note that the same parameters can be passed to the client library using  "" 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

An Online Pomodoro Timer is here