[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
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
I just upgraded to 7.4.6 and have the same error message. -- Original Message --- From: Jim Buttafuoco [EMAIL PROTECTED] 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 --- ---(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
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 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
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 [EMAIL PROTECTED] To: [EMAIL PROTECTED] 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 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL --- End of Original Message --- ---(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
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 1100011 in binary. Taking the highest 1 yeilds Xid 2359295. So, what files do you have? -- Alvaro Herrera ([EMAIL PROTECTED]) 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
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
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 -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 [EMAIL PROTECTED] To: Jim Buttafuoco [EMAIL PROTECTED] Cc: Joshua D. Drake [EMAIL PROTECTED], 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 1100011 in binary. Taking the highest 1 yeilds Xid 2359295. So, what files do you have? -- Alvaro Herrera ([EMAIL PROTECTED]) 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 --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
Jim Buttafuoco [EMAIL PROTECTED] 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 -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
Re: [HACKERS] pg_clog problem (PG version 7.4.5)
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 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Alvaro Herrera [EMAIL PROTECTED], 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 [EMAIL PROTECTED] 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 -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 --- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
