Thread: Problem with a transaction on dump
Hello everyone, It is the second time i got this error message (sorry, its french, i prefer to c/p this one than making a bad translation) : -------8<------------8<-----------8<----------- pg_dump: ERREUR: Impossible d'accéder au statut de la transaction 892415538 DETAIL: Impossible d'ouvrir le fichier «/var/lib/postgresql/7.4/main/pg_clog/0353» : Aucun fichier ou répertoire de ce type pg_dump: La commande SQL de sauvegarde du contenu de la table "facture" a échoué : échec de PQendcopy(). pg_dump: Message d'erreur du serveur : ERREUR: Impossible d'accéder au statut de la transaction 892415538 DETAIL: Impossible d'ouvrir le fichier «/var/lib/postgresql/7.4/main/pg_clog/0353» : Aucun fichier ou répertoire de ce type pg_dump: La commande était : COPY public.facture ([...]) TO stdout; -------8<------------8<-----------8<----------- It say that it cant access to the transaction id 892415538 because the file /var/lib/postgresql/7.4/main/pg_clog/0353 dont exist. And this file doesn't exist. I use PG 7.4.16 on linux debian etch. I already made a HD & memory test, and i find nothing wrong. Last time i got this error, the only solution was to get a "safe" backup and lost some data. But i didnt have time to manage this server, so this error is here for a couple of day, and im afraid about how many data may be lost. So if someone have an idea to have a "safe" backup and start on a clean database... Thx in advance, Regards,
Froggy / Froggy Corp. wrote: > Hello everyone, > > It is the second time i got this error message (sorry, its french, i > prefer to c/p this one than making a bad translation) : > > -------8<------------8<-----------8<----------- > > pg_dump: ERREUR: Impossible d'accéder au statut de la transaction > 892415538 > DETAIL: Impossible d'ouvrir le fichier > «/var/lib/postgresql/7.4/main/pg_clog/0353» : Aucun fichier ou > répertoire de ce type What files are there in main/pg_clog? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > > Froggy / Froggy Corp. wrote: > > Hello everyone, > > > > It is the second time i got this error message (sorry, its french, i > > prefer to c/p this one than making a bad translation) : > > > > -------8<------------8<-----------8<----------- > > > > pg_dump: ERREUR: Impossible d'accéder au statut de la transaction > > 892415538 > > DETAIL: Impossible d'ouvrir le fichier > > «/var/lib/postgresql/7.4/main/pg_clog/0353» : Aucun fichier ou > > répertoire de ce type > > What files are there in main/pg_clog? 0000 to 0024 --------------------8<------------8<----------------8<-------- -rw------- 1 postgres postgres 262144 Jul 25 2006 0000 -rw------- 1 postgres postgres 262144 Jul 28 2006 0001 -rw------- 1 postgres postgres 262144 Aug 5 2006 0002 -rw------- 1 postgres postgres 262144 Aug 13 2006 0003 -rw------- 1 postgres postgres 262144 Aug 22 2006 0004 -rw------- 1 postgres postgres 262144 Aug 28 2006 0005 -rw------- 1 postgres postgres 262144 Sep 4 2006 0006 -rw------- 1 postgres postgres 262144 Sep 10 2006 0007 -rw------- 1 postgres postgres 262144 Sep 17 2006 0008 -rw------- 1 postgres postgres 262144 Sep 25 2006 0009 -rw------- 1 postgres postgres 262144 Oct 2 2006 000A -rw------- 1 postgres postgres 262144 Oct 9 19:50 000B -rw------- 1 postgres postgres 262144 Oct 17 06:04 000C -rw------- 1 postgres postgres 262144 Oct 25 11:13 000D -rw------- 1 postgres postgres 262144 Nov 2 12:44 000E -rw------- 1 postgres postgres 262144 Nov 8 21:41 000F -rw------- 1 postgres postgres 262144 Nov 16 10:03 0010 -rw------- 1 postgres postgres 262144 Nov 23 07:56 0011 -rw------- 1 postgres postgres 262144 Nov 29 22:48 0012 -rw------- 1 postgres postgres 262144 Dec 6 16:36 0013 -rw------- 1 postgres postgres 262144 Dec 13 02:15 0014 -rw------- 1 postgres postgres 262144 Dec 19 15:57 0015 -rw------- 1 postgres postgres 262144 Dec 27 04:34 0016 -rw------- 1 postgres postgres 262144 Jan 2 22:14 0017 -rw------- 1 postgres postgres 262144 Jan 8 17:27 0018 -rw------- 1 postgres postgres 262144 Jan 17 16:02 0019 -rw------- 1 postgres postgres 262144 Jan 24 00:18 001A -rw------- 1 postgres postgres 262144 Jan 31 10:03 001B -rw------- 1 postgres postgres 262144 Feb 6 17:23 001C -rw------- 1 postgres postgres 262144 Feb 13 13:34 001D -rw------- 1 postgres postgres 262144 Feb 20 22:23 001E -rw------- 1 postgres postgres 262144 Feb 28 18:03 001F -rw------- 1 postgres postgres 262144 Mar 8 09:58 0020 -rw------- 1 postgres postgres 262144 Mar 16 15:07 0021 -rw------- 1 postgres postgres 262144 Mar 25 04:02 0022 -rw------- 1 postgres postgres 262144 Apr 2 14:21 0023 -rw------- 1 postgres postgres 81920 Apr 4 16:17 0024 ---------8<------------8<--------------8<----------
Froggy / Froggy Corp. wrote: > > > Alvaro Herrera wrote: > > > > Froggy / Froggy Corp. wrote: > > > Hello everyone, > > > > > > It is the second time i got this error message (sorry, its french, i > > > prefer to c/p this one than making a bad translation) : > > > > > > -------8<------------8<-----------8<----------- > > > > > > pg_dump: ERREUR: Impossible d'accéder au statut de la transaction > > > 892415538 > > > DETAIL: Impossible d'ouvrir le fichier > > > «/var/lib/postgresql/7.4/main/pg_clog/0353» : Aucun fichier ou > > > répertoire de ce type > > > > What files are there in main/pg_clog? > > 0000 to 0024 FWIW, I examined the bit pattern in the number 892415538, which is 110101001100010010111000110010, and taken as octets interpreted as ASCII chars, they look like this: 51.2 So my guess is that your table was corrupted by the filesystem or something like that. Did your system crash recently? My guess is that this is a filesystem problem, kernel bug or something like that. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Froggy / Froggy Corp. wrote:
>> pg_dump: ERREUR: Impossible d'acc�der au statut de la transaction
>> 892415538
> What files are there in main/pg_clog?
Doesn't really matter --- I'd say it's pretty obviously a data corruption
problem. That value equates to 35312e32 hex which looks way too much
like ASCII. Investigation with pg_filedump might give some clue about
what happened, but if you just want to salvage what you can, the key is
to identify and zero out the broken page. See past discussions of
recovery from data corruption in the archives.
regards, tom lane
Alvaro Herrera wrote: > > Froggy / Froggy Corp. wrote: > > > > > > Alvaro Herrera wrote: > > > > > > Froggy / Froggy Corp. wrote: > > > > Hello everyone, > > > > > > > > It is the second time i got this error message (sorry, its french, i > > > > prefer to c/p this one than making a bad translation) : > > > > > > > > -------8<------------8<-----------8<----------- > > > > > > > > pg_dump: ERREUR: Impossible d'accéder au statut de la transaction > > > > 892415538 > > > > DETAIL: Impossible d'ouvrir le fichier > > > > «/var/lib/postgresql/7.4/main/pg_clog/0353» : Aucun fichier ou > > > > répertoire de ce type > > > > > > What files are there in main/pg_clog? > > > > 0000 to 0024 > > FWIW, I examined the bit pattern in the number 892415538, which is > 110101001100010010111000110010, and taken as octets interpreted as ASCII > chars, they look like this: > > 51.2 > > So my guess is that your table was corrupted by the filesystem or > something like that. Did your system crash recently? My guess is that > this is a filesystem problem, kernel bug or something like that. Yep i got a crash before the first problem. But the dump run nicely until a day it crash with this kind of error. BTW, beetween, i put all data on a new (safe ?) database, and i dont have any kind of crash. Actually, do you know a way to get back a maximum of data ? I have a particular thing : i have one dump with 13MB and other are 5MB. I dont really understand the exact error message. That mean that on a transaction, the DB crash (or something like that), so i have an INSERT/UPDATE or something like that which is lost, but other data are not corrupted ? Or it is an index which is corrupted, so more data are corrupted too ?