PostgreSQL在进行大型插入时连接意外关闭

Question

我正在使用Python和psycopg2将从另一个数据库中提前选择的约1100万行填充到PostgreSQL表中。整个过程需要大约1.5小时才能完成。但是，在大约30分钟后，我会遇到“连接意外关闭”的异常。源代码如下：

incursor = indb.cursor()
incursor.execute("SELECT ...")
indb.commit() # (1) close transaction
outcursor = outdb.cursor()
rows = 0
for (col1, col2, col3) in incursor: # incursor contains ~11.000.000 rows
    outcursor.execute("INSERT ...", (col1, col2, col3)) # This fails after ~30 minutes
    row += 1
    if row % 100 == 0: # (2) Write data every 100 rows
         outcursor.close()
         outdb.commit()
         outcursor = outdb.cursor()
incursor.close()
outcursor.close()
outdb.commit()

在第一次尝试失败后，我在其中插入了(1)和(2)，假设一个开放的事务有一个上限时间约为30分钟，或者光标有一个挂起插入的上限。看来这些假设都不正确，错误出现在其他地方。

两个数据库都存储在VirtualBox机器上，我通过端口转发从主机连接。我在主机上运行程序。

这两个数据库仅供测试目的，并且它们没有其他连接需要管理。也许我必须重写问题以解决这个问题，但是我需要在其他地方进行非常耗时的插入（大约运行几天），因此我非常担心psycopg2或PostgreSQL中存在一些隐藏的时间限制。

- WolfgangP

---

我认为问题可能出在你的配置文件中的work_mem变量上。据我所知，这个变量设置了一个连接允许使用的最大内存。检查日志，应该会有关于问题的记录。 - Voooza

但是那么SELECT语句就完全无法工作了，不是吗？但我失去了与“outdb”的连接。 - WolfgangP

请使用COPY或更大的事务。在单个事务中仅执行100条记录，您需要完成大约110,000个事务才能完成整个作业。单个7400rpm驱动器每秒只能处理120个提交（除非由于缓存而导致不可靠）。您当前的问题听起来像是网络问题。 - Frank Heikens

你是否正在使用连接池？一些连接池具有默认配置，如果连接在一定时间内未被返回，则会关闭连接以避免连接泄漏。 - user330315

work_mem 不会为一个连接设置最大内存，其默认值仅为1MB。 - Glenn Maynard

Answer 1

我不知道postgresql本身是否有这样的“隐藏”超时。PostgreSQL确实有statement_timeout，但如果达到了这个限制，服务器日志中应该会出现ERROR: canceling statement due to statement timeout（同时也会记录被取消的语句）。至于psycopg2，我无法评论。一定要检查服务器日志，看看是否有相关内容。

也许这是一个网络问题？长时间运行的语句将是一个TCP连接，它会保持空闲状态很长一段时间。也许您的端口转发会清除空闲时间超过30分钟的连接？也许您的TCP连接没有使用keepalive。Postgresql有一些用于调整TCP keepalive的设置（如tcp_keepalives_interval等），您可能还需要进行一些内核/网络配置，以确保它们实际上已启用。

例如，我刚刚尝试在这里连接到自己的机器，tcp_keepalives_interval默认为7200，即2小时。如果您的端口转发在30分钟后断开连接，那么这个默认值就不够用了。您可以覆盖客户端连接字符串中使用的设置（假设您可以直接调整conninfo字符串），或者在用户/数据库属性或postgresql.conf中设置GUC变量。

请参见：

• http://www.postgresql.org/docs/9.0/static/runtime-config-connection.html - 服务器设置
• http://www.postgresql.org/docs/9.0/static/libpq-connect.html - 客户端连接字符串设置

- araqnid

Answer 2

如果要插入数百万行数据，我建议参考官方指南并考虑使用复制功能。

- dfichter

Answer 3

我有一个Django管理命令，可以更新成千上万行数据。一段时间后，我看到了同样的错误。我相信内存使用超过了限制。虽然不知道如何在命令中手动控制事务。

- Georgie Porgie