DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=X9sO2ZwJSj/9GVxTtOG/pXN3oMh3EoB6wbmhzC9bw2YCqTYX3iw5/wNXiDep3FqP1n
	aDDpAyTipT7o/1M9jKVJ7mOb7wkPBoqe7S9sulYGC7DtfdK0/vN66dAJAVR+ilofWjiG
	hO/DnDu2MGp+jYjGUobDkjgHo2vHHJMOCCrXA=
MIME-Version: 1.0
In-Reply-To: <200902210943.27875@zmi.at>
References: <43a3a0bccd3684972f33e0051b45f2b4@webmail.zmi.at>
	<200902210943.27875@zmi.at>
Date: Sat, 21 Feb 2009 02:40:43 -0700
Message-ID: <dcc563d10902210140x22e9b4bckcf17ac02e4c91769@mail.gmail.com>
Subject: Re: 8.3.5 broken after power fail SOLVED
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Michael Monnerie <michael.monnerie@is.it-management.at>
Cc: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Sat, Feb 21, 2009 at 1:43 AM, Michael Monnerie
<michael.monnerie@is.it-management.at> wrote:
> I managed to recover the data that was still readable. About 650
> messageblock entries got lost. What makes me nervous a bit is that
> postgres kept running despite (partially) being destroyed. It should
> really have shutdown itself after the first problem was found. That
> database is for mails, and I would understand to have lost some from the
> time before the power loss, but I even lost entries from *after* the
> crash. That means the error happened after the system was up again and
> happily did it's work, but lost entries to a table. Could there be a
> better way to check the db at crash recovery startup time?
>
> If someone is interested, I have a full postgres log with every single
> command done to the database.

We preach this again and again.  PostgreSQL can only survive a power
outage type failure ONLY if the hardware / OS / filesystem don't lie
about fsync.  If they do, all bets are off, and this kind of failure
means you should really failover to another machine or restore a
backup.

It's why you have to do possibly destructive tests to see if your
server stands at least some chance of surviving this kind of failure,
log shipping for recovery, and / or replication of another form (slony
etc...) to have a reliable server.

The recommendations for recovery of data are just that, recovery
oriented.  They can't fix a broken database at that point.  You need
to take it offline after this kind of failure if you can't trust your
hardware.

Usually when it finds something wrong it just won't start up.