Postgres Closes Connection During Query After A Few Hundred Seconds When Using Psycopg2

I'm running PostgreSQL 9.6 (in Docker, using the postgres:9.6.13 image) and psycopg2 2.8.2. My PostgreSQL server (local) hosts two databases. My goal is to create materialized view

Solution 1:

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
        )

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?

Solution 2:

It might be that PostgreSQL 9.6 kills your connections after the new timeout mentioned at https://stackoverflow.com/a/45627782/1587329. In that case, you could set

the statement_timeout in postgresql.conf

but it is not recommended.

It might work in Postico because the value has been set there.

To log an error you need to set log_min_error_statement to ERROR or lower for it to show.