Re: [Gnumed-devel] Approaches to maintain clinical data uptime

[Karsten Hilbert]

On Wed, May 03, 2006 at 08:26:29AM -0700, Jim Busser wrote:

> I had thought (maybe incorrectly) that there can exist a "lag"  
> between information that can be written on the primary (master)  
> server, and information that has been "conveyed/written/replicated"  
> to the slave.
There can, yes.

> So if a master table should get corrupted the WAL  
> could serve as a source of information added since the last dump.
Yes, for this you need to use PITR which includes information from the WAL.

> Do Postgres tables never get corrupted?
They do. Bugs in PG, defective discs.

> >>- is it recommended or required that the database to be fully  
> >>logged off, so that it is in an unambiguous or "clean" state (no  
> >>unprocessed transactions) at the time of the dump?
> Is Postgres different from (say) MySQL in this regard?
Why, of course. That's why we chose one over the other.

> In reference to backing up, articles say
> 
> >>>One of the difficulties with a large and active MySQL database is  
> >>>making clean backups without having to bring the server down.
Such things don't apply to PostgreSQL.

> >>>Otherwise, a backup may slow down the system and there may be  
> >>>inconsistency with data, since related tables may be changed  
> >>>while another is being backed up.
No such crap is heard of in the vicinity of PostgreSQL.
Would you even consider accepting any such thing to drive
your clinical database ?

> I didn't mean "clean" in terms of data quality ("non-crap"), I just  
> meant complete and unambiguous, i.e. no tables being modified at the  
> time of the backup, so that the "state of completeness" of the dump  
> could be better known.
A dump is run inside a transaction as well so the snapshot
the dump process sees does not change in any way until the
dump is completed.

> How would the dump process choose where to  
> begin and end if there is concurrent activity?
It does not care about concurrent activity. It takes a
*snapshot* of the database when it starts. Whatever happens
past that it does not even see let alone be able to care
about. Snapshots are guaranteed to happen at transaction
boundaries.

> May it not be helpful,  
> to be able to know that a brief work stoppage associated with a  
> certain date/time assures that all records to that point are "intact"  
> in the backup?
Using PostgreSQL it is simply not necessary.

As for *internal* *business-level* consistency this all
depends on the application committing non-crap transactions.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346