On Fri, 18 Jan 2008 12:16:04 -0300 Gerardo Herzig <[EMAIL PROTECTED]> 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. 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. 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. Hope this gets you started. There's still lots of gotchas on the way. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
