Thread: URGENT! could not access status of transaction

URGENT! could not access status of transaction

From
"Reimer"
Date:
Hello,
 
We had some disk problems here and now pg_dump is complaining with:
 
pg_dump: ERROR:  could not access status of transaction 1768711534
DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0696": Arquivo ou dire
tório não encontrado
pg_dump: SQL command to dump the contents of table "tt_fin" failed: PQendcopy() fa
iled.
pg_dump: Error message from server: ERROR:  could not access status of transaction
 1768711534
DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0696": Arquivo ou dire
tório não encontrado
pg_dump: The command was: COPY "REIMER".tt_fin (codfil, sequen, tipger, tipnum, co
dusu, filger, filnum, filusu, seqger, seqnum, datope, vlrmov, seqtrn, codctr, codc
us, numdoc, observ, flgfin, chvtrn, atu_em) TO stdout;
I know is something related with the hardware and not with postgresql but I don´t want to restore all the database.
 
How is the best way to fix? pg_resetxlog?
 
Thanks in advance!
 
Reimer

Re: URGENT! could not access status of transaction

From
"Reimer"
Date:
I forgot to mention postgreSQL version: 7.4.6
 
Thanks!
----- Original Message -----
From: Reimer
Sent: Friday, March 24, 2006 10:41 AM
Subject: [GENERAL] URGENT! could not access status of transaction

Hello,
 
We had some disk problems here and now pg_dump is complaining with:
 
pg_dump: ERROR:  could not access status of transaction 1768711534
DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0696": Arquivo ou dire
tório não encontrado
pg_dump: SQL command to dump the contents of table "tt_fin" failed: PQendcopy() fa
iled.
pg_dump: Error message from server: ERROR:  could not access status of transaction
 1768711534
DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0696": Arquivo ou dire
tório não encontrado
pg_dump: The command was: COPY "REIMER".tt_fin (codfil, sequen, tipger, tipnum, co
dusu, filger, filnum, filusu, seqger, seqnum, datope, vlrmov, seqtrn, codctr, codc
us, numdoc, observ, flgfin, chvtrn, atu_em) TO stdout;
I know is something related with the hardware and not with postgresql but I don´t want to restore all the database.
 
How is the best way to fix? pg_resetxlog?
 
Thanks in advance!
 
Reimer

Re: URGENT! could not access status of transaction

From
"Jim C. Nasby"
Date:
On Fri, Mar 24, 2006 at 10:51:25AM -0300, Reimer wrote:
> I forgot to mention postgreSQL version: 7.4.6

Had you been running the latest 7.4 release, this probably wouldn't have
happened. There's been a number of data-loss bugs fixed since 7.4.6.

> Thanks!
>   ----- Original Message -----
>   From: Reimer
>   To: pgsql-general@postgresql.org
>   Sent: Friday, March 24, 2006 10:41 AM
>   Subject: [GENERAL] URGENT! could not access status of transaction
>
>
>   Hello,
>
>   We had some disk problems here and now pg_dump is complaining with:
>
>   pg_dump: ERROR:  could not access status of transaction 1768711534
>   DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0696": Arquivo ou dire
>   t?rio n?o encontrado
>   pg_dump: SQL command to dump the contents of table "tt_fin" failed: PQendcopy() fa
>   iled.
>   pg_dump: Error message from server: ERROR:  could not access status of transaction
>    1768711534
>   DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0696": Arquivo ou dire
>   t?rio n?o encontrado
>   pg_dump: The command was: COPY "REIMER".tt_fin (codfil, sequen, tipger, tipnum, co
>   dusu, filger, filnum, filusu, seqger, seqnum, datope, vlrmov, seqtrn, codctr, codc
>   us, numdoc, observ, flgfin, chvtrn, atu_em) TO stdout;
>
>   I know is something related with the hardware and not with postgresql but I don?t want to restore all the database.
>
>   How is the best way to fix? pg_resetxlog?
>
>   Thanks in advance!
>
>   Reimer

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: URGENT! could not access status of transaction

From
Tom Lane
Date:
"Reimer" <carlosreimer@terra.com.br> writes:
> pg_dump: ERROR:  could not access status of transaction 1768711534
> DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0696": =
> Arquivo ou dire
> t=F3rio n=E3o encontrado

> How is the best way to fix? pg_resetxlog?

resetxlog won't help: you've got corrupt data in that table.  See the
archives for previous threads about finding and removing corrupt rows.

I concur with the nearby suggestion that you should be running something
newer than 7.4.6, but even with an update you'll still be needing to
clean up this corruption manually :-(

            regards, tom lane

Re: URGENT! could not access status of transaction

From
"Reimer"
Date:
ok,

I fixed it using the dd command and the pg_dump is not complaining anymore,
but can I trust in my database now?

Thanks in advance!

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Reimer" <carlosreimer@terra.com.br>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, March 24, 2006 1:11 PM
Subject: Re: [GENERAL] URGENT! could not access status of transaction


> "Reimer" <carlosreimer@terra.com.br> writes:
>> pg_dump: ERROR:  could not access status of transaction 1768711534
>> DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0696": =
>> Arquivo ou dire
>> t=F3rio n=E3o encontrado
>
>> How is the best way to fix? pg_resetxlog?
>
> resetxlog won't help: you've got corrupt data in that table.  See the
> archives for previous threads about finding and removing corrupt rows.
>
> I concur with the nearby suggestion that you should be running something
> newer than 7.4.6, but even with an update you'll still be needing to
> clean up this corruption manually :-(
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>