postgresql psycopg2 与服务器断开连接

psycopg2 disconnects from server

提问by ironfroggy

I've been tackling this for a while. I setup a completely new machine. I've installed a fresh copy of postgresql and all my other dependencies. Basically, I get these database disconnections at random times. I can perform identical requests and either it works or it doesn't. Very nondeterministic in outward appearance. Watching logs at Postgresql, it doesn't even get a connection. Now, I would expect that if it never connected I would get this problem when establishing the connection and getting the cursor, but I get it when trying to actually use the connection later. Given the traceback below, I would expect to see a connection made in the pg logs, and then disconnected for some reason later. I don't, so I wonder if there is some clue in that mismatch.

我一直在处理这个问题。我设置了一台全新的机器。我已经安装了 postgresql 和所有其他依赖项的新副本。基本上，我会在随机时间断开这些数据库连接。我可以执行相同的请求，无论它是否有效。外观非常不确定。在 Postgresql 上查看日志，它甚至没有建立连接。现在，我希望如果它从未连接过，我会在建立连接和获取游标时遇到这个问题，但是在稍后尝试实际使用连接时我会遇到这个问题。鉴于下面的回溯，我希望在 pg 日志中看到一个连接，然后由于某种原因断开连接。我没有，所以我想知道这种不匹配是否有一些线索。

Traceback (most recent call last):
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/core/handlers/wsgi.py", line 242, in __call__
    response = self.get_response(request)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/core/handlers/base.py", line 73, in get_response
    response = middleware_method(request)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/middleware/locale.py", line 16, in process_request
    language = translation.get_language_from_request(request)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/utils/translation/__init__.py", line 97, in get_language_from_request
    return real_get_language_from_request(request)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/utils/translation/trans_real.py", line 349, in get_language_from_request
    lang_code = request.session.get('django_language', None)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/contrib/sessions/backends/base.py", line 63, in get
    return self._session.get(key, default)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/contrib/sessions/backends/base.py", line 172, in _get_session
    self._session_cache = self.load()
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/contrib/sessions/backends/db.py", line 16, in load
    expire_date__gt=datetime.datetime.now()
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/db/models/manager.py", line 120, in get
    return self.get_query_set().get(*args, **kwargs)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/db/models/query.py", line 300, in get
    num = len(clone)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/db/models/query.py", line 81, in __len__
    self._result_cache = list(self.iterator())
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/db/models/query.py", line 238, in iterator
    for row in self.query.results_iter():
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/db/models/sql/query.py", line 287, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/db/models/sql/query.py", line 2369, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.1-py2.6.egg/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

采纳答案by pcardune

This is a very similar question to the one posted here:

这是一个与此处发布的问题非常相似的问题：

Django + FastCGI - randomly raising OperationalError

Django + FastCGI - 随机引发 OperationalError

I imagine the answer will be the same to both if and when someone eventually figured it out. This same problem has been bothering me for about a month now and I have no idea what could be causing it.

我想如果有人最终弄明白了，答案将是相同的。同样的问题已经困扰我大约一个月了，我不知道是什么原因造成的。

回答by Matan Dobrushin

Even though it's very old question, Best Solution I've found is in thisanswer. just do the following:

尽管这是一个很老的问题，但我发现的最佳解决方案是在这个答案中。只需执行以下操作：

from django import db

and before calling fork or using multiprocessing execute:

在调用 fork 或使用多处理执行之前：

db.connections.close_all()

回答by Denis Otkidach

Do you fork()child processes (use preforked FastCGI or something similar)? This might be the reason that connection established in parent process doesn't work in child. If you use preforked method it's easy to switch to threading to see whether the problem has gone away. I saw exactly the same floating error in such case.

您是否有fork()子进程（使用预分叉的 FastCGI 或类似的东西）？这可能是在父进程中建立的连接在子进程中不起作用的原因。如果您使用预分叉方法，则很容易切换到线程处理以查看问题是否已消失。在这种情况下，我看到了完全相同的浮动错误。

回答by robinsax

In my case, my WSGI server, uWSGI, was forking my app processes such that the underlying connection pool was shared. This was causing undefined behavior that (non-deterministically) was sending connections down as OP saw.

就我而言，我的 WSGI 服务器 uWSGI 正在分叉我的应用程序进程，以便共享底层连接池。这导致未定义的行为（非确定性地）在 OP 看到的情况下发送连接。

I'd recommend ensuring your app processes are eagerly forked. In uWSGI you do that with the option

我建议确保您的应用程序进程急切地分叉。在 uWSGI 中，您可以使用选项来做到这一点

lazy-apps = true

lazy-apps = true