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(at)neopolitan(dot)com