Обсуждение: db recovery after raid5 failure

<qcor@vp.pl> wrote:

> I have serious problems recovering our db after recent raid5
> failure.  Long story short - no recent dumps, some missing files
> (like pg_control).

Been there -- at least on the end of helping with recovery for
people in that position with a different database product.  It can
be very painful.  :-(  (I'm assuming from your post that a second
drive failed before recovery from failure of the first was
complete.)

First a word of advice -- don't discard anything.  Keep any backups,
keep the bad drives, keep any logs, exports, reports, or anything
else which might contain fragments of the data.  Make a backup of
what you have now, if you haven't already.  Keep these things for a
long time.

Second, a word of encouragement -- given all these scattered
fragments and enough time and money, you'd might be surprised at how
much data can be recovered.  But time and money is required --
someone has to make the hard call of how much money it is worth to
recover how much of the data.

Based on your description, it sounds like you will probably need the
assistance of an outside company to recover very much.  Possibly one
company specializing in recovery of data off of damaged disks, and
another for PostgreSQL internals expertise.

I don't suppose there's another source for the data to avoid
attempting this recovery?

-Kevin

Balkrishna Sharma <b_ki@hotmail.com> wrote:

> If the database is not extremely huge, makes you wonder what does
> a RAID actually give us.

Well, RAID5 gives you a situations where you must have a second
drive fail before recovery for the first failure is complete, versus
being instantly dead on a single-drive failure.  RAID6 requires
three drives to fail in close succession (assuming a hot spare which
initiates recovery on failure).  RAID10 requires that two paired
drives fail.  We have about 100 database servers, and probably
average about two drive failures a month; having any down time from
them is rare because of RAID (and that's with us primarily using
RAID5).

> A robust near-realtime replication setup (say PITR + cloud)
> may be good enough against once in a few years of disk
> failure.atleast you don't add another point of failure that you
> (your database/OS) can't do anything about.

You've totally lost me there.  "The cloud" still uses similar
techniques, just out of your sight and control.  If you assume that
whoever is running it can do it better than you can, that's one
thing; just don't assume it's magic.  The machines in my shop are
what I *can* do something about.  Management here insists on near-
real-time backup using at least two completely independent
techniques to multiple machines in multiple buildings, with
continuous testing that all backups actually restore.  If we were to
float data off into a cloud somewhere, I can guarantee we wouldn't
count on it without an alternative.  As a place to put "one more
copy" it might make sense, as long as it had strong encryption.
(Again, you've lost all control over who has what access once you
send it into the cloud.)

-Kevin

Balkrishna Sharma  wrote:

>> average about two drive failures a month

> You must be having a real huge postgres setup with several hundreds
> of drives to have such high frequency of failure.

About 100 database servers with over 1000 drives spinning 24/7.  Also
probably significant, management-set policy is to replace machines
after four years, and we don't always hit that.  I haven't tried to
run numbers on it, but the pattern sure seems to match published
reports that we have some initial failures within the first few
months when a set of machines go in, it settles down for about three
years, then the failure rate starts to edge inexorably upward.

>> As a place to put "one more copy" it might make sense, as long as
>> it had strong encryption.

> I didn't expand but that's what I meant. The copy in cloud to be
> your final resort incase the LAN and the WAN copy both fail. You
> get an extra copy in a different geographic location for some
> catastrophic event.

OK, that makes sense.

-Kevin