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(dot)faux(at)med(dot)monash(dot)edu(dot)au
web site: http;//vbc.med.monash.edu.au/~fauxn/