Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 21721634296 for ; Thu, 27 May 2010 04:49:48 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 24634-04 for ; Thu, 27 May 2010 07:49:40 +0000 (UTC) X-Greylist: delayed 00:15:29.785854 by SQLgrey-1.7.6 Received: from mail2.sun.ac.za (mail2.sun.ac.za [146.232.64.14]) by mail.postgresql.org (Postfix) with ESMTP id E74AC632BCA for ; Thu, 27 May 2010 04:49:39 -0300 (ADT) Received: from werkesel.sun.ac.za ([146.232.129.30]) by mail2.sun.ac.za with esmtp (Exim 4.63) (envelope-from ) id 1OHXbQ-0002w8-F0 for pgsql-admin@postgresql.org; Thu, 27 May 2010 09:34:05 +0200 Received: from js by werkesel.sun.ac.za with local (Exim 4.71) (envelope-from ) id 1OHXbQ-0008QZ-CQ for pgsql-admin@postgresql.org; Thu, 27 May 2010 09:34:04 +0200 Date: Thu, 27 May 2010 09:34:04 +0200 From: Johann Spies To: pgsql-admin@postgresql.org Subject: Corrupted database - how to recover Message-ID: <20100527073404.GA32267@sun.ac.za> Mail-Followup-To: Johann Spies , pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.20 (2009-06-14) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.51 tagged_above=-10 required=5 tests=BAYES_50=0.8, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/259 X-Sequence-Number: 34732 After two disks on our raid-5 system failed causing a filesystem failure. We could get one online again and the filesystem is at present usable but I cannot start postgresql at the moment: $ /usr/lib/postgresql/8.3/bin/postgres --single -D /etc/postgresql/8.3/main/ 2010-05-27 09:22:48 SAST FATAL: could not stat directory "base/16400": Structure needs cleaning 2010-05-27 09:22:48 SAST CONTEXT: xlog redo insert: rel 1663/16400/16438; tid 10960/41 in 'base' I see drwx------ 10 postgres postgres 102 2010-04-22 15:09 . drwx------ 10 postgres postgres 4096 2010-05-27 09:29 .. drwx------ 2 postgres postgres 4096 2010-05-20 02:11 1 drwx------ 2 postgres postgres 4096 2010-04-22 15:07 11510 ?????????? ? ? ? ? ? 16388 drwx------ 2 postgres postgres 4096 2010-05-20 02:11 16389 drwx------ 2 postgres postgres 4096 2010-05-20 02:11 16390 drwx------ 2 postgres postgres 4096 2010-05-20 02:11 16391 ?????????? ? ? ? ? ? 16400 I can just move the data-directory, do an initdb and use an older dump to repair the database as it was some time ago. Fortunately the data is not mission critical. But what if it was. How do I recover from a situation like this? Regards Johann -- Johann Spies Telefoon: 021-808 4599 Informasietegnologie, Universiteit van Stellenbosch "All that the Father giveth me shall come to me; and him that cometh to me I will in no wise cast out." John 6:37