Re: problem with maintenance script and missing pg_clog files with pg 7.2.1

Oliver Elphick <olly@lfix.co.uk> writes:
> POSTGRES_HOME is in fact only used by the install scripts and as the
> directory to cd to before running pg_ctl to start the postmaster.  So
> unless you have something in ~postgres/ that is giving PGDATA a
> different value, I can't see any problem here.

Right, there really isn't any (necessary) connection between postgres'
home directory and $PGDATA.  The junk in the old directory seems odd,
but I doubt it's related to your troubles (especially since it
apparently is one level up from your old data directory).

Given this:

> > FATAL 2:  open of /2/var/lib/postgres/data/pg_clog/0002 failed: No such file
> > or directory

ns1:/2/var/lib/postgres# ls -l /2/var/lib/postgres/data/pg_clog
total 588
-rw-------    1 postgres postgres   262144 Sep 22 01:05 0004
-rw-------    1 postgres postgres   262144 Sep 25 01:05 0005
-rw-------    1 postgres postgres    65536 Sep 25 04:40 0006

I now have to revise my opinion somewhat.  The reports we'd been seeing
that seemed to be data corruption mostly showed attempted accesses to
clog segments with numbers far beyond the active segment(s).  Here
you've got a reference to an old segment that's already been removed.
This looks like it could be a software bug --- ie, failure to be sure
we'd nailed down the commit status of all old tuples before removing a
past clog segment.  We found one or two bugs before 7.2 release that
could lead to this symptom, but I'd hoped we'd caught them all.  Maybe
not.

I would be interested to look closely at the data and see if anything
can be learned.  Would you be willing to consider letting me have access
to your machine & database to study the problem?

            regards, tom lane