Re: Error: server closed the connection unexpectedly
On 10/9/21 23:18, Ron wrote: On 10/9/21 3:26 PM, Adrian Klaver wrote: On 10/9/21 9:01 AM, Anil wrote: Hi Team, I am using Postgres (AWS instance, version: PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit) with python for programming and pgAdmin for querying. However facing below error very often but did not see any aborted information in log (in server activity, pdAdmin dashboard). Tried to get support from open community but no luck so writing to you, could you help me? What does the actual Postgres log show? Adrian, I've been getting the same error for two months from psql 12.5 on an EC2 Linux host, trying to connect to an RDS Postgresql 12.7 instance, after months of working properly. [a467197@uisawsprdlinrds ~]$ psql postgres psql: error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Unfortunately this message is from the wrong end of the transaction. You and the OP need to look on the server end for the cause. I would start with the Postgres server log and the OS system logs. -- Angular momentum makes the world go 'round. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Error: server closed the connection unexpectedly
On 10/10/21 10:19, Anil wrote: Please reply to list also. Ccing list. For future reference do not include images for textual information. Copy and paste the errors as text. Response in line. Thanks for the response. 1. I am facing with both (pgAdmin and in python). pgAdmin is throwing this window. image.png 2. Python is throwing below error very often, so I had to create a pool object again, which is slowing down my application 45 times. *OperationalError: server closed the connection unexpectedly * *This probably means the server terminated abnormally before or while processing the request.* 3. I don't see anything in log (from pgAdmin). image.png I tried to use the connection pool but it was still the same. Could you help? Per my response to Ron, the answer to this is going to be found on the server side not the client side. You need to look at the Postgres server log as well as the OS system log. Thanks, Anil On Sun, Oct 10, 2021 at 1:56 AM Adrian Klaver wrote: On 10/9/21 9:01 AM, Anil wrote: > Hi Team, > > I am using Postgres (AWS instance, version: PostgreSQL 12.7 on > x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat > 4.8.3-9), 64-bit) with python for programming and pgAdmin for querying. > However facing below error very often but did not see any aborted > information in log (in server activity, pdAdmin dashboard). Tried to get > support from open community but no luck so writing to you, could you > help me? What does the actual Postgres log show? When does the error occur, with the Python program, the pgAdmin query or both? > > *OperationalError: server closed the connection unexpectedly > * > *This probably means the server terminated abnormally > before or while processing the request.* > > Thanks, > Anil -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: Error: server closed the connection unexpectedly
On 10/10/21 11:27 AM, Adrian Klaver wrote: On 10/9/21 23:18, Ron wrote: On 10/9/21 3:26 PM, Adrian Klaver wrote: On 10/9/21 9:01 AM, Anil wrote: Hi Team, I am using Postgres (AWS instance, version: PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit) with python for programming and pgAdmin for querying. However facing below error very often but did not see any aborted information in log (in server activity, pdAdmin dashboard). Tried to get support from open community but no luck so writing to you, could you help me? What does the actual Postgres log show? Adrian, I've been getting the same error for two months from psql 12.5 on an EC2 Linux host, trying to connect to an RDS Postgresql 12.7 instance, after months of working properly. [a467197@uisawsprdlinrds ~]$ psql postgres psql: error: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Unfortunately this message is from the wrong end of the transaction. You and the OP need to look on the server end for the cause. I would start with the Postgres server log and the OS system logs. I think Anil mentioned that there's *nothing* in the Postgresql server log, even (in my case) with log_min_messages cranked to debug5. And, of course, we can't see the RDS system logs. -- Angular momentum makes the world go 'round.
Re: Error: server closed the connection unexpectedly
Ron writes: > I think Anil mentioned that there's *nothing* in the Postgresql server log, > even (in my case) with log_min_messages cranked to debug5. I'm skeptical that you're looking in the right place, then. What do you get from show log_destination; show log_directory; regards, tom lane
Re: Error: server closed the connection unexpectedly
On 10/10/21 2:20 PM, Tom Lane wrote: Ron writes: I think Anil mentioned that there's *nothing* in the Postgresql server log, even (in my case) with log_min_messages cranked to debug5. I'm skeptical that you're looking in the right place, then. What do you get from show log_destination; show log_directory; This is AWS RDS Postgresql. The db server log is accessed via a web interface, and is /the/ postgresql log just like you see in vanilla Postgresql. I've attached it. My attempt to connect happened some time between between 04:10:30 and 04:17:30. regards, tom lane -- Angular momentum makes the world go 'round. 2021-10-03 04:01:06 UTC::@:[1721]:LOG: checkpoint starting: time 2021-10-03 04:01:30 UTC::@:[1721]:LOG: checkpoint complete: wrote 235 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=23.706 s, sync=0.004 s, total=23.733 s; sync files=73, longest=0.004 s, average=0.001 s; distance=66469 kB, estimate=68389 kB 2021-10-03 04:06:06 UTC::@:[1721]:LOG: checkpoint starting: time 2021-10-03 04:10:21 UTC::@:[1714]:LOG: received SIGHUP, reloading configuration files 2021-10-03 04:10:21 UTC::@:[1714]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:21 UTC::@:[1714]:LOG: parameter "log_min_error_statement" changed to "debug5" 2021-10-03 04:10:21 UTC::@:[1719]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:21 UTC::@:[1723]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:21 UTC::@:[1727]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:21 UTC::@:[1722]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:21 UTC::@:[1726]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:21 UTC::@:[1724]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:21 UTC::@:[1725]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:21 UTC::@:[1721]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:10:36 UTC::@:[1721]:LOG: checkpoint complete: wrote 6166 buffers (0.1%); 0 WAL file(s) added, 0 removed, 2 recycled; write=269.121 s, sync=0.008 s, total=269.152 s; sync files=172, longest=0.008 s, average=0.001 s; distance=64636 kB, estimate=68014 kB 2021-10-03 04:16:06 UTC::@:[1721]:LOG: checkpoint starting: time 2021-10-03 04:16:12 UTC::@:[1721]:LOG: checkpoint complete: wrote 56 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=5.788 s, sync=0.007 s, total=5.816 s; sync files=39, longest=0.004 s, average=0.001 s; distance=130865 kB, estimate=130865 kB 2021-10-03 04:17:40 UTC::@:[1714]:LOG: received SIGHUP, reloading configuration files 2021-10-03 04:17:40 UTC::@:[1714]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:17:40 UTC::@:[1714]:LOG: parameter "log_min_error_statement" changed to "info" 2021-10-03 04:17:40 UTC::@:[1719]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:17:40 UTC::@:[1724]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:17:40 UTC::@:[1723]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:17:40 UTC::@:[1722]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:17:40 UTC::@:[1726]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:17:40 UTC::@:[1725]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:17:40 UTC::@:[1721]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:17:40 UTC::@:[1727]:LOG: skipping missing configuration file "/rdsdbdata/config/recovery.conf" 2021-10-03 04:21:06 UTC::@:[1721]:LOG: checkpoint starting: time 2021-10-03 04:21:07 UTC::@:[1721]:LOG: checkpoint complete: wrote 8 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.958 s, sync=0.006 s, total=0.986 s; sync files=5, longest=0.006 s, average=0.002 s; distance=65543 kB, estimate=124333 kB 2021-10-03 04:26:06 UTC::@:[1721]:LOG: checkpoint starting: time 2021-10-03 04:26:28 UTC::@:[1721]:LOG: checkpoint complete: wrote 215 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=21.796 s, sync=0.006 s, total=21.823 s; sync files=117, longest=0.006 s, average=0.001 s; distance=65729 kB, estimate=118473 kB 2021-10-03 04:31:06 UTC::@:[1721]:LOG: checkpoint starting: time 2021-10-03 04:31:29 UTC::@:[1721]:LOG: checkpoint complete: wrote 230 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=23.305 s, sync=0.013 s, total=23.339 s; sync files=89, longest=0.013 s, average=0.001 s; distance=65945 kB, estimate=113220 kB 2021-10-03 04:36:
Re: Error: server closed the connection unexpectedly
Ron writes: > This is AWS RDS Postgresql. The db server log is accessed via a web > interface, and is /the/ postgresql log just like you see in vanilla > Postgresql. > I've attached it. My attempt to connect happened some time between between > 04:10:30 and 04:17:30. Hm. Not much there. You might try turning on log_connections to be sure, but what this looks like is that the connection request never got to the server. Maybe you've got some middleware involved, like pgpool or some other connection pooler? regards, tom lane
Re: Error: server closed the connection unexpectedly
On 10/10/21 3:00 PM, Tom Lane wrote: Ron writes: This is AWS RDS Postgresql. The db server log is accessed via a web interface, and is /the/ postgresql log just like you see in vanilla Postgresql. I've attached it. My attempt to connect happened some time between between 04:10:30 and 04:17:30. Hm. Not much there. You might try turning on log_connections to be sure, I'll do that. but what this looks like is that the connection request never got to the server. Maybe you've got some middleware involved, like pgpool or some other connection pooler? If so, it must be something hidden by AWS, because I never installed it. The crazy thing is that coworkers with accounts on the same VM can connect to the instance just fine. (Yes, we use the exact same command to connect, as a test.) -- Angular momentum makes the world go 'round.
Re: Error: server closed the connection unexpectedly
On 10/10/21 12:30, Ron wrote: On 10/10/21 2:20 PM, Tom Lane wrote: Ron writes: I think Anil mentioned that there's *nothing* in the Postgresql server log, even (in my case) with log_min_messages cranked to debug5. I'm skeptical that you're looking in the right place, then. What do you get from show log_destination; show log_directory; This is AWS RDS Postgresql. The db server log is accessed via a web interface, and is /the/ postgresql log just like you see in vanilla Postgresql. I've attached it. My attempt to connect happened some time between between 04:10:30 and 04:17:30. Is that you sending the SIGHUP's? regards, tom lane -- Angular momentum makes the world go 'round. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Error: server closed the connection unexpectedly
On 10/10/21 13:06, Ron wrote: On 10/10/21 3:00 PM, Tom Lane wrote: Ron writes: This is AWS RDS Postgresql. The db server log is accessed via a web interface, and is /the/ postgresql log just like you see in vanilla Postgresql. I've attached it. My attempt to connect happened some time between between 04:10:30 and 04:17:30. Hm. Not much there. You might try turning on log_connections to be sure, I'll do that. but what this looks like is that the connection request never got to the server. Maybe you've got some middleware involved, like pgpool or some other connection pooler? If so, it must be something hidden by AWS, because I never installed it. The crazy thing is that coworkers with accounts on the same VM can connect to the instance just fine. (Yes, we use the exact same command to connect, as a test.) Are the AWS security groups set up correctly. See here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.Connecting -- Adrian Klaver adrian.kla...@aklaver.com
Re: Error: server closed the connection unexpectedly
On 10/10/21 3:12 PM, Adrian Klaver wrote: On 10/10/21 12:30, Ron wrote: On 10/10/21 2:20 PM, Tom Lane wrote: Ron writes: I think Anil mentioned that there's *nothing* in the Postgresql server log, even (in my case) with log_min_messages cranked to debug5. I'm skeptical that you're looking in the right place, then. What do you get from show log_destination; show log_directory; This is AWS RDS Postgresql. The db server log is accessed via a web interface, and is /the/ postgresql log just like you see in vanilla Postgresql. I've attached it. My attempt to connect happened some time between between 04:10:30 and 04:17:30. Is that you sending the SIGHUP's? Kinda. It's the AWS version of "pg_ctl reload" after modifying dynamic entries in the parameter group (aka postgresql.conf). -- Angular momentum makes the world go 'round.
Re: Error: server closed the connection unexpectedly
Hi Adrian/Ron, Please find the server log, if you can find something (due to some reasons not being able to get OS system log, will be shared soon). Thanks, Anil On Sun, Oct 10, 2021 at 10:56 PM Adrian Klaver wrote: > On 10/10/21 10:19, Anil wrote: > Please reply to list also. > Ccing list. > > For future reference do not include images for textual information. Copy > and paste the errors as text. > > Response in line. > > Thanks for the response. > > > > 1. I am facing with both (pgAdmin and in python). pgAdmin is throwing > > this window. > > image.png > > 2. Python is throwing below error very often, so I had to create a > > pool object again, which is slowing down my application 45 times. > > *OperationalError: server closed the connection unexpectedly > > * > > *This probably means the server terminated abnormally > > before or while processing the request.* > > > > 3. I don't see anything in log (from pgAdmin). > > image.png > > > > I tried to use the connection pool but it was still the same. Could > > you help? > > Per my response to Ron, the answer to this is going to be found on the > server side not the client side. > > You need to look at the Postgres server log as well as the OS system log. > > > > > > Thanks, > > Anil > > > > > > On Sun, Oct 10, 2021 at 1:56 AM Adrian Klaver > > wrote: > > > > On 10/9/21 9:01 AM, Anil wrote: > > > Hi Team, > > > > > > I am using Postgres (AWS instance, version: PostgreSQL 12.7 on > > > x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat > > > 4.8.3-9), 64-bit) with python for programming and pgAdmin for > > querying. > > > However facing below error very often but did not see any aborted > > > information in log (in server activity, pdAdmin > > dashboard). Tried to get > > > support from open community but no luck so writing to you, could > > you > > > help me? > > > > What does the actual Postgres log show? > > > > When does the error occur, with the Python program, the pgAdmin query > > or both? > > > > > > > > *OperationalError: server closed the connection unexpectedly > > > * > > > *This probably means the server terminated abnormally > > > before or while processing the request.* > > > > > > Thanks, > > > Anil > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > 2021-10-10 14:03:04 UTC::@:[7159]:LOG: checkpoint starting: time 2021-10-10 14:03:04 UTC::@:[7159]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.102 s, sync=0.003 s, total=0.116 s; sync files=1, longest=0.003 s, average=0.003 s; distance=65536 kB, estimate=65542 kB 2021-10-10 14:08:04 UTC::@:[7159]:LOG: checkpoint starting: time 2021-10-10 14:08:05 UTC::@:[7159]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.102 s, sync=0.002 s, total=0.118 s; sync files=1, longest=0.002 s, average=0.002 s; distance=65535 kB, estimate=65541 kB 2021-10-10 14:12:50 UTC:185.220.205.213(35246):[unknown]@[unknown]:[25190]:LOG: invalid length of startup packet 2021-10-10 14:13:04 UTC::@:[7159]:LOG: checkpoint starting: time 2021-10-10 14:13:04 UTC::@:[7159]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.102 s, sync=0.003 s, total=0.116 s; sync files=1, longest=0.003 s, average=0.003 s; distance=65536 kB, estimate=65541 kB 2021-10-10 14:16:40 UTC:45.146.164.110(42488):pgadmin@postgres:[28502]:FATAL: password authentication failed for user "pgadmin" 2021-10-10 14:16:40 UTC:45.146.164.110(42488):pgadmin@postgres:[28502]:DETAIL: Role "pgadmin" does not exist. Connection matched pg_hba.conf line 13: "host all all all md5" 2021-10-10 14:18:04 UTC::@:[7159]:LOG: checkpoint starting: time 2021-10-10 14:18:04 UTC::@:[7159]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.102 s, sync=0.004 s, total=0.117 s; sync files=1, longest=0.004 s, average=0.004 s; distance=65536 kB, estimate=65540 kB 2021-10-10 14:23:04 UTC::@:[7159]:LOG: checkpoint starting: time 2021-10-10 14:23:05 UTC::@:[7159]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.102 s, sync=0.004 s, total=0.137 s; sync files=1, longest=0.004 s, average=0.004 s; distance=65536 kB, estimate=65540 kB 2021-10-10 14:28:04 UTC::@:[7159]:LOG: checkpoint starting: time 2021-10-10 14:28:04 UTC::@:[7159]:LOG: checkpoint complete: wrote 6 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.607 s, sync=0.003 s, total=0.619 s; sync files=6, longest=0.003 s, average=0.001 s; distance=65560 kB, estimate=65560 kB 2021-10-10 14:33:04 UTC::@:[7159]:LOG: checkpoint starting: time 2021-10-10 14:33:04 UTC::@:[7159]:LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 remov
