Thread: Recovering data from corrupted table. Urgent Help!!
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.
ruben20@superguai.com wrote: > 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. 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 wrote: > ruben20@superguai.com wrote: > >> 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. > > > 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. 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
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@superguai.com wrote: > 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 -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > 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.
Attachment
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. > Gaetano Mendola wrote: > >>ruben20@superguai.com wrote: >> >> >>>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. >> >> >>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. > > > 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
ruben wrote: > Hi Gaetano: > > This procedure to recover data from a corrupted table should be > documented somewhere... If it is, I could not find it! 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 ? > Now I wonder if I have lost any data, because after creating the > pg_clog/0004 and running VACCUM everything seems ok. 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
Thanks Martijn: It worked. I was having issues with 7.4.2, run memory ans disk checks and hardware seems ok, that's why I decided to upgrade to 7.4.5, but unfortunately I'm still experiencing data corruption. Ruben. ruben20 wrote: > ---------- Forwarded Message ----------- > From: Martijn van Oosterhout <kleptog@svana.org> > To: "ruben20@superguai.com" <ruben20@superguai.com> > Sent: Wed, 13 Oct 2004 17:21:35 +0200 > Subject: Re: [GENERAL] 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@superguai.com wrote: > >>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 > >