Re: table corruption

--- Thilo Hille <thilo(at)resourcery(dot)de> wrote:
> Hi,
> i am using postgresql-7.2.3. One of the tables in my
> database seems to be
> corrupted.
> It has about 250000 records, but when dumping the
> table postmaster crashes
> when querys exceeding row 152238.
>
> "select * from prvlog limit 1,152238;" works fine
>
> "select * from prvlog limit 1,152239;" results in:
> server closed the connection unexpectedly
> This probably means the server terminated
> abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting
> reset: Succeeded.
>
> The same happens to anything above 152239. (well, i
> didnt tried all...)
> The first (regarding to the tabledefinition) 2
> fields of row 152239 can be
> viewed. Querying other rows show the above error.
> There is no index on the table. Is there something
> i can do to rescue the
> data?

If you search the archives (or via Google) you will
find advice on recovering. I believe there is really
no way to "recover" the corrupted data, the best you
will be able to do is delete the corrupted record(s)
(You may be able to get away with setting the affected
fields to null).

The best way to unequivocally identify the corrupt
records is by ctid. Do "select ctid, * ..." until you
are sure that you have identified the corrupt
record(s), then delete it/them. Then run "vacuum
full". If that goes ok, you should be able to dump
without trouble.

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com