Re: PostgreSQL Data Loss

On Jan 26, 2007, at 2:22 AM, BluDes wrote:
>  I have a problem with one of my costomers.
> I made a program that uses a PostgreSQL (win32) database to save  
> its data.
> My customer claims that he lost lots of data reguarding his own  
> clients and that those data had surely been saved on the database.
> My first guess is that he is the one who deleted the data but wants  
> to blame someone else, obviously I can't prove it.
>
> Could it be possible for PostgreSQL to lose its data? Maybe with a  
> file corruption? Could it be possible to restore these data?
>
> My program does not modify or delete data since its more like a log  
> that only adds information. It is obviously possible to delete  
> these logs but it requires to answer "yes" to 2 different warnings,  
> so the data can't be deleted accidentally.
>
> I have other customers with even 10 times the amount of data of the  
> one who claimed the loss but no problems with them.
> He obviously made no backups (and claims whe never told him to do  
> them so we are responsible even for this) though the program has a  
> dedicated Backup-section.


I have seen this data loss pattern many, many times, and on Oracle  
too.  The most frequent culprits in my experience:

1.)  The customer screwed up big time and does not want to admit that  
they made a mistake, hoping you can somehow pull their butt out of  
the fire for free.

2.)  Someone else sabotaged or messed up the customers database, and  
the customer is not aware of it.

3.)  The customer deleted their own data and is oblivious to the fact  
that they are responsible.

4.)  There is some rare edge case in your application that generates  
SQL that deletes all the data.


There is always the possibility that there is in fact some data loss  
due to a failure of the database, but it is a rare kind of corruption  
that deletes a person's data but leaves everything else intact with  
no error messages, warnings, or other indications that something is  
not right.  Given the description of the problem, I find an internal  
failure of the database to be a low probability reason for the data  
loss.


Having run many database systems that had various levels of pervasive  
internal change auditing/versioning, often unbeknownst to the casual  
user, virtually all of the several "data loss" cases I've seen with a  
description like the above clearly fit in the cases #1-3 above when  
we went into the audit logs i.e. someone explicitly did the  
deleting.  I cannot tell you how many times people have tried to  
pretend that the database "lost" or "messed up" their data and then  
been embarrassed when they discover that I can step through every  
single action they took to destroy their own data.  I've never seen a  
single case like the one described above that was due to an internal  
database failure; when there is an internal database failure, it is  
usually ugly and obvious.

Cheers,

J. Andrew Rogers
jrogers@neopolitan.com