Обсуждение: pg_clog problem (PG version 7.4.5)
hackers, I am having a problem with table (identified by pg_dump). I get the follow error when I try to COPY the table to stdout (or /dev/null). DB=# copy rnk to '/dev/null'; ERROR: could not access status of transaction 1076101119 DETAIL: could not open file "/usr/local/pgsql/data/pg_clog/0402": No such file or directory Pg version is select version(); version -------------------------------------------------------------------------------------------------PostgreSQL 7.4.5 on i686-pc-linux-gnu,compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) I do have a good backup from the day before, if needed. is there a magic command to get me out of this? Thanks Jim
I just upgraded to 7.4.6 and have the same error message. ---------- Original Message ----------- From: "Jim Buttafuoco" <jim@contactbda.com> To: "pgsql-hackers" <pgsql-hackers@postgresql.org> Sent: Sat, 22 Jan 2005 09:35:02 -0500 Subject: [HACKERS] pg_clog problem (PG version 7.4.5) > hackers, > > I am having a problem with table (identified by pg_dump). I get the follow error when I try to COPY the table > to stdout (or /dev/null). > > DB=# copy rnk to '/dev/null'; > ERROR: could not access status of transaction 1076101119 > DETAIL: could not open file "/usr/local/pgsql/data/pg_clog/0402": No such file or directory > > Pg version is > select version(); > version > ------------------------------------------------------------------------------------------------- > PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) > > I do have a good backup from the day before, if needed. is there a magic command to get me out of this? > > Thanks > Jim > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend ------- End of Original Message -------
Jim Buttafuoco wrote: >hackers, > >I am having a problem with table (identified by pg_dump). I get the follow error when I try to COPY the table to >stdout (or /dev/null). > >DB=# copy rnk to '/dev/null'; >ERROR: could not access status of transaction 1076101119 >DETAIL: could not open file "/usr/local/pgsql/data/pg_clog/0402": No such file or directory > >Pg version is >select version(); > version >------------------------------------------------------------------------------------------------- > PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) > >I do have a good backup from the day before, if needed. is there a magic command to get me out of this? > > You could try creating that file by writing a bunch of zeros to it, for example: Shutdown PostgreSQL Then run the following command in $PGDATA/pg_clog dd bs=8K count=1 if=/dev/zero of=<filename> Where the filename is the name of the file missing. No promises though. Sincerely, Joshua D. Drake >Thanks >Jim > > > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Вложения
didn't work. ERROR: could not access status of transaction 1076101119 DETAIL: could not read from file "/usr/local/pgsql/data/pg_clog/0402" at offset 57344: Success any more ideas? ---------- Original Message ----------- From: "Joshua D. Drake" <jd@commandprompt.com> To: jim@contactbda.com Cc: pgsql-hackers <pgsql-hackers@postgresql.org> Sent: Sat, 22 Jan 2005 08:00:25 -0800 Subject: Re: [HACKERS] pg_clog problem (PG version 7.4.5) > Jim Buttafuoco wrote: > > >hackers, > > > >I am having a problem with table (identified by pg_dump). I get the follow error when I try to COPY the table to > >stdout (or /dev/null). > > > >DB=# copy rnk to '/dev/null'; > >ERROR: could not access status of transaction 1076101119 > >DETAIL: could not open file "/usr/local/pgsql/data/pg_clog/0402": No such file or directory > > > >Pg version is > >select version(); > > version > >------------------------------------------------------------------------------------------------- > > PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian 1:3.3.4-6sarge1) > > > >I do have a good backup from the day before, if needed. is there a magic command to get me out of this? > > > > > You could try creating that file by writing a bunch of zeros to it, for > example: > > Shutdown PostgreSQL > Then run the following command in $PGDATA/pg_clog > dd bs=8K count=1 if=/dev/zero of=<filename> > > Where the filename is the name of the file missing. > > No promises though. > > Sincerely, > > Joshua D. Drake > > >Thanks > >Jim > > > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 8: explain analyze is your friend > > > > > > -- > Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC > Postgresql support, programming shared hosting and dedicated hosting. > +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com > PostgreSQL Replicator -- production quality replication for PostgreSQL ------- End of Original Message -------
On Sat, Jan 22, 2005 at 12:06:46PM -0500, Jim Buttafuoco wrote: > didn't work. > > > ERROR: could not access status of transaction 1076101119 > DETAIL: could not read from file "/usr/local/pgsql/data/pg_clog/0402" at offset 57344: Success > > any more ideas? You need to extend the file further than byte 57344. So repeat the dd command, with count=8. Anyway, this situation is suspect anyway. There were bugs related to pg_clog files not being there, but AFAIR they were triggered on segment edges, not in the middle like this one. What other files there are in the pg_clog directory? If they are nowhere near the 0402 vicinity, you may have a memory or disk corruption problem. FWIW, 1076101119 is 1000000001000111111111111111111 in binary. Taking the highest 1 yeilds Xid 2359295. So, what files do you have? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)
Alvaro, Thanks for the reply. here is an "ls" of my pg_clog directory. The 0402 file, I created as per Joshua's directions. I might have created one too small. If so, what size do you think I should use. Jim bda1:/usr/local/pgsql/data# ls -l pg_clog total 992 -rw------- 1 postgres dba 262144 Sep 7 10:12 0000 -rw------- 1 postgres dba 262144 Nov 12 09:57 0001 -rw------- 1 postgres dba 262144 Dec 7 17:31 0002 -rw------- 1 postgres dba 204800 Jan 22 13:11 0003 -rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402 ---------- Original Message ----------- From: Alvaro Herrera <alvherre@dcc.uchile.cl> To: Jim Buttafuoco <jim@contactbda.com> Cc: "Joshua D. Drake" <jd@commandprompt.com>, pgsql-hackers <pgsql-hackers@postgresql.org> Sent: Sat, 22 Jan 2005 15:07:35 -0300 Subject: Re: [HACKERS] pg_clog problem (PG version 7.4.5) > On Sat, Jan 22, 2005 at 12:06:46PM -0500, Jim Buttafuoco wrote: > > didn't work. > > > > > > ERROR: could not access status of transaction 1076101119 > > DETAIL: could not read from file "/usr/local/pgsql/data/pg_clog/0402" at offset 57344: Success > > > > any more ideas? > > You need to extend the file further than byte 57344. So repeat the dd > command, with count=8. > > Anyway, this situation is suspect anyway. There were bugs related to > pg_clog files not being there, but AFAIR they were triggered on segment > edges, not in the middle like this one. > > What other files there are in the pg_clog directory? If they are > nowhere near the 0402 vicinity, you may have a memory or disk > corruption problem. > > FWIW, 1076101119 is 1000000001000111111111111111111 in binary. Taking > the highest 1 yeilds Xid 2359295. So, what files do you have? > > -- > Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) > "La primera ley de las demostraciones en vivo es: no trate de usar el sistema. > Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen) > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend ------- End of Original Message -------
"Jim Buttafuoco" <jim@contactbda.com> writes:
> Thanks for the reply. here is an "ls" of my pg_clog directory. The 0402 file, I created as per Joshua's directions.
> I might have created one too small. If so, what size do you think I should use.
> bda1:/usr/local/pgsql/data# ls -l pg_clog
> total 992
> -rw------- 1 postgres dba 262144 Sep 7 10:12 0000
> -rw------- 1 postgres dba 262144 Nov 12 09:57 0001
> -rw------- 1 postgres dba 262144 Dec 7 17:31 0002
> -rw------- 1 postgres dba 204800 Jan 22 13:11 0003
> -rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402
Given that set of pre-existing files, there is no possible way that you
really had a transaction in the range of IDs that 0402 would cover.
I agree with Alvaro's theory of a corrupted tuple. In fact it seems
plausible that the error is a single high-order 1 bit and the ID that
appears to be in the range of 0402 really belonged to file 0002.
A single dropped bit sounds more like RAM flakiness than disk problems
to me, so I'd get out the memory tester programs and start looking.
As far as recovering the data goes, you can use the usual techniques for
homing in on the location of the bad tuple and getting rid of it (or try
manually patching the XID field with a hex editor...)
regards, tom lane
I was able to copy the table over to a temp table and truncate it with only a little loss. I will be able to recover the lost data from backup so no big deal. I will have to schedule downtime to do the memory test with the "big" snow storm it will not be until monday night. thanks for the help Jim ---------- Original Message ----------- From: Tom Lane <tgl@sss.pgh.pa.us> To: jim@contactbda.com Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>, "pgsql-hackers" <pgsql-hackers@postgresql.org> Sent: Sat, 22 Jan 2005 13:41:04 -0500 Subject: Re: [HACKERS] pg_clog problem (PG version 7.4.5) > "Jim Buttafuoco" <jim@contactbda.com> writes: > > Thanks for the reply. here is an "ls" of my pg_clog directory. The 0402 file, I created as per Joshua's directions. > > I might have created one too small. If so, what size do you think I should use. > > > bda1:/usr/local/pgsql/data# ls -l pg_clog > > total 992 > > -rw------- 1 postgres dba 262144 Sep 7 10:12 0000 > > -rw------- 1 postgres dba 262144 Nov 12 09:57 0001 > > -rw------- 1 postgres dba 262144 Dec 7 17:31 0002 > > -rw------- 1 postgres dba 204800 Jan 22 13:11 0003 > > -rw-r--r-- 1 postgres dba 8192 Jan 22 12:05 0402 > > Given that set of pre-existing files, there is no possible way that you > really had a transaction in the range of IDs that 0402 would cover. > I agree with Alvaro's theory of a corrupted tuple. In fact it seems > plausible that the error is a single high-order 1 bit and the ID that > appears to be in the range of 0402 really belonged to file 0002. > > A single dropped bit sounds more like RAM flakiness than disk problems > to me, so I'd get out the memory tester programs and start looking. > > As far as recovering the data goes, you can use the usual techniques for > homing in on the location of the bad tuple and getting rid of it (or try > manually patching the XID field with a hex editor...) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ------- End of Original Message -------