Error: server closed the connection unexpectedly

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:

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.

--
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 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.

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:

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.

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 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

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
```

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
```

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)
```

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
```

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

--

Thanks and Regards,
Sachin Kotwal