PostgreSQL在大插入时意外关闭连接
我正在用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中会有一些隐藏的时间限制。
3 个回答
我有一个Django的管理命令，它会更新成千上万的行数据。过了一段时间，我就会看到同样的错误。我觉得是因为内存使用超过了限制。不过，我不知道怎么在命令中手动控制事务。
我不知道在PostgreSQL里有没有什么“隐藏”的超时设置。PostgreSQL确实有一个叫做statement_timeout的设置，如果你触发了这个超时，你应该会在服务器日志里看到ERROR: canceling statement due to statement timeout的错误信息（而且它也会记录被取消的语句）。至于psycopg2，我就不太清楚了。最好查看一下服务器日志，看看有没有相关的信息。
也许是网络问题？一个运行时间很长的语句会导致TCP连接长时间处于空闲状态。可能你的端口转发设置会清除那些空闲超过30分钟的连接？或者你的TCP连接没有使用保持活动（keepalive）功能。PostgreSQL有一些设置可以调整TCP的保持活动（比如tcp_keepalives_interval等），你可能还需要做一些内核或网络配置，确保这些设置是启用的。
例如，我刚刚尝试连接到我自己的机器，发现tcp_keepalives_interval的默认值是7200秒，也就是2小时。如果你的端口转发在30分钟后就断开，这个默认值就不够用了。你可以在客户端连接字符串中覆盖这个设置（假设你能直接修改连接信息字符串），或者在用户/数据库属性或postgresql.conf文件中设置这个GUC变量。
参考链接：