pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Lost tables in Postgres 7.1
2+ messages / 2 participants
[nested] [flat]

* Lost tables in Postgres 7.1
@ 2006-02-13 15:59  Steve Burrows <steve@jla.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Steve Burrows @ 2006-02-13 15:59 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Lost tables in Postgres 7.1
@ 2006-02-13 17:25  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Steve Burrows <steve@jla.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2006-02-13 17:25 UTC (permalink / raw)
  To: Steve Burrows <steve@jla.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2006-02-13 17:25 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-13 15:59 Lost tables in Postgres 7.1 Steve Burrows <steve@jla.com>
2006-02-13 17:25 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox