Date: Thu, 10 Nov 2011 09:53:31 +0400
From: "Ruslan A. Bondar" <fsat@list.ru>
To: Craig Ringer <ringerc@ringerc.id.au>
Cc: pgsql-admin@postgresql.org
Subject: Re: How to deal with corrupted database?
Message-ID: <20111110095331.0772436d@list.ru>
In-Reply-To: <4EBB38C7.6030707@ringerc.id.au>
References: <20111109150225.076c2f38@list.ru> <4EBA7A6F.50602@ringerc.id.au>
	<20111109173744.7592798e@list.ru> <4EBB38C7.6030707@ringerc.id.au>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

> > So I've stopped software caused these inserts and deletes, but
> > reindexing shows same warnings. I've restarted postgresql server.
> 
> How did you restart PostgreSQL?
> 
> If there were backends hung in the vfs, did the eventually terminate
> by themselves? If not, did you terminate them yourself? How? With a
> signal (kill)? Which signal? Some other way?
> 
Just /etc/init.d/postgresql-9.0 restart
As far as I know it uses "smart" shutdown on debian.

> > Postgres restarted successfully, but the database became
> > unaccessible. Filesystem is clean. File base/16387/86057840 exists
> > but is zero length. File pg_subtrans/00F2 does not exists.
> 
> Hm, ok. I'm a bit suspicious of the deadlock in the kernel. It isn't
> necessarily a kernel issue, but given the system you're running on I
> won't be too surprised if it is either. There's a fairly good chance
> the trigger for this was a kernel issue munching your data.
> 
> Are you able to reproduce this issue with another instance of
> PostgreSQL running with a freshly created database cluster (initdb) ?

Ok. I'll reinit clean cluster, restore databases from backups and will try to reproduce the issue.
I'll have nearly the same configuration and data like before crash. Hope the issue will appear.
Also I've copied old, corrupted, database for the case issue will not appear in a week.

P.S. Yes, the host is xen guest and postgres works under a heavy load.
But at a hardware level it uses 4 individual HDD's in a mirror for storing it's data. 
The filesystem operations should be fast enough.