Server closed the connection unexpectedly sometimes

Question

Project background:

Django project, I use gunicorn to run the project, in the project, i use python socket io to process some event
Postgresql, config like this:

DATABASES['default'] = {
    'ENGINE': 'django.db.backends.postgresql_psycopg2',
    'NAME': xxx,
    'USER': xxx,
    'PASSWORD': xxx,
    'HOST': 'xxx',
    'PORT': '5432',
    'CONN_MAX_AGE': 60 * 10,  # seconds
    'OPTIONS': {
        'connect_timeout': 20,
    },
}

the python socket io will keep a thread to process some events. so the thread has its own postgresql database connection. sometimes, I find it will throw database connection problem, like this, I don't know why the database connection isn't be closed normally

Traceback (most recent call last):
  File "/usr/local/lib/python2.7/site-packages/engineio/server.py", line 520, in _trigger_event
    return self.handlers[event](*args)
  File "/usr/local/lib/python2.7/site-packages/socketio/server.py", line 590, in _handle_eio_message
    self._handle_event(sid, pkt.namespace, pkt.id, pkt.data)
  File "/usr/local/lib/python2.7/site-packages/socketio/server.py", line 526, in _handle_event
    self._handle_event_internal(self, sid, data, namespace, id)
  File "/usr/local/lib/python2.7/site-packages/socketio/server.py", line 529, in _handle_event_internal
    r = server._trigger_event(data[0], namespace, sid, *data[1:])
  File "/usr/local/lib/python2.7/site-packages/socketio/server.py", line 558, in _trigger_event
    return self.handlers[namespace][event](*args)
  File "/usr/src/app/async_worker/controllers/server/event_handler.py", line 61, in on_jira_handle_from_client_retrieve
    ticket = Ticket.get_ticket_by_id(ticket_id)
  File "/usr/src/app/review/models/ticket.py", line 144, in get_ticket_by_id
    return Ticket.objects.filter(id=ticket_id).first()
  File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py", line 564, in first
    objects = list((self if self.ordered else self.order_by('pk'))[:1])
  File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py", line 250, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py", line 1118, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
  File "/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 899, in execute_sql
    raise original_exception
OperationalError: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

This is what I get in the PostgreSQL log file:

LOG: could not receive data from client: Connection reset by peer

some ideas about the problem? I think if the connection keeps too long time (> CONN_MAX_AGE), the conn should be closed and set to None, but actually the connection is not null but the already closed

The error message is from the PostgreSQL client, but the PostgreSQL server will never close idle connections unless it shuts down, crashes or the peer is unreachable. See what the error message in the PostgreSQL log is. I suspect an ill-configured firewall. — Laurenz Albe, Sep 26 '19 at 04:07
the postgres only has some logs like ``` LOG: could not receive data from client: Connection reset by peer ``` and when the server closed error happened, I didn't see the postgres log occured immediately — loveyzhou, Sep 26 '19 at 08:07
That makes me almost certain that this is a network problem. Both sides moan that the peer has suddenly gone away. — Laurenz Albe, Sep 26 '19 at 08:36

score 0 · Answer 1 · answered Sep 26 '19 at 08:40

The error message you see is from the PostgreSQL client library.

Now both the PostgreSQL server and client complain that the other side suddenly went away, so this is almost certainly a nerwork problem.

My money is on an ill-configured firewall that drops idle connections after a while. I've always wondered if there is any valid use case to have a firewall do this...

Server closed the connection unexpectedly sometimes

1 Answers1