X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 94EA6D769B for ; Mon, 22 Aug 2005 14:46:08 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81770-10 for ; Mon, 22 Aug 2005 17:46:07 +0000 (GMT) Received: from mailhost.gigave.com (mailhost.gigave.com [38.113.228.14]) by svr1.postgresql.org (Postfix) with ESMTP id E299CD732B for ; Mon, 22 Aug 2005 14:46:06 -0300 (ADT) Date: Mon, 22 Aug 2005 10:46:06 -0700 From: Sean Chittenden To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: Corrupted data, best course of repair? Message-ID: <20050822174606.GA46390@sean.gigave.com> References: <20050822065149.GD71159@sean.gigave.com> <15945.1124722814@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <15945.1124722814@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200508/271 X-Sequence-Number: 18590 > They run with fsync off AND they like to toggle the power switch at > random? I'd suggest finding other employment --- they couldn't > possibly be paying you enough to justify cleaning up after stupidity > as gross as that. Colo-by-windows. If there weren't DBAs with Win32 admin tendencies, I'd be out of work. :) > Anyway, the errors appear to indicate that there are pages in the > database with LSN (last WAL location) larger than the actual current > end of WAL. The difference is pretty large though --- at least 85MB > of WAL seems to have gone missing. My first thought was a corrupted > LSN field. But seeing that there are at least two such pages, and > given the antics you describe above, what seems more likely is that > the LSNs were correct when written. I think some page of WAL never > made it to disk during a period of heavy updates that was terminated > by a power cycle, and during replay we stopped at the first point > where the WAL data was detectably corrupt, and so a big chunk of WAL > never got replayed. Which of course means there's probably a lot of > stuff that needs to be fixed and did not get fixed, but in > particular our idea of the current end-of-WAL address is a lot less > than it should be. If you have the server log from just after the > last postmaster restart, looking at what terminated the replay might > confirm this. Peachy. > You could get the DB to stop complaining by doing a pg_resetxlog to > push the WAL start address above the largest "flush request" > mentioned in any of the messages. But my guess is that you'll find > a lot of internal corruption after you do it. Going back to the > dump might be a saner way to proceed. Tons of corruption and a backup that's a few weeks old. *grin* The most recent dump seems to have all of the data, but some rows are there in duplicate. Thanks for the input. -sc -- Sean Chittenden