LOG: could not send data to client: Broken pipe
Hello Postgres Support Team, I need your help to identify the issue and apply the fix for it. My client is running one query through Application and getting error like below. 11:20:46.298 [http-nio-8083-exec-7] ERROR c.s.s.g.a.alcor.pdi.dao.PdiDao - While getting pdi from 2019-12-21 to 2020-06-01: An I/O error occurred while sending to the backend. org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. When I checked in the postgres log I noticed the below. 2020-06-02 02:09:21.333 CEST db:[xxx] user:[x] [xx(53643)] [08006] LOG: could not send data to client: Broken pipe 2020-06-02 02:09:21.333 CEST db:[xxx] user:[x] [(53643)] [08006] STATEMENT: select . (it just a select query) 2020-06-02 02:09:21.333 CEST db:[xxx] user:[xx] [xx(53643)] [08006] FATAL: connection to client lost Note: DB name, hostname, username, sql text morphed in above message for security reasons, so kindly consider it. I ran the above select query from psql prompt and I got the output, Time: 7178.926 ms. Ofcource my one is local connection so no broken pipe, where as client is using the network so he is getting broken pipe. We checked with Network team they are claiming no issues with the network, But not sure. I worked with Unix team and they raised a case to Red hat support and got confirmation from them that "no issues observed from OS End" In-fact Red hat looking for a feedback of Postgres Community. Version of Postgres : PostgreSQL 9.6.5 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit OS : Red Hat Enterprise Linux Server release 7.2 (Maipo) We observed "LOG: could not send data to client: Broken pipe" in postgres log from long time , But recently client complained about it. So we are in process of fixing it for which we need your help. Fyi.. I had performed VACUUM(FULL, ANALYZE, VERBOSE) on the table. So the last analyzed is latest. I already checked in Postgres archive and found below links on the same topic, But I could not get the concreate answer to fix the issue, so please help. https://www.postgresql.org/search/?m=1&q=LOG%3A++could+not+send+data+to+client%3A+Broken+pipe&l=2&d=-1&s=i checked 11,12,13,14,15,16,17,18 mail replies Let me know if you need any more details from my end. Regards, Narresh = Ce message et toutes les pieces jointes (ci-apres le "message") sont confidentiels et susceptibles de contenir des informations couvertes par le secret professionnel. Ce message est etabli a l'intention exclusive de ses destinataires. Toute utilisation ou diffusion non autorisee interdite. Tout message electronique est susceptible d'alteration. La SOCIETE GENERALE et ses filiales declinent toute responsabilite au titre de ce message s'il a ete altere, deforme falsifie. = This message and any attachments (the "message") are confidential, intended solely for the addresses, and may contain legally privileged information. Any unauthorized use or dissemination is prohibited. E-mails are susceptible to alteration. Neither SOCIETE GENERALE nor any of its subsidiaries or affiliates shall be liable for the message if altered, changed or falsified. =
RE: LOG: could not send data to client: Broken pipe
Hello Adrian Klaver, Here is the answers What version of JDBC? : 9.3-1104-jdbc41 What is the application stack? : Java Client OS? : windows 2016 What is the network distance between the client/application and the server? Reply from 184.6.160.214: bytes=32 time=2ms TTL=57 Reply from 184.6.160.214: bytes=32 time=2ms TTL=57 Reply from 184.6.160.214: bytes=32 time=2ms TTL=57 Reply from 184.6.160.214: bytes=32 time=2ms TTL=57 Ping statistics for 184.6.160.214: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 2ms, Maximum = 2ms, Average = 2ms Regards, Narresh -Original Message- From: Adrian Klaver Sent: Tuesday, June 2, 2020 7:50 PM To: TALLURI Nareshkumar SgscGtsPasDre ; pgsql-gene...@postgresql.org Subject: Re: LOG: could not send data to client: Broken pipe On 6/1/20 8:41 PM, TALLURI Nareshkumar wrote: > Hello Postgres Support Team, > > I need your help to identify the issue and apply the fix for it. > > My client is running one query through Application and getting error > like below. > > 11:20:46.298 [http-nio-8083-exec-7] ERROR > c.s.s.g.a.alcor.pdi.dao.PdiDao > - While getting pdi from 2019-12-21 to 2020-06-01: An I/O error > occurred while sending to the backend. > > org.postgresql.util.PSQLException: An I/O error occurred while sending > to the backend. > Should have included in previous post: What version of JDBC? What is the application stack? Client OS? What is the network distance between the client/application and the server? -- Adrian Klaver adrian.kla...@aklaver.com
RE: LOG: could not send data to client: Broken pipe
Hello Adrian, What is the application stack? - I will check with Application team and get back to you. When I see Windows and interruptions to Postgres I think anti-virus software. Is there any running on the client? yes , symantec Are the client and server on the same network? yes Are the client/server separated by a building, country, etc? same country , But different Datacenter Regards, Narresh -Original Message- From: Adrian Klaver Sent: Wednesday, June 3, 2020 12:38 AM To: TALLURI Nareshkumar SgscGtsPasDre ; pgsql-gene...@postgresql.org Subject: Re: LOG: could not send data to client: Broken pipe On 6/2/20 11:18 AM, TALLURI Nareshkumar wrote: > Hello Adrian Klaver, > > Here is the answers > > > What version of JDBC? : 9.3-1104-jdbc41 > > What is the application stack?: Java That is the programming language. I was looking for the actual applications used. > > Client OS?: windows 2016 When I see Windows and interruptions to Postgres I think anti-virus software. Is there any running on the client? > > What is the network distance between the client/application and the server? I should have been more specific: Are the client and server on the same network? Are the client/server separated by a building, country, etc? > Reply from 184.6.160.214: bytes=32 time=2ms TTL=57 Reply from > 184.6.160.214: bytes=32 time=2ms TTL=57 Reply from 184.6.160.214: > bytes=32 time=2ms TTL=57 Reply from 184.6.160.214: bytes=32 time=2ms > TTL=57 > > Ping statistics for 184.6.160.214: > Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate > round trip times in milli-seconds: > Minimum = 2ms, Maximum = 2ms, Average = 2ms > > > Regards, > Narresh > > > > > -Original Message----- > From: Adrian Klaver > Sent: Tuesday, June 2, 2020 7:50 PM > To: TALLURI Nareshkumar SgscGtsPasDre > ; pgsql-gene...@postgresql.org > Subject: Re: LOG: could not send data to client: Broken pipe > > On 6/1/20 8:41 PM, TALLURI Nareshkumar wrote: >> Hello Postgres Support Team, >> >> I need your help to identify the issue and apply the fix for it. >> >> My client is running one query through Application and getting error >> like below. >> >> 11:20:46.298 [http-nio-8083-exec-7] ERROR >> c.s.s.g.a.alcor.pdi.dao.PdiDao >> - While getting pdi from 2019-12-21 to 2020-06-01: An I/O error >> occurred while sending to the backend. >> >> org.postgresql.util.PSQLException: An I/O error occurred while >> sending to the backend. >> > > Should have included in previous post: > > What version of JDBC? > > What is the application stack? > > Client OS? > > What is the network distance between the client/application and the server? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Adrian Klaver adrian.kla...@aklaver.com
psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Hello Postgres Support Team, Today we have an outage, our DB was wend down due to 100% space full at FS. We added space and could able to bring the cluster. DB version: psql (PostgreSQL) 10.12 OS version : Red Hat Enterprise Linux Server release 7.8 (Maipo) [0]postgres@axmdevhkdb008$ [PHKGAXMD008] pg_ctl start -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck waiting for server to start2020-07-14 08:50:42.273 CEST db:[] user:[] [] [0] LOG: listening on IPv4 address "176.5.88.68", port 5433 2020-07-14 08:50:42.278 CEST db:[] user:[] [] [0] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433" 2020-07-14 08:50:42.280 CEST db:[] user:[] [] [0] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [0] LOG: redirecting log output to logging collector process 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [0] HINT: Future log output will appear in directory "log". done server started __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base [0]postgres@axmdevhkdb008$ [PHKGAXMD008] psql psql: FATAL: database "postgres" does not exist __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base [2]postgres@axmdevhkdb008$ [PHKGAXMD008] ps -ef|grep postgres root 16567 16513 0 14:12 pts/100:00:00 sudo su - postgres root 16569 16567 0 14:12 pts/100:00:00 su - postgres postgres 16571 16569 0 14:12 pts/100:00:00 -ksh postgres 23888 1 0 14:50 pts/100:00:00 /opt/rh/rh-postgresql10/root/usr/bin/postgres -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck postgres 23890 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: logger process postgres 23892 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: checkpointer process postgres 23893 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: writer process postgres 23894 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: wal writer process postgres 23895 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: autovacuum launcher process postgres 23896 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: stats collector process postgres 23897 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: bgworker: logical replication launcher postgres 24689 16571 0 14:55 pts/100:00:00 ps -ef postgres 24690 16571 0 14:55 pts/100:00:00 grep --color=auto postgres __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base We have the directories at Data location. [2]postgres@axmdevhkdb008$ [PHKGAXMD008] ls -lrt total 180 drwx-- 2 postgres postgres 4096 Jan 2 2020 1 drwx-- 2 postgres postgres 4096 Jan 2 2020 13211 drwx-- 2 postgres postgres 4096 Jul 13 09:51 pgsql_tmp drwx-- 2 postgres postgres 139264 Jul 13 21:02 16389 drwx-- 2 postgres postgres 12288 Jul 13 21:02 13212 drwx-- 2 postgres postgres 4096 Jul 14 11:08 13213 drwx-- 2 postgres postgres 12288 Jul 14 11:08 13214 __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base How to overcome this ? Note: we don't have any wal files, it is ok for us if we could bring it up with our recover. 2 nd one: Since we are not able to connect to DB , we did a new initialization and copy the directories from base directory to new base directory(in new data location) And update pg_catalog.pg_database table with the DB name (postgres@[local]:5433)# [PHKGAXMP003]INSERT INTO -- # pg_catalog.pg_database( -- # datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, -- # datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl) -- # VALUES( -- # -- Write Your collation -- # 'axiom', 10, 0, 'C', 'C', -- # False, True, -1, 16389, '536', '1', 1663, Null); INSERT 16384 1 Time: 70.239 ms (postgres@[local]:5433)# [PHKGAXMP003]select oid from pg_database a where a.datname = 'axiom'; oid --- 16384 (1 row) After this we could able to see the DB axiom. Name| Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |Description ---+--+---+-+---+---+-++ axiom | postgres | SQL_ASCII | C | C | | 16 GB | pg_default | postgres | postgres | SQL_ASCII | C | C | | 7647 kB | pg_default | default administrative connection database template0 | postgres | SQL_ASCII | C | C | =c/postgres +| 7513 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | || template1 | postgres | SQL_ASCII | C | C | =c/postgres +| 7513 kB | pg_default | default template for new databases | | | |
RE: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Hello Team, Can some one help us on this please , Actually we are blocked . Regards, Narresh From: TALLURI Nareshkumar SgscGtsPasDre Sent: Tuesday, July 14, 2020 5:20 PM To: pgsql-general@lists.postgresql.org Subject: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index" Hello Postgres Support Team, Today we have an outage, our DB was wend down due to 100% space full at FS. We added space and could able to bring the cluster. DB version: psql (PostgreSQL) 10.12 OS version : Red Hat Enterprise Linux Server release 7.8 (Maipo) [0]postgres@axmdevhkdb008$ [PHKGAXMD008] pg_ctl start -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck waiting for server to start2020-07-14 08:50:42.273 CEST db:[] user:[] [] [0] LOG: listening on IPv4 address "176.5.88.68", port 5433 2020-07-14 08:50:42.278 CEST db:[] user:[] [] [0] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433" 2020-07-14 08:50:42.280 CEST db:[] user:[] [] [0] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [0] LOG: redirecting log output to logging collector process 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [0] HINT: Future log output will appear in directory "log". done server started __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base [0]postgres@axmdevhkdb008$ [PHKGAXMD008] psql psql: FATAL: database "postgres" does not exist __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base [2]postgres@axmdevhkdb008$ [PHKGAXMD008] ps -ef|grep postgres root 16567 16513 0 14:12 pts/100:00:00 sudo su - postgres root 16569 16567 0 14:12 pts/100:00:00 su - postgres postgres 16571 16569 0 14:12 pts/100:00:00 -ksh postgres 23888 1 0 14:50 pts/100:00:00 /opt/rh/rh-postgresql10/root/usr/bin/postgres -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck postgres 23890 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: logger process postgres 23892 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: checkpointer process postgres 23893 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: writer process postgres 23894 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: wal writer process postgres 23895 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: autovacuum launcher process postgres 23896 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: stats collector process postgres 23897 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: bgworker: logical replication launcher postgres 24689 16571 0 14:55 pts/100:00:00 ps -ef postgres 24690 16571 0 14:55 pts/100:00:00 grep --color=auto postgres __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base We have the directories at Data location. [2]postgres@axmdevhkdb008$ [PHKGAXMD008] ls -lrt total 180 drwx-- 2 postgres postgres 4096 Jan 2 2020 1 drwx-- 2 postgres postgres 4096 Jan 2 2020 13211 drwx-- 2 postgres postgres 4096 Jul 13 09:51 pgsql_tmp drwx-- 2 postgres postgres 139264 Jul 13 21:02 16389 drwx-- 2 postgres postgres 12288 Jul 13 21:02 13212 drwx-- 2 postgres postgres 4096 Jul 14 11:08 13213 drwx-- 2 postgres postgres 12288 Jul 14 11:08 13214 __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base How to overcome this ? Note: we don't have any wal files, it is ok for us if we could bring it up with our recover. 2 nd one: Since we are not able to connect to DB , we did a new initialization and copy the directories from base directory to new base directory(in new data location) And update pg_catalog.pg_database table with the DB name (postgres@[local]:5433)# [PHKGAXMP003]INSERT INTO -- # pg_catalog.pg_database( -- # datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, -- # datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl) -- # VALUES( -- # -- Write Your collation -- # 'axiom', 10, 0, 'C', 'C', -- # False, True, -1, 16389, '536', '1', 1663, Null); INSERT 16384 1 Time: 70.239 ms (postgres@[local]:5433)# [PHKGAXMP003]select oid from pg_database a where a.datname = 'axiom'; oid --- 16384 (1 row) After this we could able to see the DB axiom. Name| Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |Description ---+--+---+-+---+---+-++ axiom | postgres | SQL_ASCII | C | C | | 16 GB | pg_default | postgres | postgres | SQL_ASCII | C | C |
