使用Psycopg2几百秒后，Postgres在查询期间关闭连接 [英] Postgres closes connection during query after a few hundred seconds when using Psycopg2

问题描述

我正在运行PostgreSQL 9.6（在Docker中，使用postgres：9.6.13映像）和psycopg2 2.8.2。

I'm running PostgreSQL 9.6 (in Docker, using the postgres:9.6.13 image) and psycopg2 2.8.2.

我的PostgreSQL服务器（本地）主机两个数据库。我的目标是在一个数据库中创建物化视图，该数据库使用Postgres的外部数据包装器使用另一个数据库中的数据。我可以使用使用psycopg2的Python脚本来完成所有这些操作。

My PostgreSQL server (local) hosts two databases. My goal is to create materialized views in one of the databases that use data from the other database using Postgres's foreign data wrappers. I do all this from a Python script that uses psycopg2.

只要创建实例化视图的时间不会太长（例如，如果要导入的文件不太大）。但是，如果该过程花费的时间超过大约250秒，则psycopg2会引发异常

This works well as long as creating the materialized view does not take too long (i.e. if the amount of data being imported isn't too large). However, if the process takes longer than roughly ~250 seconds, psycopg2 throws the exception

psycopg2.OperationalError: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

在Postgres的日志中找不到错误消息（或与此相关的任何消息）。

No error message (or any message concerning this whatsoever) can be found in Postgres's logs.

如果从SQL客户端（Postico）执行，则材料化视图创建成功完成。

Materialized view creation completes successfully if I do it from an SQL client (Postico).

此代码大致说明了我我正在使用Python脚本进行操作：

This code illustrates roughly what I'm doing in the Python script:

db = pg.connect(
    dbname=config.db_name,
    user=config.db_user,
    password=config.db_password,
    host=config.db_host,
    port=config.db_port
)
with db.cursor() as c:
    c.execute("""
        CREATE EXTENSION IF NOT EXISTS postgres_fdw;
        CREATE SERVER fdw FOREIGN DATA WRAPPER postgres_fdw OPTIONS (...);
        CREATE USER MAPPING FOR CURRENT_USER SERVER fdw OPTIONS (...);
        CREATE SCHEMA foreign;
        IMPORT FOREIGN SCHEMA foreign_schema FROM SERVER fdw INTO foreign;
    """)
    c.execute("""
        CREATE MATERIALIZED VIEW IF NOT EXISTS my_view AS (
            SELECT (...)
            FROM foreign.foreign_table
        );
    """)

推荐答案

添加<调用 psycopg2.connect 的code> keepalive 参数似乎已经解决了问题：

Adding the keepalive parameters to the psycopg2.connect call seems to have solved the problem:

self.db = pg.connect(
            dbname=config.db_name,
            user=config.db_user,
            password=config.db_password,
            host=config.db_host,
            port=config.db_port,
            keepalives=1,
            keepalives_idle=30,
            keepalives_interval=10,
            keepalives_count=5
        )

我还是不知道为什么这是必要的。我找不到其他描述过在Docker中使用Postgres时必须使用 keepalives 参数关键字的人，以便能够运行花费较长时间的查询超过4-5分钟，但也许很明显没有人注意到它？

I still don't know why this is necessary. I can't find anyone else who has described having to use the keepalives parameter keywords when using Postgres in Docker just to be able to run queries that take longer than 4-5 minutes, but maybe it's obvious enough that nobody has noted it?

这篇关于使用Psycopg2几百秒后，Postgres在查询期间关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！