Обсуждение: Re: Terminating connection because of crash of another server process

Поиск
Список
Период
Сортировка

Re: Terminating connection because of crash of another server process

От
Laurenz Albe
Дата:
On Wed, 2023-09-27 at 16:25 +0300, Deo Felix wrote:
> Please assist Im getting the error below in Postgresql 13 database
>
> WARNING:  terminating connection because of crash of another server process

The trick is not to have any crashes.

Look at the PostgreSQL log to determine the cause of the crash and
see that it does not happen again.  A well configured PostgreSQL
on good hardware should not crash.

Yours,
Laurenz Albe

Re: Terminating connection because of crash of another server process

От
Scott Ribe
Дата:
I've had two rounds of that problem over the years. Both were caused by 3rd-party extensions to PostgreSQL.

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory

Please advice what configuration should I do


From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Wednesday, September 27, 2023 4:39:18 PM
To: Deo Felix <deofelix9@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Terminating connection because of crash of another server process
 
On Wed, 2023-09-27 at 16:25 +0300, Deo Felix wrote:
> Please assist Im getting the error below in Postgresql 13 database
>
> WARNING:  terminating connection because of crash of another server process

The trick is not to have any crashes.

Look at the PostgreSQL log to determine the cause of the crash and
see that it does not happen again.  A well configured PostgreSQL
on good hardware should not crash.

Yours,
Laurenz Albe

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
What extension please can you mention
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Wednesday, September 27, 2023 4:50:27 PM
To: Deo Felix <deofelix9@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Terminating connection because of crash of another server process
 
I've had two rounds of that problem over the years. Both were caused by 3rd-party extensions to PostgreSQL.

Re: Terminating connection because of crash of another server process

От
Laurenz Albe
Дата:
On Wed, 2023-09-27 at 13:57 +0000, Deo Felix wrote:
> Please advice what configuration should I do

Read the PostgreSQL log.  Not only the one line you keep repeating.

Yours,
Laurenz Albe

Re: Terminating connection because of crash of another server process

От
Scott Ribe
Дата:
> On Sep 27, 2023, at 7:58 AM, Deo Felix <deofelix9@gmail.com> wrote:
>
> What extension please can you mention

Both were years ago and may well have been fixed by now. You need to audit what (whether) 3rd-party extensions you
have,and how necessary they are.  

That said: POWA and TimescaleDB.

Re: Terminating connection because of crash of another server process

От
Scott Ribe
Дата:
> On Sep 27, 2023, at 7:58 AM, Deo Felix <deofelix9@gmail.com> wrote:
> 
> What extension please can you mention

I did. But you need to see what extensions *you* have installed, if any.

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
There is a view that is causing to fail 

failed process was running refresh materialized view

On Wed, 27 Sept 2023 at 17:05, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2023-09-27 at 13:57 +0000, Deo Felix wrote:
> Please advice what configuration should I do

Read the PostgreSQL log.  Not only the one line you keep repeating.

Yours,
Laurenz Albe

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
Okay thank you, I have not installed the mentioned extensions

On Wed, 27 Sept 2023 at 17:19, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Sep 27, 2023, at 7:58 AM, Deo Felix <deofelix9@gmail.com> wrote:
>
> What extension please can you mention

Both were years ago and may well have been fixed by now. You need to audit what (whether) 3rd-party extensions you have, and how necessary they are.

That said: POWA and TimescaleDB.




Re: Terminating connection because of crash of another server process

От
Scott Ribe
Дата:
> On Sep 27, 2023, at 1:07 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
> Okay thank you, I have not installed the mentioned extensions

That's not the point. The point is that buggy extensions are one cause of this kind of instability, and you need to
auditfor *any* 3rd-party extensions in *YOUR* installation.

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
Okay, basically I have installed this extension 
pgaudit, repmgr, pgagent And I will audit them as you have adviced

On Wed, 27 Sept 2023 at 22:13, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Sep 27, 2023, at 1:07 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
> Okay thank you, I have not installed the mentioned extensions

That's not the point. The point is that buggy extensions are one cause of this kind of instability, and you need to audit for *any* 3rd-party extensions in *YOUR* installation.

Re: Terminating connection because of crash of another server process

От
Deepak Goel
Дата:
The error posted by you ( WARNING:  terminating connection because of crash of another server process) is at a very high level. Can you drill down for a detailed error?


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 8:13 AM Deo Felix <deofelix9@gmail.com> wrote:
Okay, basically I have installed this extension 
pgaudit, repmgr, pgagent And I will audit them as you have adviced

On Wed, 27 Sept 2023 at 22:13, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Sep 27, 2023, at 1:07 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
> Okay thank you, I have not installed the mentioned extensions

