0

I have a python code that runs simulations and writes the results to Postgres table. I have several machines on the network, But only the first one runs without any problems. other machines have connection issues after some time, although the code is exactly the same on all devices.

this is the code that ı use for connection:

##CONNECTION 
global postgre_conn
global postgre_engine
global postgre_cursor
p_conn_str = f"postgresql+psycopg2://Username_X:[email protected]/SIMDB?options=--search_path=db_schema"
postgre_conn = psycopg2.connect(
                            host="192.168.X.X",
                            database="SIMDB",
                            user="username_X",
                            password="Password",
                            port="XXXX",
                            sslmode='disable',
                            connect_timeout=90000000
                            )
 postgre_engine = create_engine(p_conn_str)
 postgre_cursor = postgre_conn.cursor()

the function that I use for storing data to db:

def write_df_to_db(df, table, db_name, db_schema, db_type, col_list=None):
result = False
global postgre_conn
global postgre_engine
global postgre_cursor
for i in range(5):
    try:
        df.to_sql(table, postgre_engine, index=False, schema=db_schema, if_exists='append', method='multi', chunksize=10000) #'replace',
        break

    except Exception as e:
        print(e)
        print(f'Trying file ({i})', f'{table}', datetime.datetime.now(), str(e))
        time.sleep(5)
        continue

if result == False:
    print('Error while storing to DB')
    sim_id = df.loc[0, 'sim_id']
    batch_id = df.loc[0, 'batch_id']
    order_ref_no = df.loc[0, 'order_ref_no']
    df.to_csv(f"{result_path}{table}_{sim_id}_{batch_id}_{order_ref_no}.csv", header=True, index=False)

This is from log file of ubuntu machine that Postgres runs. I am 100% sure there is no network issue what so ever. all machines are on the same lan connected to a gigabit hub, static ip's. Please any help will be appreciated.

2024-06-18 15:58:57.516 UTC [1778978] bbot_rt_1@SIMDB FATAL:  canceling authentication due to timeout
2024-06-18 16:52:58.506 UTC [1823873] bbot_rt_1@SIMDB FATAL:  canceling authentication due to timeout
2024-06-18 18:40:19.898 UTC [1852956] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1666556] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852896] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852912] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852908] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852909] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852911] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852916] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852913] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852907] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852910] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 19:41:11.483 UTC [1852914] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection timed out
2024-06-18 20:40:32.065 UTC [1959699] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection reset by peer
2024-06-18 20:40:32.067 UTC [1959658] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection reset by peer
2024-06-18 20:40:32.070 UTC [1959690] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection reset by peer
2024-06-18 20:40:32.070 UTC [1959708] bbot_rt_1@SIMDB LOG:  could not receive data from client: Connection reset by peer
4
  • Have you investigated the Postgres logs? Commented Jun 26 at 13:05
  • "there is no network issue what so ever" -- Postgres apparently thinks otherwise
    – mustaccio
    Commented Jun 26 at 13:09
  • @Lennart-SlavaUkraini, Can you tell me which logs should I check? I only know /var/log/postgresql/postgresql-14-main.log /
    – HakanA
    Commented Jun 26 at 14:40
  • That should do. If you don't find anything try connecting to the postgres port with telnet, netcat or similar, to ensure that you can connect. Commented Jun 27 at 11:58

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.