C# - GPDB 커넥션 에러

npgsql 4.0.10 버전 사용

 

에러 메시지

npgsql.npgsqlexception(0x80004005): exception while reading from stream --> system.io.ioexception : 전송 연결 연결된 구성원으로부터 응답이 없거나, 끊어졌습니다

 

npgsqlexception ?

.NET 애플리케이션에서 PostgreSQL 데이터베이스와의 통신 중 발생하는 예외

데이터베이스와의 연결 문제, 쿼리 실행 오류, 서버 응답 오류 등..

 

IOException ?

.NET 또는 Java와 같은 프로그래밍 환경에서 입출력(I/O) 작업 중에 발생하는 예외.

파일, 네트워크 스트림, 디바이스 등의 읽기, 쓰기, 열기, 닫기 등의 작업에서 오류가 발생할 때 던져진다.

 

---

 

https://help.santesuite.org/knowledgebase/sdb-kb/kb008-postgresql-connections-fail-with-block-message

PostgreSQL connections fail with block message | SanteSuite Help Portal

 

This is very common when hosting the IMS on AWS or other hosted providers which limit the number of concurrent connections to the server.

 

solutions

 

• You can increase the number of maximum connections allowed to the database. This can be done by upgrading the AWS RDS instance, or (if not hosting on AWS) increasing the maximum connections settings in postgres.config

• You can force-disconnect all clients on your database by running the following command

 

select pg_terminate_backend(pid)
from pg_stat_activity
where datname = 'yourdatabasename'

 

 

---

 

https://stackoverflow.com/questions/76879239/etl-process-with-npgsql-source-suddenly-started-crashing

ETL process with Npgsql source suddenly started crashing

 

After noticing some regularity, setting CommandTimeout=0 in SSIS on source helped.

 

---

 

https://stackoverflow.com/questions/73412166/exception-while-reading-from-stream-system-io-ioexception

Exception while reading from stream ---> System.IO.IOException

 

• Session had hit an inactivity timeout and was terminated. ==> You can try adding/changing CommandTimeout parameter to more than default of 30 seconds.
• Connection used an authentication token, which had expired.
• Database connection listener service had been restarted, possibly due to patching or automated code deployment. ==> You could add code to reconnect and run a simple "heartbeat" query like SELECT CURRENT_DATE;

It seems like your ssl is not working. You might need the certificate to connection via https to the DB

 

---

 

https://stackoverflow.com/questions/55397025/receiving-system-io-ioexception-when-trying-to-delete-an-entry

Receiving System.IO.IOException when trying to delete an entry

 

Per the exception, this issue is related to a timeout of a long running script. You didn't include a code sample so I can't specifically provide the syntax to increase this but setting the commandTimeout = 0 will disable timeout and will resolve the problem.

To address your question in the comments,

Is there any way how I can investigate why it takes such a long time?

It could just be deleting a large amount of records. You should run your queries outside of C# to ensure queries are optimized before inserting them into code.

Deleting large sets of records can take a while, you should look into truncate or some other purging design such as dumping into temporary tables, etc.

 

---

 

질문 올렸는데 답변이 달릴지 모르겠음 (답변이 다 옛날 글이여)

 

receiving system.io.ioexception when i try to connect postgresql db (using IIS) - Stack Overflow