pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: 8.3.5 broken after power fail SOLVED
6+ messages / 4 participants
[nested] [flat]
* Re: 8.3.5 broken after power fail SOLVED
@ 2009-02-21 08:43 Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Monnerie @ 2009-02-21 08:43 UTC (permalink / raw)
To: pgsql-admin
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.
mfg zmi
--
// 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
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.3.5 broken after power fail SOLVED
@ 2009-02-21 09:40 Scott Marlowe <scott.marlowe@gmail.com>
parent: Michael Monnerie <michael.monnerie@is.it-management.at>
0 siblings, 2 replies; 6+ messages in thread
From: Scott Marlowe @ 2009-02-21 09:40 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@is.it-management.at>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.3.5 broken after power fail SOLVED
@ 2009-02-21 12:58 Michael Monnerie <michael.monnerie@is.it-management.at>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Monnerie @ 2009-02-21 12:58 UTC (permalink / raw)
To: pgsql-admin
On Samstag 21 Februar 2009 Scott Marlowe wrote:
> 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.
The shit thing is, I just discussed with the XFS devs last week, whether
it is save to have a virtualization like VMware or XEN, and the answer
was "depends on the hypervisor". I had such an issue with VMware 2 years
ago, and now with XEN, so I would say they are not save. But there must
be something you can configure in order not to have such drastic errors
on power fail. It's just nobody seems to know (or want to tell) how to
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
another, I guess I'll do that with WAL, so at least then I can recover
to that latest entry.
> 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.
The problem was I wasn't working this week, and did just a basic check
if everything is up again. There were e-mails arriving, so I thought
it's OK. I was very pissed when some days later I found strange things
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
error recovery should have found the problem, latest at the moment the
first transaction touched the problematic table. Instead of throwing the
data effectively to /dev/null :-(
mfg zmi
--
// 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
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.3.5 broken after power fail SOLVED
@ 2009-02-21 14:34 Naomi Walker <nwalker@eldocomp.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Naomi Walker @ 2009-02-21 14:34 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Michael Monnerie <michael.monnerie@is.it-management.at>; pgsql-admin
Other than disaster tests, how would I know if I have an system that
lies about fsync?
>
> 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.
>
>
--
------------------------------------------------------------------------
Naomi Walker Chief Information Officer
Eldorado Computing, Inc nwalker@eldocomp.com
---An EDS Company 602-604-3100
------------------------------------------------------------------------
Hard things are put in our way, not to stop us, but to call out our
courage and strength. -- Anonymous
------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.3.5 broken after power fail SOLVED
@ 2009-02-21 22:41 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Naomi Walker <nwalker@eldocomp.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ron Mayer @ 2009-02-21 22:41 UTC (permalink / raw)
To: Naomi Walker <nwalker@eldocomp.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Michael Monnerie <michael.monnerie@is.it-management.at>; pgsql-admin
Naomi Walker wrote:
> Other than disaster tests, how would I know if I have an system that
> lies about fsync?
Well, the linux kernel tries to detect it on bootup and
will give messages like this:
%dmesg | grep 'disabling barriers'
JBD: barrier-based sync failed on md1 - disabling barriers
JBD: barrier-based sync failed on hda3 - disabling barriers
when it detects certain types of unreliable fsync's. The command
%hdparm -I /dev/hdf | grep FLUSH_CACHE_EXT
will give you clues if a hard drive itself even can support
a non-lying fsync when it's internal cache is enabled.
Sadly some filesystems (ext3) lie even above and beyond what
Linux does - by only using the write barriers correctly
when the inode itself is modified; not when the data is modified.
A test program here:
http://archives.postgresql.org/pgsql-performance/2008-08/msg00159.php
can detect those cases where the kernel & drive don't lie
about fsync but ext3 lies in spite of them; with more background
info here:
http://article.gmane.org/gmane.linux.file-systems/21373
http://thread.gmane.org/gmane.linux.kernel/646040
Elsewhere in the archives you can find programs that measure
how fast fsyncs happen - but on your hardware, and you can
try to see if those numbers approximately match how fast your
disks spin. But then you still need to make sure the test
program used the same methods for syncing the drive that your
postgres configuration files are choosing.
I wonder if the only really safe way is to run a very
write intensive database script and pull and kill your
system in a number of ways, including yanking power to
the system; to disk arrays, etc and see if your database died.
>
>
>>
>> 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.
>>
>>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 8.3.5 broken after power fail SOLVED
@ 2009-02-22 02:43 Scott Marlowe <scott.marlowe@gmail.com>
parent: Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2009-02-22 02:43 UTC (permalink / raw)
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: Naomi Walker <nwalker@eldocomp.com>; Michael Monnerie <michael.monnerie@is.it-management.at>; pgsql-admin
On Sat, Feb 21, 2009 at 3:41 PM, Ron Mayer
<rm_pg@cheapcomplexdevices.com> wrote:
> Naomi Walker wrote:
>> Other than disaster tests, how would I know if I have an system that
>> lies about fsync?
>
> Well, the linux kernel tries to detect it on bootup and
> will give messages like this:
> %dmesg | grep 'disabling barriers'
> JBD: barrier-based sync failed on md1 - disabling barriers
> JBD: barrier-based sync failed on hda3 - disabling barriers
> when it detects certain types of unreliable fsync's. The command
> %hdparm -I /dev/hdf | grep FLUSH_CACHE_EXT
> will give you clues if a hard drive itself even can support
> a non-lying fsync when it's internal cache is enabled.
>
>
> Sadly some filesystems (ext3) lie even above and beyond what
> Linux does - by only using the write barriers correctly
> when the inode itself is modified; not when the data is modified.
> A test program here:
> http://archives.postgresql.org/pgsql-performance/2008-08/msg00159.php
> can detect those cases where the kernel & drive don't lie
> about fsync but ext3 lies in spite of them; with more background
> info here:
> http://article.gmane.org/gmane.linux.file-systems/21373
> http://thread.gmane.org/gmane.linux.kernel/646040
>
>
> Elsewhere in the archives you can find programs that measure
> how fast fsyncs happen - but on your hardware, and you can
> try to see if those numbers approximately match how fast your
> disks spin. But then you still need to make sure the test
> program used the same methods for syncing the drive that your
> postgres configuration files are choosing.
>
> I wonder if the only really safe way is to run a very
> write intensive database script and pull and kill your
> system in a number of ways, including yanking power to
> the system; to disk arrays, etc and see if your database died.
Well, you can't prove it's 100% safe but you can usually find most of
the not safe systems this way. I usually setup a big pgbench db, run
500 or so concurrent, wait 5 or 10 minutes, run a checkpoint, and
halfway through it pull the plug. It's still possible for a system to
fail after passing this test, but I feel a lot better knowing I've
done it a couple of times and the db came back up without problems.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-02-22 02:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-02-21 08:43 Re: 8.3.5 broken after power fail SOLVED Michael Monnerie <michael.monnerie@is.it-management.at>
2009-02-21 09:40 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-21 12:58 ` Michael Monnerie <michael.monnerie@is.it-management.at>
2009-02-21 14:34 ` Naomi Walker <nwalker@eldocomp.com>
2009-02-21 22:41 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2009-02-22 02:43 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox