Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5807263309E for ; Fri, 13 Feb 2009 13:57:10 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 42954-08 for ; Fri, 13 Feb 2009 13:57:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mk-filter-3-a-1.mail.uk.tiscali.com (mk-filter-3-a-1.mail.uk.tiscali.com [212.74.100.54]) by mail.postgresql.org (Postfix) with ESMTP id A680163225E for ; Fri, 13 Feb 2009 13:57:07 -0400 (AST) X-Trace: 145699559/mk-filter-3.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.46.2/None/john.lister-ps@kickstone.com X-SBRS: None X-RemoteIP: 80.225.46.2 X-IP-MAIL-FROM: john.lister-ps@kickstone.com X-MUA: Thunderbird 2.0.0.19 (Windows/20081209) X-IP-BHB: Once X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AsoBADxDlUlQ4S4C/2dsb2JhbAAI0kKEHAY X-IronPort-AV: E=Sophos;i="4.38,203,1233532800"; d="scan'208";a="145699559" Received: from 80-225-46-2.dynamic.dial.as9105.com (HELO [192.168.6.11]) ([80.225.46.2]) by smtp.tiscali.co.uk with ESMTP; 13 Feb 2009 17:57:04 +0000 Message-ID: <4995B477.7090805@kickstone.com> Date: Fri, 13 Feb 2009 17:57:11 +0000 From: John Lister User-Agent: Thunderbird 2.0.0.19 (Windows/20081209) MIME-Version: 1.0 To: Tom Lane CC: pgsql-hackers@postgresql.org Subject: Re: Database corruption help References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com> <17205.1234200693@sss.pgh.pa.us> <4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us> <49908D72.7040904@kickstone.com> <25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com> <4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us> <4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us> <4994A45B.4030707@kickstone.com> <49952D33.3000707@kickstone.com> <3304.1234542508@sss.pgh.pa.us> In-Reply-To: <3304.1234542508@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/768 X-Sequence-Number: 134058 Cheers, i'll give it ago. I'm probably going to do a full restore over the weekend while i can shut things down without too many complaints... I can save any of the files if you are interested in them later on... JOHN Tom Lane wrote: > John Lister writes: > >> Any help would be appreciated as the pg_class table is constantly >> growing which i'm guessing is going to start to affect performance >> fairly soon. I'd like to avoid a full restore from backup if possible. >> > > BTW, what I would recommend as a recovery action is to zero out that > page of pg_class while the postmaster is stopped. We know that none > of those rows are useful to you, and there shouldn't be any index > entries pointing at them (since they're all HOT tuples), so at least > in theory that won't cause any damage. Then you can try another > VACUUM FULL and see if there are any more pages with, er, issues. > > If you're on a machine that has /dev/zero then something like this > should work: > > dd bs=8k count=1 seek=15538 conv=notrunc if=/dev/zero of=$PGDATA/base/16392/1259 > > but it'd be a good idea to save a copy of the target file so you can try > again if you mess up. > > Also, it'd really be prudent to do a dump, initdb, reload once you > get to a point where pg_dump succeeds without complaints. We don't > have any good way to know what other corruption might be lurking > undetected. > > regards, tom lane >