Recovering data from corrupted table. Urgent Help!!

**Gaetano Mendola** · Nov 23 '05, 02:30 AM

Re: Recovering data from corrupted table. Urgent Help!!

ruben20@supergu ai.com wrote:[color=blue]
> Hi:
>
> Is there any way to recover data from a corrupted table? I can only run
> SELECTs on certain WHERE conditions.
>
> I cannot vacuum, pg_dump, I've deleted the indexes and try to reindex,
> always get error:
>
> ERROR: could not access status of transaction 4244329
> DETAIL: could not open file "/usr/local/pgsql/data/pg_clog/0004": No
> existe el fichero o el directorio
>
> Thanks a lot.[/color]

Again:

create an empty file:

# touch /usr/local/pgsql/data/pg_clog/0004

at this point postgres will complain about the fact that an offset is missing,

at this point fill with 0 your file ( blocks of 8K ) till reach that offset reclaimed.

Regards
Gaetano Mendola

**Gaetano Mendola** · Nov 23 '05, 02:30 AM

Re: Recovering data from corrupted table. Urgent Help!!

Gaetano Mendola wrote:[color=blue]
> ruben20@supergu ai.com wrote:
>[color=green]
>> Hi:
>>
>> Is there any way to recover data from a corrupted table? I can only
>> run SELECTs on certain WHERE conditions.
>>
>> I cannot vacuum, pg_dump, I've deleted the indexes and try to reindex,
>> always get error:
>>
>> ERROR: could not access status of transaction 4244329
>> DETAIL: could not open file "/usr/local/pgsql/data/pg_clog/0004": No
>> existe el fichero o el directorio
>>
>> Thanks a lot.[/color]
>
>
> Again:
>
>
> create an empty file:
>
> # touch /usr/local/pgsql/data/pg_clog/0004
>
> at this point postgres will complain about the fact that an offset is
> missing,
>
> at this point fill with 0 your file ( blocks of 8K ) till reach that
> offset reclaimed.[/color]

I forgot to suggest you to do:

dd bs=8k count=1 < /dev/zero >> /usr/local/pgsql/data/pg_clog/0004

you have to repeat this command till the offset is covered.

Regards
Gaetano Mendola

**Martijn van Oosterhout** · Nov 23 '05, 02:30 AM

Re: Recovering data from corrupted table. Urgent Help!!

Create a file with that name filled with zeros with the same length as
the other files in that directory. That should get you far enough to
dump the data. Then run a complete set of memory and disk checks on
your system...

On Wed, Oct 13, 2004 at 02:56:37PM +0100, ruben20@supergu ai.com wrote:[color=blue]
> Hi:
>
> Is there any way to recover data from a corrupted table? I can only run
> SELECTs on certain WHERE conditions.
>
> I cannot vacuum, pg_dump, I've deleted the indexes and try to reindex,
> always get error:
>
> ERROR: could not access status of transaction 4244329
> DETAIL: could not open file "/usr/local/pgsql/data/pg_clog/0004": No
> existe el fichero o el directorio
>
> Thanks a lot.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBbUf+Y5T wig3Ge+YRAogRAK CSUDlH1FJq09Shs ITqeN5iClDaYACg pzII
FvTIPKtBUXuoME7 +g+Vtq1M=
=ZVvW
-----END PGP SIGNATURE-----

**ruben** · Nov 23 '05, 02:30 AM

Re: Recovering data from corrupted table. Urgent Help!!

Hi Gaetano:

This procedure to recover data from a corrupted table should be
documented somewhere... If it is, I could not find it!

Now I wonder if I have lost any data, because after creating the
pg_clog/0004 and running VACCUM everything seems ok.

Thanks a lot for your help.
Ruben.
[color=blue]
> Gaetano Mendola wrote:
>[color=green]
>>ruben20@super guai.com wrote:
>>
>>[color=darkred]
>>>Hi:
>>>
>>>Is there any way to recover data from a corrupted table? I can only
>>>run SELECTs on certain WHERE conditions.
>>>
>>>I cannot vacuum, pg_dump, I've deleted the indexes and try to reindex,
>>>always get error:
>>>
>>>ERROR: could not access status of transaction 4244329
>>>DETAIL: could not open file "/usr/local/pgsql/data/pg_clog/0004": No
>>>existe el fichero o el directorio
>>>
>>>Thanks a lot.[/color]
>>
>>
>>Again:
>>
>>
>>create an empty file:
>>
>># touch /usr/local/pgsql/data/pg_clog/0004
>>
>>at this point postgres will complain about the fact that an offset is
>>missing,
>>
>>at this point fill with 0 your file ( blocks of 8K ) till reach that
>>offset reclaimed.[/color]
>
>
> I forgot to suggest you to do:
>
> dd bs=8k count=1 < /dev/zero >> /usr/local/pgsql/data/pg_clog/0004
>
> you have to repeat this command till the offset is covered.
>
> Regards
> Gaetano Mendola[/color]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Gaetano Mendola** · Nov 23 '05, 02:31 AM

Re: Recovering data from corrupted table. Urgent Help!!

ruben wrote:[color=blue]
> Hi Gaetano:
>
> This procedure to recover data from a corrupted table should be
> documented somewhere... If it is, I could not find it![/color]

First of all the table was not corrupted, the glitch was in another
subsystem.
The procedure is documented in the archives :-(

I agree with you but any cure seems worst then the disease. You have understand
why the file 0004 was not anymore there, did you had a power failure for
example ?
[color=blue]
> Now I wonder if I have lost any data, because after creating the
> pg_clog/0004 and running VACCUM everything seems ok.[/color]

Normally you didn't lost any data.

Regards
Gaetano Mendola

PS: I had the same error for the first time in my postgres usage life
only after ( some weeks after ) having upgrade from a 7.4.2 -> 7.4.5