Recovering data from corrupted table. Urgent Help!!

#1

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

#2

ruben20 (AT) superguai (DOT) com wrote:

Quote:

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

#3

Gaetano Mendola wrote:

Quote:

ruben20 (AT) superguai (DOT) 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

#4

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 (AT) superguai (DOT) com wrote:

Quote:

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 (AT) svana (DOT) org> http://svana.org/kleptog/

Quote:

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-----

#5

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.

Quote:

Gaetano Mendola wrote:

ruben20 (AT) superguai (DOT) 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

#6

ruben wrote:

Quote:

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 ?

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Recovering data from corrupted table. Urgent Help!!

comp.databases.postgresql.general comp.databases.postgresql.general

Recovering data from corrupted table. Urgent Help!! - 10-13-2004 , 08:56 AM

Re: Recovering data from corrupted table. Urgent Help!! - 10-13-2004 , 09:54 AM

Re: Recovering data from corrupted table. Urgent Help!! - 10-13-2004 , 09:57 AM

Re: Recovering data from corrupted table. Urgent Help!! - 10-13-2004 , 10:21 AM

Re: Recovering data from corrupted table. Urgent Help!! - 10-13-2004 , 03:02 PM

Re: Recovering data from corrupted table. Urgent Help!! - 10-14-2004 , 04:09 AM