That's not the point. The point is that buggy extensions are one cause of this kind of instability, and you need to audit for *any* 3rd-party extensions in *YOUR* installation.

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
The problem occurred  when refreshing materialized views, out of shared memory error occur

On Thu, 28 Sept 2023 at 08:51, Deepak Goel <deicool@gmail.com> wrote:
The error posted by you ( WARNING:  terminating connection because of crash of another server process) is at a very high level. Can you drill down for a detailed error?


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 8:13 AM Deo Felix <deofelix9@gmail.com> wrote:
Okay, basically I have installed this extension 
pgaudit, repmgr, pgagent And I will audit them as you have adviced

On Wed, 27 Sept 2023 at 22:13, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Sep 27, 2023, at 1:07 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
> Okay thank you, I have not installed the mentioned extensions

That's not the point. The point is that buggy extensions are one cause of this kind of instability, and you need to audit for *any* 3rd-party extensions in *YOUR* installation.

Re: Terminating connection because of crash of another server process

От
Deepak Goel
Дата:
Please print the "out of shared memory error" or you need to make a provision for printing detailed error.


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 4:55 PM Deo Felix <deofelix9@gmail.com> wrote:
The problem occurred  when refreshing materialized views, out of shared memory error occur

On Thu, 28 Sept 2023 at 08:51, Deepak Goel <deicool@gmail.com> wrote:
The error posted by you ( WARNING:  terminating connection because of crash of another server process) is at a very high level. Can you drill down for a detailed error?


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 8:13 AM Deo Felix <deofelix9@gmail.com> wrote:
Okay, basically I have installed this extension 
pgaudit, repmgr, pgagent And I will audit them as you have adviced

On Wed, 27 Sept 2023 at 22:13, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Sep 27, 2023, at 1:07 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
> Okay thank you, I have not installed the mentioned extensions

That's not the point. The point is that buggy extensions are one cause of this kind of instability, and you need to audit for *any* 3rd-party extensions in *YOUR* installation.

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
Please find the screenshot of  error from the log file  postgresqlerror.png




On Thu, 28 Sept 2023 at 14:29, Deepak Goel <deicool@gmail.com> wrote:
Please print the "out of shared memory error" or you need to make a provision for printing detailed error.


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 4:55 PM Deo Felix <deofelix9@gmail.com> wrote:
The problem occurred  when refreshing materialized views, out of shared memory error occur

On Thu, 28 Sept 2023 at 08:51, Deepak Goel <deicool@gmail.com> wrote:
The error posted by you ( WARNING:  terminating connection because of crash of another server process) is at a very high level. Can you drill down for a detailed error?


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 8:13 AM Deo Felix <deofelix9@gmail.com> wrote:
Okay, basically I have installed this extension 
pgaudit, repmgr, pgagent And I will audit them as you have adviced

On Wed, 27 Sept 2023 at 22:13, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Sep 27, 2023, at 1:07 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
> Okay thank you, I have not installed the mentioned extensions

That's not the point. The point is that buggy extensions are one cause of this kind of instability, and you need to audit for *any* 3rd-party extensions in *YOUR* installation.
Вложения

Re: Terminating connection because of crash of another server process

От
Deepak Goel
Дата:
The error says very little. You might want to put the 'Debug' on along with "Detail".

Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Fri, Sep 29, 2023 at 1:54 PM Deo Felix <deofelix9@gmail.com> wrote:
Please find the screenshot of  error from the log file  postgresqlerror.png




On Thu, 28 Sept 2023 at 14:29, Deepak Goel <deicool@gmail.com> wrote:
Please print the "out of shared memory error" or you need to make a provision for printing detailed error.


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 4:55 PM Deo Felix <deofelix9@gmail.com> wrote:
The problem occurred  when refreshing materialized views, out of shared memory error occur

On Thu, 28 Sept 2023 at 08:51, Deepak Goel <deicool@gmail.com> wrote:
The error posted by you ( WARNING:  terminating connection because of crash of another server process) is at a very high level. Can you drill down for a detailed error?


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 8:13 AM Deo Felix <deofelix9@gmail.com> wrote:
Okay, basically I have installed this extension 
pgaudit, repmgr, pgagent And I will audit them as you have adviced

On Wed, 27 Sept 2023 at 22:13, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Sep 27, 2023, at 1:07 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
> Okay thank you, I have not installed the mentioned extensions

That's not the point. The point is that buggy extensions are one cause of this kind of instability, and you need to audit for *any* 3rd-party extensions in *YOUR* installation.
Вложения

Re: Terminating connection because of crash of another server process

От
Laurenz Albe
Дата:
On Fri, 2023-09-29 at 11:21 +0300, Deo Felix wrote:
> Please find the screenshot of  error from the log file  postgresqlerror.png

