Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id 45C709F9ECF for ; Wed, 15 Nov 2006 22:28:47 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024) with ESMTP id 84597-02 for ; Wed, 15 Nov 2006 22:28:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.trinitydigital.com.au (unknown [202.134.38.99]) by postgresql.org (Postfix) with ESMTP id B401F9F9C9E for ; Wed, 15 Nov 2006 22:28:43 -0400 (AST) Received: from [172.17.73.3] (unknown [202.134.38.110]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.trinitydigital.com.au (Postfix) with ESMTP id 648BEB6F; Thu, 16 Nov 2006 13:28:23 +1100 (EST) Message-ID: <455BCCCD.2020701@pws.com.au> Date: Thu, 16 Nov 2006 13:28:29 +1100 From: Russell Smith User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: "Craig A. James" CC: pgsql-performance@postgresql.org Subject: Re: Postgres server crash References: <20061023215913.GW26892@nasby.net> <20061025001709.GA26892@nasby.net> <453EF7C4.2040105@modgraph-usa.com> <455BCAE8.8010905@modgraph-usa.com> In-Reply-To: <455BCAE8.8010905@modgraph-usa.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/117 X-Sequence-Number: 21887 Craig A. James wrote: > For the third time today, our server has crashed, or frozen, actually > something in between. Normally there are about 30-50 connections > because of mod_perl processes that keep connections open. After the > crash, there are three processes remaining: > > # ps -ef | grep postgres > postgres 23832 1 0 Nov11 pts/1 00:02:53 > /usr/local/pgsql/bin/postmaster -D /postgres/main > postgres 1200 23832 20 14:28 pts/1 00:58:14 postgres: pubchem > pubchem 66.226.76.106(58882) SELECT > postgres 4190 23832 25 14:33 pts/1 01:09:12 postgres: asinex > asinex 66.226.76.106(56298) SELECT > > But they're not doing anything: No CPU time consumed, no I/O going on, > no progress. If I try to connect with psql(1), it says: > > psql: FATAL: the database system is in recovery mode > > And the server log has: > > LOG: background writer process (PID 23874) was terminated by signal 9 > LOG: terminating any other active server processes > LOG: statistics collector process (PID 23875) was terminated by signal 9 > WARNING: terminating connection because of crash of another server > process > DETAIL: The postmaster has commanded this server process to roll back > the current transaction and exit, because another server process > exited ab > normally and possibly corrupted shared memory. > HINT: In a moment you should be able to reconnect to the database and > repeat your command. > WARNING: terminating connection because of crash of another server > process > DETAIL: The postmaster has commanded this server process to roll back > the current transaction and exit, because another server process > exited ab > ... repeats about 50 times, one per process. > > Questions: > 1. Any idea what happened and how I can avoid this? It's a *big* > problem. > 2. Why didn't the database recover? Why are there two processes > that couldn't be killed? > 3. Where did the "signal 9" come from? (Nobody but me ever logs > in to the server machine.) > I would guess it's the linux OOM if you are running linux. You need to turn off killing of processes when you run out of memory. Are you getting close to running out of memory? > Help! > > Thanks, > Craig > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > >