Hello, Thanks for the reply. On Fri, Jul 31, 2009 at 5:24 PM, Greg Stark <gsst...@mit.edu> wrote:
> > I tried this, and might have exposed another issue. > > Now I am getting: > > ERROR: could not access status of transaction 3839923882 > > DETAIL: could not open file "pg_clog/0E4E": No such file or directory. > > I checked the pg_clog folder, and there is only a 0000 file. > > How long has this database been in use? That's a very high transaction > number that would only be reached on a heavily used database after a > reasonably long period of use. If that's not likely than a likely > possibility is that your table has been overwritten with garbage. > Possibly by a filesystem bug or hardware failure. > Not really that long. The database was created in early June, and if that is supposed to be a transaction count (3839923882), then yeah that seems way out there. > > You can work around this problem for that row by creating a file named > 0E4E in the clog directory. I think you can fill it with zeros but if > you search the mailing list you'll find instructions for doing this > that are might have better suggestions. > When you say fill it with zeros, is there a certain file size I need to create? (I will do some more googling to see if I can find more examples of people recovering from this problem) > > You'll still be best off recovering rows one by one. If you have > trouble using the index you might find it more reliable (but more > tedious) to do it using: > > select * from tab where ctid = '(0,0)' > select * from tab where ctid = '(0,1)' > select * from tab where ctid = '(0,2)' > ... > > The first digit is the page number and the second is the index on the > page which can go as high as about 250. > I am sorry, that is a little over my head with my knowledge gained with PG thus far. Is there some background information I could read that would explain this to me? I tried that select a couple of times, and eventually got the "invalid page header in the block 984 of relation "pg_toast_17431_index" error message again. ctid = '(0,6)' gave it to me. (Although, I don't know what I am doing here! :) Thanks again for the reply, your help is appreciated! Michael.
