Re: [Gnumed-devel] Corrupt database

[Karsten Hilbert]

On Fri, May 31, 2013 at 07:37:00AM +0000, Jim Busser wrote:

> >> Can it be assumed that if the schema can be successfully queried, and that 
> >> it matches the hash of the reference fingerprint, that the file is not 
> >> corrupted?
> > No, and in fact, there are many files making up one database.
> 
> In this case, exists there any postgres maintenance
> function or utility that can test and report the integrity
> or non-integrity of the database?

No. AFAICT 9.3 will contain page checksums which helps with
some of that. There are many levels of "integrity", some of
them ephemeral, and one would need to say which of them we
are talking about.

Our schema fingerprinting adds one bit (namely database
layout, which is something layers below GNUmed cannot know
by themselves) to the existing safeguards.

> >> Would it make sense to automate this check of fingerprints as part of the 
> >> backup procedure?
> > 
> > Refuse to back up and/or restore if there is a database mismatch ?
> 
> The above could keep a record of when something about the
> database had been changed. This could help someone to target
> a date or range of dates when they might have been in
> communication with someone about a problem that they were
> having with their database, and may point them to the change
> that they had made (such as altering a constraint) which
> they can no longer recall what they did.

As soon as they alter something relevant (now including
foreign keys, and for good measure I made it eventually
include ALL constraints, now, too) the client will refuse to
connect. If they choose to  explicitely manually override
that refusal (and click away the still-shown warning) they
are on their own.

> >> This might involve to keep, in the backup directory, a copy of whatever is 
> >> the desired reference fingerprint.
> > 
> > What use do you have in mind ?  One thing I can imagine is including
> > "corrupted_schema" in the backup filename if so.
> 
> see my question below
> 
> >> The result of the script could append a line onto the end of a backup file 
> >> say
> >> 
> >>    fingerprint.check
> >> 
> >> with
> >> 
> >>    datetime version_current hashvalue_current version_reference 
> >> hashvalue_reference
> >>    Tue 28 May 2013 19:01:02 PDT gnumed_v18 
> >> a0f9efcabdecfb4ddb6d8c0b69c02092 gnumed_v18 
> >> a0f9efcabdecfb4ddb6d8c0b69c02092
> >>    Wed 29 May 2013 21:01:02 PDT gnumed_v18 
> >> a0f9efcabdecfb4ddb6d8c0b69c02092 gnumed_v18 
> >> a0f9efcabdecfb4ddb6d8c0b69c02092
> >>    Thu 30 May 2013 20:04:09 PDT gnumed_v18 
> >> z6f7a1428e7c83f4bf638c3a1146e93s gnumed_v18 
> >> a0f9efcabdecfb4ddb6d8c0b69c02092
> > 
> > What would be the benefit ?  Given that the client verifies the hash every 
> > single time
> > it is started I dare say the user would learn of mismatches very quickly 
> > w/o keeping
> > a log of hashes.
> 
> When the hash mismatches, will the client refuse to run? Or will it simply 
> warn?

It will refuse to run unless you explicitely tell it to not
refuse to run after which it will STILL warn.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346