X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E4BC6535FC for ; Sun, 27 Mar 2005 17:23:09 +0100 (BST) 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 53715-05 for ; Sun, 27 Mar 2005 16:23:04 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id DBE02534E4 for ; Sun, 27 Mar 2005 17:23:03 +0100 (BST) 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 j2RGN0qB005731; Sun, 27 Mar 2005 11:23:00 -0500 (EST) To: Jos van Roosmalen Cc: pgsql-admin@postgresql.org Subject: Re: Help: my table is corrupt! In-reply-to: <67AC5485-F0F9-4118-A053-A1E5D7807D9D@josr.org> References: <67AC5485-F0F9-4118-A053-A1E5D7807D9D@josr.org> Comments: In-reply-to Jos van Roosmalen message dated "Sun, 27 Mar 2005 17:19:33 +0200" Date: Sun, 27 Mar 2005 11:23:00 -0500 Message-ID: <5730.1111940580@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/448 X-Sequence-Number: 16946 Jos van Roosmalen writes: > Can someone explain me how to repair a table? > If I execute a 'select * from ' in PSQL I get the next > error. A pg_dump export only partially export the broken table and > ends with the same error: > "Invalid memory alloc request size: 4294967290" What you've probably got is a variable-width field whose length word is corrupted. The best procedure is to identify and delete the broken row (or rows; it's likely that the corruption hit more than just one byte). You can find more details about this in the archives but the short answer is that you do this by trial-and-error. I'd do something like: 1. Determine how far into the table the bad value is, by finding the smallest N such that select * from tab offset N limit 1 fails. This is a bit tedious but if you understand the principle of binary search (a/k/a divide and conquer) it won't take long. 2. Find a way to name that row, and delete it. ctid always works: select ctid from tab offset N limit 1 delete from tab where ctid = 'that value' You might also select as much as you can out of the row before you delete it, to find out what it is you are losing. Usually, you'll be able to select all the columns to the left of the corrupted one. select a,b,c from tab where ctid = 'that value' 3. Can you pg_dump the table yet? If not, return to step 1. This assumes that you just want to get rid of the bad data ASAP and not try to do any forensics to discover what happened. If you want to study the corruption in detail you could use pg_filedump on the damaged page. The first component of ctid is the page number. > Is there a Postgresql Repair application which delete or repair > corrupt records? I've not seen anything that really looks at the data contents of rows. regards, tom lane