Re: Best Replication Tool

On Sun, Feb 7, 2010 at 8:22 PM, Kiswono Prayogo <kiswono(at)gmail(dot)com> wrote:
> Hi, i'm really new to postgresql replication, was there any
> replication tool for postgresql that can do real-time replication from
> 1 database to ~3-10 database (on the other machines) with little
> overhead for load balancing purpose?

If, by "real time", you mean that a tuple created or changed on the
master is visible to queries on an arbitrary slave within a few
seconds, then Bucardo and Slony are both good candidates. I've used
Bucardo in just such an environment, along with its master-master
capability. Of the two, I definitely prefer the former, but this
isn't an advocacy list, and they both work.

The amount of overhead you'll experience is a function of how "chatty"
your data is -- the higher the volume of changes it needs to
replicate, the more work it will take to keep those changes flowing
out to the slave nodes. If you update the same tuple three times in a
five second period, that's three tuples you'll be replicating out to
each slave.

Another important consideration is the size of your replication sets
(in Slony terms; "herds" in Bucardo). The bigger they are -- the more
tables they include -- the more work it will take to replicate any
changes in any of their member objects. IME, you'll typically do well
to keep your sets down to the smallest meaningful groups you can.
Particularly, you'll want to group together tables that are
interrelated by foreign keys (this is especially important if you're
using Slony, and have any plans to support switching masters), or that
you expect to be modified together -- for example, all the tables that
might be modified in the course of a new order being entered into your
OLTP application.

rls

--
:wq