pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew Sullivan <andrew@libertyrms.info>
To: pgsql-performance@postgresql.org
Subject: Re: Crash Recovery
Date: Fri, 24 Jan 2003 08:22:19 -0500
Message-ID: <20030124082219.B26558@mail.libertyrms.com> (raw)
In-Reply-To: <2147483647.1043361178@[10.0.1.133]>
References: <2147483647.1043361178@[10.0.1.133]>

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




view thread (4+ messages)  latest in thread

Message-ID: <20030124082219.B26558@mail.libertyrms.com>
Permalink:  ../20030124082219.B26558@mail.libertyrms.com/
Also on:    postgresql.org/message-id/20030124082219.B26558@mail.libertyrms.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: andrew@libertyrms.info
  Subject: Re: Crash Recovery
  In-Reply-To: <20030124082219.B26558@mail.libertyrms.com>
  References: <2147483647.1043361178@[10.0.1.133]> <20030124082219.B26558@mail.libertyrms.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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