Message-ID: <455C9CCA.7080804@modgraph-usa.com>
Date: Thu, 16 Nov 2006 09:15:54 -0800
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061025)
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>, Russell Smith <mr-russ@pws.com.au>,
	pgsql-performance@postgresql.org
Subject: Re: Postgres server crash
References: <ce4072df0610201305u7836dc7bn437ec0aaf1c3e25f@mail.gmail.com>
	<b42b73150610201811m84369f7kc7d58b39aed3ad73@mail.gmail.com>
	<ce4072df0610230810t5d66bbb8xf1e9c0ed71b6b6c0@mail.gmail.com>
	<20061023215913.GW26892@nasby.net>
	<ce4072df0610240617s72f35540odd3d4e0903313b63@mail.gmail.com>
	<20061025001709.GA26892@nasby.net>
	<453EF7C4.2040105@modgraph-usa.com>
	<455BCAE8.8010905@modgraph-usa.com>
	<455BCCCD.2020701@pws.com.au> <455C420D.8090209@archonet.com>
In-Reply-To: <455C420D.8090209@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

By the way, in spite of my questions and concerns, I was *very* impressed by the recovery process.  I know it might seem like old hat to you guys to watch the WAL in action, and I know on a theoretical level it's supposed to work, but watching it recover 150 separate databases, and find and fix a couple of problems was very impressive.  It gives me great confidence that I made the right choice to use Postgres.

Richard Huxton wrote:
>>>  2. Why didn't the database recover?  Why are there two processes
>>>     that couldn't be killed?
> 
> I'm guessing it didn't recover *because* there were two processes that 
> couldn't be killed. Responsibility for that falls to the 
> operating-system. I've seen it most often with faulty drivers or 
> hardware that's being communicated with/written to. However, see below.

It can't be a coincidence that these were the only two processes in a SELECT operation.  Does the server disable signals at critical points?

I'd make a wild guess that this is some sort of deadlock problem -- these two servers have disabled signals for a critical section of SELECT, and are waiting for something from the postmaster, but postmaster is dead.

This is an ordinary system, no hardware problems, stock RH FC3 kernel, stock PG 8.1.4, with 4 GB memory, and at the moment the database is running on a single SATA disk.  I'm worried that a production server can get into a state that requires manual intervention to recover.

Craig