Skip site navigation (1) Skip section navigation (2)

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

From: Noel Faux <noel(dot)faux(at)med(dot)monash(dot)edu(dot)au>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: Postgres <pgsql-novice(at)postgresql(dot)org>, hu ping <hping(at)vpac(dot)org>
Subject: Re: Loss of data and info from system tables!!
Date: 2005-03-31 04:41:04
Message-ID: 424B7F60.50004@med.monash.edu.au (view raw or flat)
Thread:
Lists: pgsql-novice
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/


Attachment: noel.faux.vcf
Description: text/x-vcard (287 bytes)

In response to

Responses

pgsql-novice by date

Next:From: Michael FuhrDate: 2005-03-31 05:07:49
Subject: Re: Loss of data and info from system tables!!
Previous:From: Michael FuhrDate: 2005-03-31 03:49:11
Subject: Re: Loss of data and info from system tables!!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group