I have a project with 10 parsers and at the end have this code:

`

cursor = conn.cursor()
my_file = open(r'csv\file.csv')

sql_statement = """
            CREATE TEMP TABLE temp
            (
                LIKE vhcl
            )
            ON COMMIT DROP;

            COPY temp FROM STDIN WITH
                CSV
                HEADER
                DELIMITER AS ',';

            INSERT INTO vhcl
            SELECT *
            FROM temp
            ON CONFLICT (id) DO UPDATE SET name= EXCLUDED.name"""

cursor.copy_expert(sql=sql_statement, file=my_file)
conn.commit()
cursor.close()

` Everything worked fine until a couple of weeks ago I started to get these errors:

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

I noticed, that if parsers works (for example) less, than 10 minutes, I won't get those errors

I tried to make a separate function, that adds data to the DB after the parser ends working. It still gives me that error. The strange thing is that I ran my parsers on my home pc, and it works fine, also, if I add data manually with the same function, but in a different file, it also works fine.

I asked about banned IP for db, but it's okay. So I have no idea why I have this error.

PostgreSQL log

CodePudding user response：

You have a network problem.

Both the server and the client complain that the other side unexpectedly hung up on them. So it was some misconfigured network component in the middle that cut the line. You have two options:

• fix the network

• lower tcp_keepalives_idle on the PostgreSQL client or server, so that the operating system sends "keepalive packets" frequently and the network doesn't consider the connection idle

You might want to read this for more details.

CodePudding user response：

Finally, I found a solution. I still don't know what the problem was. It isn't a connection issue, cause some parsers with the same IP and same network connections work normally. And I'm was still able to add data with the same script, but in a separate project file.

My solution is to add 'keepalives' settings in connection:

conn = psycopg2.connect(
host=hostname,
dbname=database,
user=username,
password=password,
port=port_id,
keepalives=1,
keepalives_idle=30,
keepalives_interval=10,
keepalives_count=5)