From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: D'Arcy J.M. Cain <darcy@druid.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: transaction and triggers
Date: Mon, 21 Jan 2008 09:12:56 -0300
Message-ID: <47948C48.6030002@fmed.uba.ar> (raw)
In-Reply-To: <20080118104802.181ca4a8.darcy@druid.net>
References: <4790B50A.5050101@fmed.uba.ar>
<92869e660801180642i1fdd417r3d3b9399c5e5ab98@mail.gmail.com>
<4790C2B4.1070103@fmed.uba.ar>
<20080118104802.181ca4a8.darcy@druid.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gherzig@fmed.uba.ar, darcy@druid.net
Subject: Re: transaction and triggers
In-Reply-To: <47948C48.6030002@fmed.uba.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox