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 133FDD6D71 for ; Mon, 22 Aug 2005 12:00:36 -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 91497-04 for ; Mon, 22 Aug 2005 15:00:29 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 53D91D701F for ; Mon, 22 Aug 2005 12:00:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7MF0EPO015946; Mon, 22 Aug 2005 11:00:14 -0400 (EDT) To: Sean Chittenden Cc: pgsql-admin@postgresql.org Subject: Re: Corrupted data, best course of repair? In-reply-to: <20050822065149.GD71159@sean.gigave.com> References: <20050822065149.GD71159@sean.gigave.com> Comments: In-reply-to Sean Chittenden message dated "Sun, 21 Aug 2005 23:51:49 -0700" Date: Mon, 22 Aug 2005 11:00:14 -0400 Message-ID: <15945.1124722814@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/265 X-Sequence-Number: 18584 Sean Chittenden writes: > Today I was charged with fixing a DB, and upon looking into it, I > discovered that there was gigs upon gigs of the errors from below. > Has anyone seen this or, more interesting to me, recovered from this > kind of a failure mode? > Here's a bit of background. The system is a good system with verified > disks/FS, etc., but it's a busy database and (here's what's bugging > me): it's using pg_autovacuum *AND* is a system that gets a power > cycle every few weeks[1]. I'm worried that a non-exclusive vacuum > during recovery is causing some kind of problem. The system was > running fine under 7.4.8 for months, but after an upgrade to 8, > they've been experiencing some "interesting characteristics" that led > them to resolve the system getting wedged by "readjusting a power cord > or toggling a power switch[1]." Any helpful advice? reindex? I > still have the original data files, but they're sizable (~30GB) and I > haven't had a chance to really experiment. A dump/reload has been > done and data is sporadically missing (less than 0.01%) or in some > cases, there are duplicate rows. I'm guessing this is due to data > that's been updated since some kind of index/corruption occurred > during the previous "reboot." fsync was off. 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. 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. 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. regards, tom lane