PostgreSQL's OperationalError: server closed the connection unexpectedly

Created:22-06-04 14:16    Updated:23-01-28 22:55


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",
                        host="Your_IP_address",
                        user="username",
                        password="Your_password",
                        port=5432,
                        keepalives=1,
                        keepalives_idle=130,
                        keepalives_interval=10,
                        keepalives_count=15)

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


An Online Pomodoro Timer is here