By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,374 Members | 2,014 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,374 IT Pros & Developers. It's quick & easy.

Recovering data from corrupted table. Urgent Help!!

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
ru*****@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
Nov 23 '05 #2

P: n/a
Gaetano Mendola wrote:
ru*****@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


Nov 23 '05 #3

P: n/a
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, ru*****@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 <kl*****@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.


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

iD8DBQFBbUf+Y5Twig3Ge+YRAogRAKCSUDlH1FJq09ShsITqeN 5iClDaYACgpzII
FvTIPKtBUXuoME7+g+Vtq1M=
=ZVvW
-----END PGP SIGNATURE-----

Nov 23 '05 #4

P: n/a
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:
ru*****@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



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

Nov 23 '05 #5

P: n/a
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

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.