Thread: Transaction error
Hi, I recently shut down my machine (as far as I know, it went down cleanly). When I brought it back up, one of my databases was messed up. Here is the error I get when I try to select: ERROR: could not access status of transaction 4114057 DETAIL: could not open file "/var/lib/postgres/data/pg_clog/0003": No such file or directory I tried restoring a backup of the data directory that did contain the 0003 file, but that didn't work either. I don't have a copy of the dump before the crash--though I think that is what I'll backup next time. How can I get my data back? Debian GNU/Linux postgres 7.4.7 Casey
"Casey T. Deccio" <casey@deccio.net> writes: > I recently shut down my machine (as far as I know, it went down > cleanly). When I brought it back up, one of my databases was messed > up. Here is the error I get when I try to select: > ERROR: could not access status of transaction 4114057 > DETAIL: could not open file "/var/lib/postgres/data/pg_clog/0003": No > such file or directory > I tried restoring a backup of the data directory that did contain the > 0003 file, but that didn't work either. Define "didn't work" ... what happened, exactly, when you tried it? People have occasionally dealt with this sort of scenario by creating dummy clog segment files by hand, and I'd think that restoring the segment file from a backup would do as well or better. regards, tom lane
"Casey T. Deccio" <casey@deccio.net> writes: > On Thu, 2005-09-01 at 11:59 -0400, Tom Lane wrote: >> Define "didn't work" ... what happened, exactly, when you tried it? > Sorry, I was too tired last night to try it again and post the error. > Here it is: > ERROR: could not access status of transaction 4114057 > DETAIL: could not read from file "/var/lib/postgres/data/pg_clog/0003" > at offset 237568: Success Apparently your backup wasn't very current, and contains a version of that clog file that hasn't yet reached full size. Oh well, I was hoping you'd get valid data from the backup. > How would I create a dummy clog segment file, if needed? What you'll have to do is pad out the segment file to full size (256K) by appending zeroes. (dd from /dev/zero is useful for this.) Note that you will probably not get a completely consistent database out of this --- there are going to be some transactions that appear uncommitted or partially committed. I'd recommend a dump and reload to help you revalidate your data, plus you'll have to apply any manual consistency checks you can think of. regards, tom lane
On Thu, 2005-09-01 at 11:59 -0400, Tom Lane wrote: > "Casey T. Deccio" <casey@deccio.net> writes: > > I recently shut down my machine (as far as I know, it went down > > cleanly). When I brought it back up, one of my databases was messed > > up. Here is the error I get when I try to select: > > > ERROR: could not access status of transaction 4114057 > > DETAIL: could not open file "/var/lib/postgres/data/pg_clog/0003": No > > such file or directory > > > I tried restoring a backup of the data directory that did contain the > > 0003 file, but that didn't work either. > > Define "didn't work" ... what happened, exactly, when you tried it? > Sorry, I was too tired last night to try it again and post the error. Here it is: ERROR: could not access status of transaction 4114057 DETAIL: could not read from file "/var/lib/postgres/data/pg_clog/0003" at offset 237568: Success > People have occasionally dealt with this sort of scenario by creating > dummy clog segment files by hand, and I'd think that restoring the > segment file from a backup would do as well or better. > How would I create a dummy clog segment file, if needed? Regards, Casey
On Thu, 2005-09-01 at 13:11 -0400, Tom Lane wrote: > > How would I create a dummy clog segment file, if needed? > > What you'll have to do is pad out the segment file to full size (256K) > by appending zeroes. (dd from /dev/zero is useful for this.) > It worked! Thanks for your help! Casey