Re: [Gnumed-devel] database replication with bucardo

[Karsten Hilbert]

On Wed, Jun 27, 2012 at 01:51:11PM +0200, Slappinjohn wrote:

> I've two Gnumed-Server-instances, one in practice, one at home for
> writing referral letters and do all the invoicing stuff. I tried it with
> VPN/WAN-access before (30sec for a patient to show up) and NX (printing
> didn't work properly),

I see.

> so the last working solution was to take a backup
> on USB-Stick with me

That means you are saying "I will never need to access both
work and home at the same time". Good to know because it'll
ease up on the requirements.

So, one solution might be (in order to eliminate the USB
stick): create a differential backup and upload that. It is
likely unpractical to simply upload the USB stick, right ?

Let's see (pg_dump --data-only --inserts):

day 1 @ work
        pg_dump > work1
        upload

day 1 @ home
        download
        restore < work1
        ...
        pg_dump > home1
        upload

day 2 @ work
        download
        restore < home1
        ...
        pg_dump > work2
        diff home1 work2 > work2-changes
        upload work2-changes

day 2 @ home
        restore work2-changes

etc.

Or use rsync:

        pg_dump > work1

        rsync from home (syncs only changes)

        restore at home

        pg_dump > home1

        rsync from work

        ...


Since the pg_dump/upload/download/rsync can be scripted (and
tied to boot/shutdown) you can a) eliminate forgetting the
USB stick, b) still only transfer changes (bandwith), c)
work at home and at work.

> So a replication is needed to do the encounter-stuff at work and the
> document stuff at home
> 
> > Also, how does Bucardo insert data into target tables ?  By
> > running SQL against them or by some lower level method ?
> 
> SQL - so it's said in the docs, haven't looked in the sources yet
> (written in Perl)

OK, good. That means you don't need to replicate the audit
table data because when Bucardo replays the SQL on the
"other" database it will invoke the audit triggers anyways.

The only question now is whether Bucardo will replay every
single change or whether it will replay the "latest version"
only in case there would have been several generations of a
row.

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