Thanks for the reply.
On Fri, Jul 31, 2009 at 5:24 PM, Greg Stark
<gsstark@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.