It is really amazing how much effort you make *not* to share
any relevant information with us.  But there is finally some
interesting message hidden in your image:

 server process (PID 14257) was terminated by signal 9: Killed

Since I don't expect that random users on your server machine
go around "kill -9"-ing PostgreSQL processes, it is very likely
that PostgreSQL is crashed by the Linux out-of-memory killer.

You should disable memory overcommit, see
https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

Then you will start getting "out of memory" errors instead of
crashes.  To combat these, reduce the memory usage parameters,
mostly "work_mem", or reduce the number of active database sessions
by using a connection pool.

Yours,
Laurenz Albe

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
It is in production that is why the debug is off

On Fri, 29 Sept 2023 at 11:41, Deepak Goel <deicool@gmail.com> wrote:
The error says very little. You might want to put the 'Debug' on along with "Detail".

Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Fri, Sep 29, 2023 at 1:54 PM Deo Felix <deofelix9@gmail.com> wrote:
Please find the screenshot of  error from the log file  postgresqlerror.png




On Thu, 28 Sept 2023 at 14:29, Deepak Goel <deicool@gmail.com> wrote:
Please print the "out of shared memory error" or you need to make a provision for printing detailed error.


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 4:55 PM Deo Felix <deofelix9@gmail.com> wrote:
The problem occurred  when refreshing materialized views, out of shared memory error occur

On Thu, 28 Sept 2023 at 08:51, Deepak Goel <deicool@gmail.com> wrote:
The error posted by you ( WARNING:  terminating connection because of crash of another server process) is at a very high level. Can you drill down for a detailed error?


Deepak
"The greatness of a nation can be judged by the way its animals are treated - Mahatma Gandhi"


"Plant a Tree, Go Green"



On Thu, Sep 28, 2023 at 8:13 AM Deo Felix <deofelix9@gmail.com> wrote:
Okay, basically I have installed this extension 
pgaudit, repmgr, pgagent And I will audit them as you have adviced

On Wed, 27 Sept 2023 at 22:13, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Sep 27, 2023, at 1:07 PM, Deo Felix <deofelix9@gmail.com> wrote:
>
> Okay thank you, I have not installed the mentioned extensions

That's not the point. The point is that buggy extensions are one cause of this kind of instability, and you need to audit for *any* 3rd-party extensions in *YOUR* installation.
Вложения

Re: Terminating connection because of crash of another server process

От
Deo Felix
Дата:
Please note that the server is in production and debug is not enabled, that is why there is not much information in the log file.
I'm starting working with your suggestion including reducing the work_mem


On Fri, 29 Sept 2023 at 12:27, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2023-09-29 at 11:21 +0300, Deo Felix wrote:
> Please find the screenshot of  error from the log file  postgresqlerror.png

It is really amazing how much effort you make *not* to share
any relevant information with us.  But there is finally some
interesting message hidden in your image:

 server process (PID 14257) was terminated by signal 9: Killed

Since I don't expect that random users on your server machine
go around "kill -9"-ing PostgreSQL processes, it is very likely
that PostgreSQL is crashed by the Linux out-of-memory killer.

You should disable memory overcommit, see
https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

Then you will start getting "out of memory" errors instead of
crashes.  To combat these, reduce the memory usage parameters,
mostly "work_mem", or reduce the number of active database sessions
by using a connection pool.

Yours,
Laurenz Albe

Re: Terminating connection because of crash of another server process

От
Deepak Goel
Дата:


On Mon, Oct 2, 2023 at 11:34 AM Deo Felix <deofelix9@gmail.com> wrote:
Please note that the server is in production and debug is not enabled, that is why there is not much information in the log file.

We need the expanded error. Please enable (debug, detail) in production. You can turn it off once the detailed error is found out.
 
I'm starting working with your suggestion including reducing the work_mem


On Fri, 29 Sept 2023 at 12:27, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2023-09-29 at 11:21 +0300, Deo Felix wrote:
> Please find the screenshot of  error from the log file  postgresqlerror.png

It is really amazing how much effort you make *not* to share
any relevant information with us.  But there is finally some
interesting message hidden in your image:

 server process (PID 14257) was terminated by signal 9: Killed

Since I don't expect that random users on your server machine
go around "kill -9"-ing PostgreSQL processes, it is very likely
that PostgreSQL is crashed by the Linux out-of-memory killer.

You should disable memory overcommit, see
https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

Then you will start getting "out of memory" errors instead of
crashes.  To combat these, reduce the memory usage parameters,
mostly "work_mem", or reduce the number of active database sessions
by using a connection pool.

Yours,
Laurenz Albe