Обсуждение: 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