X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 28D88D1CAD1 for ; Wed, 19 May 2004 20:44:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 62008-05 for ; Wed, 19 May 2004 20:44:23 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id EDB9DD1B1AA for ; Wed, 19 May 2004 20:44:19 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i4JNiLOl022444; Wed, 19 May 2004 19:44:21 -0400 (EDT) To: Stefan Holzheu Cc: ADMIN Subject: Re: Servercrash In-reply-to: <40AB87CC.8020500@bitoek.uni-bayreuth.de> References: <40AB87CC.8020500@bitoek.uni-bayreuth.de> Comments: In-reply-to Stefan Holzheu message dated "Wed, 19 May 2004 18:14:04 +0200" Date: Wed, 19 May 2004 19:44:21 -0400 Message-ID: <22443.1085010261@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/204 X-Sequence-Number: 13520 Stefan Holzheu writes: > yesterday our database-server crashed (raid-error). After reparing the > filesystem (ext2) we have a problem with our lagest table in the database. > vaccuum aborts with the following meassage (sorry it is in German): > DEBUG: vacuume »messungen.massendaten« > HINWEIS: Relation »massendaten« TID 211540/73: > DeleteTransactionInProgress 16785658 --- kann Relation nicht verkleinern > (cannot shrink relation) > DEBUG: AbortCurrentTransaction > FEHLER: ungültiger Seitenkopf in Block 354500 von Relation > »massendaten« (unvalid pagehead in block ...) > All other operations using ctid 211540/73 give: > FEHLER: konnte auf den Status von Transaktion 16785658 nicht zugreifen > (could not get status of transaction ...) > DETAIL: konnte Datei »/var/lib/pgsql/data/pg_clog/0010« nicht öffnen: > Datei oder Verzeichnis nicht gefunden > Is there a way to repair the database? You have at least two corrupted pages in that table: page 354500 has a header problem, and in page 211540 there's a bogus transaction ID in a tuple header. These are the *minimum* descriptions of the data lossage, it's entirely likely that large parts of the pages involved are junk. I would suggest proceeding by examining those pages with pg_filedump or another tool. If you can make some sense of the damage it might be possible to do a selective repair. If not, your best bet is to just zero out the damaged pages --- this will lose the rows that are on those pages, but at least you can get the rest of the table operational again. You can find more about this by looking in the mail list archives. Threads mentioning pg_filedump would be good places to start. regards, tom lane