From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jos van Roosmalen <josr@josr.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: Help: my table is corrupt!
Date: Sun, 27 Mar 2005 11:23:00 -0500
Message-ID: <5730.1111940580@sss.pgh.pa.us> (raw)
In-Reply-To: <67AC5485-F0F9-4118-A053-A1E5D7807D9D@josr.org>
References: <67AC5485-F0F9-4118-A053-A1E5D7807D9D@josr.org>
Jos van Roosmalen <josr@josr.org> writes:
> Can someone explain me how to repair a table?
> If I execute a 'select * from <brokentable>' 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, josr@josr.org
Subject: Re: Help: my table is corrupt!
In-Reply-To: <5730.1111940580@sss.pgh.pa.us>
References: <67AC5485-F0F9-4118-A053-A1E5D7807D9D@josr.org> <5730.1111940580@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox