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

Michael Fuhr wrote:

On Thu, Mar 31, 2005 at 03:12:53AM +0000, Noel Faux wrote: 

On the 27th March I notice that one of my web sites was giving me some 
weird errors.  When I looked at the SQL statements generating these 
error I found that there were no results from these queries.  When I 
selected on the table 'protein_database' I discovered that the database 
the web site refers to did not exist!! This was all fine 3 days prior! 
After further selects on more tables it became apparent there was data 
loss across a large number of tables. Now I feel extremely ill!  It 
seemed that data which was added to the database when it was initially 
created was lost.  As data which I have recently added was still there.     

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'.

 What's the result of
the following query?

SELECT oid, xmin, cmin, xmax, cmax, datname,      age(datvacuumxid) AS vxid,      age(datfrozenxid) AS fxid
FROM pg_database; 

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.

Hope this helps.

Cheers
Noel

-- 
PhD student
Department of Biochemistry and Molecular Biology
Monash University
Clayton 
Victoria 3800
Australia
Ph: +61 3 9905 1418
e-mail: noel.faux@med.monash.edu.au
web site: http;//vbc.med.monash.edu.au/~fauxn/