Re: transaction and triggers - Mailing list pgsql-sql

D'Arcy J.M. Cain wrote:

>On Fri, 18 Jan 2008 12:16:04 -0300
>Gerardo Herzig <gherzig@fmed.uba.ar> wrote:
>  
>
>>Right.  But  today, that trigger do some other work, wich includes 
>>writing some files to disk, so there is my problem. Crap, i guess i will 
>>have to review the main logic.
>>    
>>
>
>I built a replication system that syncs up dozens of systems in a
>multi-master environment spanning multiple continents in almost
>real-time and it works flawlessly so don't give up hope.
>
And im trying with 3 virtual machines...this is embarrasing :)

>  It is
>doable.  I can't give you the code because it was written under
>contract and it was based heavily on our specific business requirements
>but I can give you a few pointers.
>
>You have discovered the basic problem of trying to replicate in full
>real time.  You'll probably have to give up on that.  Instead, focus on
>making updates to the local database.  Create a replication table or
>tables that you update with triggers.  Basically this needs to be a log
>of every change to the database in a structured way.
>  
>
Crap. That was my first approach! I later chose the inmediate file 
writing, trying to minimize the changes that would be lost in case of 
primary system crash. I guess i will come with it again.

>Once you have the replication table(s) you can create external programs
>that connect to the master and update the slave.  In the slave you can
>track the last ID that completed.  Do the insert/update/delete in a
>transaction so that you have a guarantee that your database is up to
>date to a very specific point.  Note that you can have multiple slaves
>in this scenario and, in fact, the slaves can have slaves using the
>exact same scheme giving you a hierarchy.
>
>If you need multi-master you just need to have another process to feed
>your local changes up to the master.  This is not just a matter of
>making the master a slave though.  If you do that you get into a
>feedback loop.
>
>Also, if you need multi-master, you have to think about your
>sequencing.  If you need unique IDs on some tables you will have to
>think about setting up ranges of sequences based on server or have a
>central sequence server.  We used a combination of both as well as
>specifying that certain tables could only be inserted to on one
>system.  Of course, this system doesn't need to be the same as the top
>of the hierarchy and, in fact, different tables can have different
>generator systems.
>
>  
>
What i want to do is something like:If the master fails, it will be a peace of soft that would change the 
conf files (which indicate who's the master, slaves, and so on), so one 
of the slaves take the master's place. Since those are a common pc, when 
the real master come back to life, it has to be re-sync, and take his 
place as the master again. Im thinking in something as simple as posible 
(since im not a senior programmer), something like a ip address change 
could do the trick

>Hope this gets you started.  There's still lots of gotchas on the way.
>  
>
Oh yes, im specting so much fun!!!!
Thanks for sharing your knowledge with us!!

Mamooth replicator, Slone-I, feel the fear! :)

Thanks again, D'arcy!

Gerardo