Re: Loss of data and info from system tables!!

On Thu, Mar 31, 2005 at 04:41:04AM +0000, Noel Faux wrote:
> Michael Fuhr wrote:
> >
> >Are you doing regular VACUUMs of the database?
> >
> Only after the completion of large jobs (inserts) and when ever data is
> deleted and new tables and indexes are added to the schema. So the last
> one was some time at the start of the year. We generally only vacuum
> tables which are affected by deletes, inserts and updates. All vacuums
> performed as 'vacuum analyze full'.

Oh dear. You might wish to read the "Routine Database Maintenance
Tasks" chapter in the documentation, in particular the "Routine
Vacuuming" section, and *especially* "Preventing transaction ID
wraparound failures":

http://www.postgresql.org/docs/7.4/interactive/maintenance.html

> monashprotein=> SELECT oid, xmin, cmin, xmax, cmax, datname,
> age(datvacuumxid) AS vxid, age(datfrozenxid) AS fxid FROM pg_database;
> oid | xmin | cmin | xmax | cmax | datname | vxid | fxid
> ------------+----------+------+------+------+---------+------------+-------------
> 1021343158 | 63521101 | 0 | 0 | 0 | lcm | 1485252959 | -1736329086
> (1 row)
> my database is missing 'monashprotein' under datname.

I'd suggest searching the archives for messages that talk about
recovering from transaction ID wraparound (I think that's what's
happened -- somebody please correct me if I'm mistaken). I've seen
it talked about but haven't had to do it myself, so any advice I
could offer would just be repeating what others have suggested.
Maybe somebody will come along who's actually gone through it and
offer to help. Or, if you're lucky, somebody will correct my
diagnosis....

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/