Обсуждение: Error: server closed the connection unexpectedly
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?
OperationalError: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
before or while processing the request.
Thanks,
Anil
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.klaver@aklaver.com
On 10/9/21 3:26 PM, Adrian Klaver wrote:
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.
On 10/9/21 9:01 AM, Anil wrote:Adrian,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?
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.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
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.klaver@aklaver.com
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 > <adrian.klaver@aklaver.com> 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.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
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 <adrian.klaver@aklaver.com> 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
> <adrian.klaver@aklaver.com> 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.klaver@aklaver.com
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Вложения
On 10/10/21 11:27 AM, Adrian Klaver wrote:
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.
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:Adrian,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?
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.
Angular momentum makes the world go 'round.
Ron <ronljohnsonjr@gmail.com> 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
On 10/10/21 2:20 PM, Tom Lane wrote:
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.
Ron <ronljohnsonjr@gmail.com> 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.
Angular momentum makes the world go 'round.
Вложения
Ron <ronljohnsonjr@gmail.com> 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
On 10/10/21 3:00 PM, Tom Lane wrote: > Ron <ronljohnsonjr@gmail.com> 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.
On 10/10/21 12:30, Ron wrote: > On 10/10/21 2:20 PM, Tom Lane wrote: >> Ron<ronljohnsonjr@gmail.com> 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.klaver@aklaver.com
On 10/10/21 13:06, Ron wrote: > On 10/10/21 3:00 PM, Tom Lane wrote: >> Ron <ronljohnsonjr@gmail.com> 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.klaver@aklaver.com
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<ronljohnsonjr@gmail.com> 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.
On Mon, 2021-10-11 at 00:12 +0530, Anil wrote: > Please find the server log, if you can find something (due to some reasons not being able to get OS system log, will beshared soon). Nothing to see. Perhaps an ill-configured network component closes idle connection? In that case, try keepalive. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hi All,
I am also facing a similar issue. In my case I am connecting to AWS Aurora PostgreSQL.
When my psql session is idle for a while ( for 5 - 10 minutes ) , It gets terminated and reconnect but query/transaction started by earlier session remain in `idle in transaction` state and the only option left to clean it is terminate that session with `pg_terminate_backend()`. Issue happens when we are connecting from either psql or DBeaver client but not with the application.
Here is example :
```
test=> begin;
BEGIN
test=*> LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE;
LOCK TABLE
test=*>
test=*> rollback;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset:
Succeeded.
psql (13.4, server 11.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
test=> select now()-query_start, pid,state, usename, application_name, client_addr, wait_event_type, query from pg_stat_activity where state!='idle';
?column? | pid | state | usename | application_name | client_addr | wait_event_type | query
-----------------+-------+---------------------+----------+------------------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------
00:08:22.762573 | 20233 | active | postgres | PostgreSQL JDBC Driver | 10.0.174.30 | Lock | LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE
00:09:28.101577 | 14830 | idle in transaction | postgres | psql | 10.0.173.200 | Client | LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE;
00:00:00 | 10198 | active | postgres | psql | 10.0.103.144 | | select now()-query_start, pid,state, usename, application_name, client_addr, wait_event_type, query from pg_stat_activity where state!='idle';
(3 rows)
test=> rollback;
WARNING: there is no transaction in progress
ROLLBACK
```
```
test=> begin;
BEGIN
test=*> LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE;
LOCK TABLE
test=*>
test=*> rollback;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset:
Succeeded.
psql (13.4, server 11.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
test=> select now()-query_start, pid,state, usename, application_name, client_addr, wait_event_type, query from pg_stat_activity where state!='idle';
?column? | pid | state | usename | application_name | client_addr | wait_event_type | query
-----------------+-------+---------------------+----------+------------------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------
00:08:22.762573 | 20233 | active | postgres | PostgreSQL JDBC Driver | 10.0.174.30 | Lock | LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE
00:09:28.101577 | 14830 | idle in transaction | postgres | psql | 10.0.173.200 | Client | LOCK TABLE test.dependency_queue IN EXCLUSIVE MODE;
00:00:00 | 10198 | active | postgres | psql | 10.0.103.144 | | select now()-query_start, pid,state, usename, application_name, client_addr, wait_event_type, query from pg_stat_activity where state!='idle';
(3 rows)
test=> rollback;
WARNING: there is no transaction in progress
ROLLBACK
```
Nothing in the postgresql logs apart from below statements multiple times:
```
cat postgresql.log.2021-10-22-1200 | grep '10.0.173.200'
2021-10-22 12:00:09 UTC:10.0.173.200(4082):[unknown]@[unknown]:[729]:LOG: connection received: host=10.0.173.200 port=4082
2021-10-22 12:00:09 UTC:10.0.173.200(4082):[unknown]@[unknown]:[729]:LOG: incomplete startup packet
2021-10-22 12:00:09 UTC:10.0.173.200(4082):[unknown]@[unknown]:[729]:LOG: connection received: host=10.0.173.200 port=4082
2021-10-22 12:00:09 UTC:10.0.173.200(4082):[unknown]@[unknown]:[729]:LOG: incomplete startup packet
```
Now because the psql session was terminated incorrectly, another session from the application trying to get explicit lock on the same table is waiting for a long time, until we are not terminating the hanging session.
Below are timeout/keepalive settings on my PostgreSQL instance:
```
select name, setting, unit from pg_settings where name ilike '%timeout%';
name | setting | unit
-------------------------------------+----------+------
archive_timeout | 300 | s
authentication_timeout | 60 | s
checkpoint_timeout | 60 | s
deadlock_timeout | 1000 | ms
idle_in_transaction_session_timeout | 86400000 | ms
lock_timeout | 0 | ms
statement_timeout | 0 | ms
wal_receiver_timeout | 30000 | ms
wal_sender_timeout | 60000 | ms
(9 rows)
select name, setting, unit from pg_settings where name ilike '%keep%';
name | setting | unit
-------------------------+---------+------
tcp_keepalives_count | 9 |
tcp_keepalives_idle | 7200 | s
tcp_keepalives_interval | 75 | s
wal_keep_segments | 0 |
(4 rows)
```
select name, setting, unit from pg_settings where name ilike '%timeout%';
name | setting | unit
-------------------------------------+----------+------
archive_timeout | 300 | s
authentication_timeout | 60 | s
checkpoint_timeout | 60 | s
deadlock_timeout | 1000 | ms
idle_in_transaction_session_timeout | 86400000 | ms
lock_timeout | 0 | ms
statement_timeout | 0 | ms
wal_receiver_timeout | 30000 | ms
wal_sender_timeout | 60000 | ms
(9 rows)
select name, setting, unit from pg_settings where name ilike '%keep%';
name | setting | unit
-------------------------+---------+------
tcp_keepalives_count | 9 |
tcp_keepalives_idle | 7200 | s
tcp_keepalives_interval | 75 | s
wal_keep_segments | 0 |
(4 rows)
```
macOS Catelina 10.15.7 timeout settings :
```
sysctl -A | grep keep
net.inet.tcp.keepidle: 7200000
net.inet.tcp.keepintvl: 75000
net.inet.tcp.keepinit: 75000
net.inet.tcp.keepcnt: 8
net.inet.tcp.always_keepalive: 0
net.inet.mptcp.keepalive: 840
net.link.ether.inet.keep_announcements: 1
net.key.natt_keepalive_interval: 20
net.inet6.ip6.keepfaith: 0
net.necp.pass_keepalives: 1
```
```
sysctl -A | grep keep
net.inet.tcp.keepidle: 7200000
net.inet.tcp.keepintvl: 75000
net.inet.tcp.keepinit: 75000
net.inet.tcp.keepcnt: 8
net.inet.tcp.always_keepalive: 0
net.inet.mptcp.keepalive: 840
net.link.ether.inet.keep_announcements: 1
net.key.natt_keepalive_interval: 20
net.inet6.ip6.keepfaith: 0
net.necp.pass_keepalives: 1
```
I can set ` lock_timeout` and ` idle_in_transaction_session_timeout` to lower value so that the hanging session can get terminated automatically by postgres but I would like to know root cause of the issue and fix it there instead of adjusting postgresql timeout setting values.
Please let me know which setting on my client or server is not set properly and because of that the issue is happening.
Regards,
Sachin
On Mon, Oct 11, 2021 at 1:38 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2021-10-11 at 00:12 +0530, Anil wrote:
> 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).
Nothing to see.
Perhaps an ill-configured network component closes idle connection?
In that case, try keepalive.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
--
Sachin Kotwal