Understand this error

On Thu, Apr 30, 2009 at 7:00 AM, paulo matadr <saddoness@yahoo.com.br> wrote:

Hi all,
my database entry in mode recovery,
analyzing my pg_log I seem this:
system logger process (PID 6517) was terminated by signal 9
background writer process (PID 6519) was terminated by signal 9
terminating any other active server processes

Yeah, you're getting bitten by the OOM killer. What changes, if any,
have you made to the postgresql.conf file?

paulo matadr wrote:

Hi all,
my database entry in mode recovery,
analyzing my pg_log I seem this:

system logger process (PID 6517) was terminated by signal 9
background writer process (PID 6519) was terminated by signal 9
terminating any other active server processes

You haven't told us what OS you are on. Based on the log below, though,
it looks like Linux.

`kill -l' on Linux tells us that signal 9 is SIGKILL, a hard kill. That
should only happen if (a) you send it with `kill -9' or `kill -KILL' or
(b) the machine runs out of memory while in overcommit mode (the
default) and the OOM killer picks PostgreSQL as the process to terminate
to free memory.

You should NOT have your server in overcommit mode if you are running
PostgreSQL. See, in the PostgreSQL manual:

http://www.postgresql.org/docs/current/static/kernel-resources.html#AEN22235

kernel: [<ffffffff800ba475>] out_of_memory+0x53/0x267

[snip]

kernel: Out of memory: Killed process 6519 (postmaster).

How prenvent postgres use all memory of system?Why this happen?

Read the link in the PostgreSQL manual, above.

Note that it's not very likely that PostgreSQL was the process that used
up all your memory. It was just unlucky enough to be picked as the one
to be killed, because the OOM killer is terrible at estimating which
process is using the most memory when programs like PostgreSQL have
allocated large blocks of shared memory.

--
Craig Ringer

On Thu, Apr 30, 2009 at 3:00 PM, paulo matadr <saddoness@yahoo.com.br> wrote:

Hi all,
my database entry in mode recovery,
analyzing my pg_log I seem this:
system logger process (PID 6517) was terminated by signal 9
background writer process (PID 6519) was terminated by signal 9
terminating any other active server processes

You are bitten by the OOM-killer. It can lead to severy data loss if it decides
to kill the postmaster. To avoid this, you should always set overcommit_memory
to 2 (which means off). See Section 17.4.3. here:

http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html

You should *never* run a production database server in overcommit_memory mode!

Craig Ringer <craig@postnewspapers.com.au> writes:

Note that it's not very likely that PostgreSQL was the process that used
up all your memory. It was just unlucky enough to be picked as the one
to be killed, because the OOM killer is terrible at estimating which
process is using the most memory when programs like PostgreSQL have
allocated large blocks of shared memory.

It's worse than that: the OOM killer is broken by design, because it
intentionally picks on processes that have a lot of large children
--- without reference to the fact that a lot of the "largeness" might
be the same shared memory block.  So the postmaster process very often
looks like a good target to it, even though killing the postmaster will
in fact free a negligible amount of memory.

regards, tom lane