pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCrash Recovery
4+ messages / 4 participants
[nested] [flat]
* Crash Recovery
@ 2003-01-24 03:32 Noah Silverman <noah@allresearch.com>
2003-01-24 06:29 ` Re: Crash Recovery Tom Lane <tgl@sss.pgh.pa.us>
2003-01-24 13:22 ` Re: Crash Recovery Andrew Sullivan <andrew@libertyrms.info>
2003-01-24 13:52 ` Re: Crash Recovery Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 3 replies; 4+ messages in thread
From: Noah Silverman @ 2003-01-24 03:32 UTC (permalink / raw)
To: pgsql-performance
To preface my question, we are still in the process of evaluating postgres
to determine if we want to switch our production environment over.
I'm curious about where I can find documentation about crash recovery in
postgres. In mysql, there is a nice table recovery utility (myisamchk).
is there something similar in postgres? What do we do if a table or
database becomes corrupted? (I'm aware of backup techniques, but it isn't
feasible for some of our larger tables. We're already running on raid 5,
but can't do much more)
Thanks,
-N
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Crash Recovery
2003-01-24 03:32 Crash Recovery Noah Silverman <noah@allresearch.com>
@ 2003-01-24 06:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-01-24 06:29 UTC (permalink / raw)
To: Noah Silverman <noah@allresearch.com>; +Cc: pgsql-performance
Noah Silverman <noah@allresearch.com> writes:
> I'm curious about where I can find documentation about crash recovery in
> postgres. In mysql, there is a nice table recovery utility (myisamchk).
> is there something similar in postgres?
There are no automated recovery tools for Postgres, because there are
no known failure modes that are systematic enough to allow automatic
recovery. We prefer to fix such bugs rather than patch around them.
There are some last-ditch tools for reconstructing indexes (REINDEX)
and for throwing away the WAL log (pg_resetxlog) but I have not seen
any recent cases where I would have felt that blind invocation of either
would be a good move.
> What do we do if a table or
> database becomes corrupted? (I'm aware of backup techniques, but it isn't
> feasible for some of our larger tables.
Reconsider that. If your data center burns down tonight, what is your
fallback? Ultimately, you *must* have a backup copy, or you're just not
taking the possibility of failure seriously.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Crash Recovery
2003-01-24 03:32 Crash Recovery Noah Silverman <noah@allresearch.com>
@ 2003-01-24 13:22 ` Andrew Sullivan <andrew@libertyrms.info>
2 siblings, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2003-01-24 13:22 UTC (permalink / raw)
To: pgsql-performance
On Thu, Jan 23, 2003 at 10:32:58PM -0500, Noah Silverman wrote:
> To preface my question, we are still in the process of evaluating postgres
> to determine if we want to switch our production environment over.
>
> I'm curious about where I can find documentation about crash recovery in
> postgres. In mysql, there is a nice table recovery utility (myisamchk).
It recovers automatically. Make sure you run with fsync turned on.
That calls fsync on the WAL at the point of every COMMIT, and COMMIT
isn't finished before the fsync returns. Then, in case of a crash,
the WAL just plays back and fixes up the data area.
> is there something similar in postgres? What do we do if a table or
> database becomes corrupted? (I'm aware of backup techniques, but it isn't
I have never had a table become corrupted under Postgres. There have
been some recent cases where people's bad hardware caused bad data to
make it into a table. Postgres's error reporting usually saves you
there, because you can go in and stomp on the bad tuple if need be.
There are some utilities to help in this; one of them, from Red Hat,
allows you to look at the binary data in various formats (it's pretty
slick). I believe it's available from sources.redhat.com/rhdb.
> feasible for some of our larger tables. We're already running on raid 5,
> but can't do much more)
I suspect you can. First, are you using ECC memory in your
production machines? If not, start doing so. Now. It is _the most
important_ thing, aside from RAID, that you can do to protect your
data. Almost every problem of inconsistency I've seen on the lists
in the past year and a bit has been to do with bad hardware --
usually memory or disk controllers. (BTW, redundant disk
controllers, and ones with some intelligence built in so that they
check themsleves, are also mighty valuable here. But memory goes bad
way more often.)
Also, I'm not sure just what you mean about backups "not being
feasible" for some of the larger tables, but you need to back up
daily. Since pg_dump takes a consistent snapshot, there's no data
inconsistency trouble, and you can just start the backup and go away.
If the resulting files are too large, use split. And if the problem
is space, well, disk is cheap these days, and so is tape, compared to
having to re-get the data you lost.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Crash Recovery
2003-01-24 03:32 Crash Recovery Noah Silverman <noah@allresearch.com>
@ 2003-01-24 13:52 ` Ron Johnson <ron.l.johnson@cox.net>
2 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2003-01-24 13:52 UTC (permalink / raw)
To: pgsql-performance
On Thu, 2003-01-23 at 21:32, Noah Silverman wrote:
> To preface my question, we are still in the process of evaluating postgres
> to determine if we want to switch our production environment over.
>
> I'm curious about where I can find documentation about crash recovery in
> postgres. In mysql, there is a nice table recovery utility (myisamchk).
> is there something similar in postgres? What do we do if a table or
> database becomes corrupted? (I'm aware of backup techniques, but it isn't
> feasible for some of our larger tables. We're already running on raid 5,
> but can't do much more)
Of course it's feasible!! If corporations can backup terrabyte-sized
databases, then you can backup your comparatively puny DB.
In fact, if your data is vital to your company, you *must* back it
up. Otherwise, poof goes the company if the computer is destroyed.
Now, it might cost some bucks to buy a tape drive, or a multi-loader,
if you have *lots* of data, but it *can* be done...
Btw, what happens if an obscure bug in the RAID controller shows is
head, and starts corrupting your data? A table recovery utility
wouldn't do squat, then...
--
+---------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron.l.johnson@cox.net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "Fear the Penguin!!" |
+---------------------------------------------------------------+
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-01-24 13:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-24 03:32 Crash Recovery Noah Silverman <noah@allresearch.com>
2003-01-24 06:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-24 13:22 ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-24 13:52 ` Ron Johnson <ron.l.johnson@cox.net>
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