This is a bug tracker

Please complete the following information:

OS: MACOS 14.5 / Ubuntu 22.04
Psycopg version: psycopg2-binary 2.9.9
Python version: Python 3.10.11
PostgreSQL version: postgres (PostgreSQL) 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1)
pip version: pip 23.2.1

Describe the bug I am running simulations for my phd Thesis. A python code runs and make some calculations and stores them to Postgres database tables. I own 2 MACOS (same OS, same Python version) and 1 Ubuntu client which runs the same code with same python version and packages on same version .

1 client is on Wifi, 2 clients are on cable through gigabit switch. I did not see any network related issues on any of the clients (I can further check wrt to your suggestion)

When I start simulations on 3 clients, only one of them runs without any problems, the other 2 have problems on connecting on DB. It all starts at the same time, First client (MacBook Pro on Wifi) keeps running for days but other 2 clients runs for 2-3 simulations and then start getting connection errors and then got an connection exception and exits.

This is How I connect to database; I open a connection at the beginning and write data. I tried this part with a connection pool and result was the same. Furthermore this runs bore stable compared to connection pool. ` p_conn_str = f"postgresql+psycopg2://{os.getenv(os.getenv('sim_db'+suffix) + '_username')}:{os.getenv(os.getenv('sim_db'+suffix) + '_password')}@{os.getenv(os.getenv('sim_db'+suffix))}:5432/SIMDB?options=--search_path=bbot_sim_prd01"

Create PostgreSQL connection and engine

postgre_conn = psycopg2.connect( host=os.getenv(os.getenv('sim_db'+suffix)), database="SIMDB", user=os.getenv(os.getenv('sim_db'+suffix) + '_username'), password=os.getenv(os.getenv('sim_db'+suffix) + '_password'), port=os.getenv(os.getenv('sim_db'+suffix) + '_port'), sslmode='disable', connect_timeout=90000000 ) postgre_engine = create_engine(p_conn_str) postgre_cursor = postgre_conn.cursor() `

I tried to give separate users for each client, since I thought using same user from different ip might cause issues. Checked ports, firewall on all devices. (can further check wrt recommendations)

` local all postgres peer

TYPE DATABASE USER ADDRESS METHOD

"local" is for Unix domain socket connections only

local all all md5 local all all peer

IPv4 local connections:

host all all 0.0.0.0/0 md5 host all bbot_rt 0.0.0.0/0 md5 host all bbot_rt_1 0.0.0.0/0 md5 host all bbot_rt_2 0.0.0.0/0 md5 host all bbot_rt_3 0.0.0.0/0 md5 host all bbot_rt_4 0.0.0.0/0 md5 host all bbot_rt_5 0.0.0.0/0 md5 host all bbot_RT 0.0.0.0/0 md5

IPv6 local connections:

host all all ::1/128 scram-sha-256

Allow replication connections from localhost, by a user with the

replication privilege.

local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 `

This is the log that I am seeing: ` (Background on this error at: https://sqlalche.me/e/20/e3q8) retrying connection (psycopg2.OperationalError) connection to server at "192.168.149.201", port 5432 failed: Host is down Is the server running on that host and accepting TCP/IP connections?

(Background on this error at: https://sqlalche.me/e/20/e3q8) retrying connection (psycopg2.OperationalError) connection to server at "192.168.149.201", port 5432 failed: Host is down Is the server running on that host and accepting TCP/IP connections?

(Background on this error at: https://sqlalche.me/e/20/e3q8) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection Can't reconnect until invalid transaction is rolled back. Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b) retrying connection (psycopg2.OperationalError) 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.

(Background on this error at: https://sqlalche.me/e/20/e3q8) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Traceback (most recent call last): File "/Users/ha/PycharmProjects/BBX_SIM_V2/bbx_utils/db_utils.py", line 245, in update_into_mysql postgre_cursor.execute(qry_update) psycopg2.OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. `

this is from Postgres main.log 2024-07-24 23:41:28.855 UTC [9937] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 01:41:21.430 UTC [9950] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 05:02:30.294 UTC [9954] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-25 05:03:46.070 UTC [11455] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-25 05:03:46.070 UTC [9951] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-25 05:11:20.726 UTC [9949] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 05:11:20.726 UTC [9953] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 05:11:20.726 UTC [9948] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 05:11:20.726 UTC [9952] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 05:26:19.004 UTC [12356] bbot_rt_2@SIMDB LOG: could not send data to client: Connection reset by peer 2024-07-25 05:26:19.005 UTC [12356] bbot_rt_2@SIMDB FATAL: connection to client lost 2024-07-25 05:26:19.005 UTC [12357] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-25 05:26:19.005 UTC [12361] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-25 05:26:19.005 UTC [12361] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-25 07:22:25.046 UTC [12363] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 07:22:25.046 UTC [12362] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 07:22:25.046 UTC [12358] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 07:22:25.046 UTC [12360] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 13:09:39.350 UTC [12838] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-25 13:13:26.678 UTC [12834] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-25 13:14:42.455 UTC [12835] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-25 15:44:43.414 UTC [12840] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 15:44:43.414 UTC [12840] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-25 17:42:22.870 UTC [12836] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 17:42:22.870 UTC [12836] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-25 22:35:33.142 UTC [14718] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-25 22:35:33.151 UTC [14718] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-26 04:23:06.872 UTC [17926] bbot_rt_2@SIMDB LOG: could not send data to client: Connection reset by peer 2024-07-26 04:23:06.879 UTC [17926] bbot_rt_2@SIMDB FATAL: connection to client lost 2024-07-26 04:23:06.872 UTC [14720] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-26 04:23:06.880 UTC [14720] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-26 04:23:06.872 UTC [17568] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-26 04:23:06.880 UTC [17568] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-26 04:23:06.879 UTC [12839] bbot_rt_2@SIMDB LOG: could not send data to client: Connection reset by peer 2024-07-26 04:23:06.880 UTC [12839] bbot_rt_2@SIMDB FATAL: connection to client lost 2024-07-26 04:23:07.078 UTC [12837] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-26 04:23:07.078 UTC [12837] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-26 04:23:07.086 UTC [14719] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-26 04:23:07.086 UTC [14719] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-26 04:23:07.286 UTC [15010] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-26 04:23:07.286 UTC [15010] bbot_rt_2@SIMDB LOG: unexpected EOF on client connection with an open transaction 2024-07-26 13:38:55.510 UTC [19214] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-26 13:38:55.510 UTC [19116] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection reset by peer 2024-07-26 13:46:30.166 UTC [19113] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-26 13:46:30.166 UTC [19112] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-26 13:46:30.166 UTC [19114] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-26 13:46:30.166 UTC [19119] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-26 13:46:30.166 UTC [19115] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-26 13:46:30.166 UTC [19117] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out 2024-07-26 16:38:44.374 UTC [33181] bbot_rt_2@SIMDB LOG: could not receive data from client: Connection timed out

psycopg / psycopg2

connection problem from local clients to Postgres db via python #1717