Обсуждение: Lost tables in Postgres 7.1

OK, I know I should be running a later version, but we've been on this
since it came out and rebuilding the host is a big deal :( Last time we
did it was to move from 6.5 to 7.1 and we had to rebuild the whole
server (not the fault of PostgreSQL, just evolution).

Some of my tables have spontaneously disappeared. They are not visible
in pg_class. vacuuming pg_class (as suggested by Tom Lane for someone
with a similar problem) did not cause them to reappear. Doing a select
on the invisible tables returns a structure (column headings) but no
data. The log returned the following for the vacuum of pg_class,
suggesting that 44 tuples got shot.

DEBUG:  --Relation pg_class--
DEBUG:  Pages 31: Changed 0, reaped 17, Empty 0, New 0; Tup 1922: Vac
44, Keep/VTL 848/0, Crash 0, UnUsed 42, MinLen 115, MaxLen 160;
Re-using: Free/Avail. Space 11716/0; EndEmpty/Avail. Pages 1/0. CPU
0.00s/0.00u sec.
DEBUG:  Index pg_class_oid_index: Pages 61965; Tuples 1922: Deleted 44.
CPU 4.71s/0.34u sec.
DEBUG:  Index pg_class_relname_index: Pages 82270; Tuples 1922: Deleted
44. CPU 6.41s/0.39u sec.
DEBUG:  Rel pg_class: Pages: 31 --> 30.

I have a copy of the pg_class table file from before it was vacuumed, so
in theory I still have the 'deleted' tuples. Is there any way of
recovering them? Any suggestions? We have backups and are rolling the
tape, but the reality is that we will of course lose the 2 days of data
entered since the last good backup which would be painful to reproduce.
Getting a bit desperate here, 7 years on PostgreSQL and never had a
problem like this before!

Thanks,

Steve

Steve Burrows <steve@jla.com> writes:
> Some of my tables have spontaneously disappeared. They are not visible
> in pg_class. vacuuming pg_class (as suggested by Tom Lane for someone
> with a similar problem) did not cause them to reappear.

Not on 7.1 it wouldn't :-(.  That hack is dependent on the behavior of
the transaction ID wraparound code that we introduced in 7.2.

First check if this actually is a wrap issue: create a new row and see
if its xmin is small or large.  If small, I think the only recourse in
7.1 is to set the XID counter to something just a bit less than 4G,
pg_dump, initdb and reload.  If you dig around in the archives from
a few years back you should find discussion of exactly how to do that,
but I've surely forgotten the details.

            regards, tom lane