Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 96020475EDF for ; Fri, 24 Jan 2003 08:22:16 -0500 (EST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by postgresql.org (Postfix) with ESMTP id 0A338475D87 for ; Fri, 24 Jan 2003 08:22:16 -0500 (EST) Received: from andrew by mail.libertyrms.com with local (Exim 3.22 #3 (Debian)) id 18c3ml-0007Dz-00 for ; Fri, 24 Jan 2003 08:22:19 -0500 Date: Fri, 24 Jan 2003 08:22:19 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Crash Recovery Message-ID: <20030124082219.B26558@mail.libertyrms.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <2147483647.1043361178@[10.0.1.133]> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <2147483647.1043361178@[10.0.1.133]>; from noah@allresearch.com on Thu, Jan 23, 2003 at 10:32:58PM -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/278 X-Sequence-Number: 925 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 M2P 2A8 +1 416 646 3304 x110