Thread: error opening pg_clog file

   On Saturday, our database machine locked up hard on us due to some
faulty hardware.  Since then, we have been getting messages like this:

ERROR:  could not access status of transaction 143934068
DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0089": No
such file or directory

    Looking in /usr/local/pgsql/data/pg_clog, the "0089" file isn't
there.  Some investigation revealed these messages:

http://archives.postgresql.org/pgsql-hackers/2004-01/msg00534.php
http://www.mail-archive.com/pgsql-admin@postgresql.org/msg13874.html

   So, I did "dd if=/dev/zero of=/usr/local/pgsql/data/pg_clog/0089
bs=8k count=1".  I did an ls to verify that the file existed.  I started
the postmaster back up, tried a VACUUM, and got:

vacuumdb: vacuuming of database "hyperseek" failed: ERROR:  could not
access status of transaction 144565028
DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0089": No
such file or directory

   I looked, and the "0089" file was gone again.  Is there anything I
can do to save the situation?  (The PG version is 7.4.2)

steve wolfe

Steve Wolfe <nw@codon.com> writes:
>    So, I did "dd if=/dev/zero of=/usr/local/pgsql/data/pg_clog/0089
> bs=8k count=1".  I did an ls to verify that the file existed.  I started
> the postmaster back up, tried a VACUUM, and got:

> vacuumdb: vacuuming of database "hyperseek" failed: ERROR:  could not
> access status of transaction 144565028
> DETAIL:  could not open file "/usr/local/pgsql/data/pg_clog/0089": No
> such file or directory

>    I looked, and the "0089" file was gone again.  Is there anything I
> can do to save the situation?  (The PG version is 7.4.2)

Try vacuuming the damaged database *first*.  vacuumdb is probably
starting off with something that hasn't got a problem.  CLOG will only
get truncated at the end of a successful database-wide vacuum ... but
in this case that's too soon.

            regards, tom lane