From: Michael Monnerie <michael.monnerie@is.it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Re: 8.3.5 broken after power fail SOLVED
Date: Sat, 21 Feb 2009 13:58:59 +0100
User-Agent: KMail/1.10.3 (Linux/2.6.27.13-ZMI; KDE/4.1.3; x86_64; ; )
References: <43a3a0bccd3684972f33e0051b45f2b4@webmail.zmi.at>
	<200902210943.27875@zmi.at>
	<dcc563d10902210140x22e9b4bckcf17ac02e4c91769@mail.gmail.com>
In-Reply-To: <dcc563d10902210140x22e9b4bckcf17ac02e4c91769@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart1255912.zuhJWHUCm2";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200902211358.59500@zmi.at>

--nextPart1255912.zuhJWHUCm2
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On Samstag 21 Februar 2009 Scott Marlowe wrote:
> We preach this again and again. =A0PostgreSQL can only survive a power
> outage type failure ONLY if the hardware / OS / filesystem don't lie
> about fsync. =A0If they do, all bets are off, and this kind of failure
> means you should really failover to another machine or restore a
> backup.

The shit thing is, I just discussed with the XFS devs last week, whether=20
it is save to have a virtualization like VMware or XEN, and the answer=20
was "depends on the hypervisor". I had such an issue with VMware 2 years=20
ago, and now with XEN, so I would say they are not save. But there must=20
be something you can configure in order not to have such drastic errors=20
on power fail. It's just nobody seems to know (or want to tell) how to=20
do that. At least, not to me ;-)

> 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.

As I need another Postgres setup with a server syncing dbmail to=20
another, I guess I'll do that with WAL, so at least then I can recover=20
to that latest entry.

> The recommendations for recovery of data are just that, recovery
> oriented. =A0They can't fix a broken database at that point. =A0You 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.

The problem was I wasn't working this week, and did just a basic check=20
if everything is up again. There were e-mails arriving, so I thought=20
it's OK. I was very pissed when some days later I found strange things=20
happening, and then to see that a table was broken and ate nearly all e-
mails. If at least Postgres would have whined and stopped working...

I know it's not Postgres' fault to have fsync messed up, but at least=20
error recovery should have found the problem, latest at the moment the=20
first transaction touched the problematic table. Instead of throwing the=20
data effectively to /dev/null :-(

mfg zmi
=2D-=20
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4


--nextPart1255912.zuhJWHUCm2
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)

iEYEABECAAYFAkmf+pMACgkQzhSR9xwSCbRxWwCfcHiMexjJGeO8xFR6EwMlnyFZ
wroAn0X7JU6HXHHRMPx0bY4iE4Kf56Jw
=Ek6Q
-----END PGP SIGNATURE-----

--nextPart1255912.zuhJWHUCm2--