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
