Re: Postgresql replication - Mailing list pgsql-general

Or, for something far easier, try
http://pgfoundry.org/projects/pgcluster/ which provides syncronous
multi-master clustering.

On Wed, Aug 24, 2005 at 12:53:34PM -0700, Jeff Davis wrote:
> Chris Browne wrote:
> >
> > Slony-I is a master/slave asynchronous replication system; if you
> > already considered it unsuitable, then I see little likelihood of
> > other systems with the same sorts of properties being suitable.
> >
> > What could conceivably be of use to you would be a *multimaster*
> > asynchronous replication system.  Unfortunately, multimaster
> > *anything* is a really tough nut to crack.
> >
>
> In general that's a difficult problem, but in practice there may be a
> solution.
>
> For instance, perhaps the following configuration would be helpful:
>
> Make a database for each physical server, called db1 ... dbN. Let your
> logical tables in each database be table1 ... tableM. Now, for each
> logical tableX (where 1 <= X <= M), make N physical tables, tableX_1 ...
> tableX_N. Now, make a view called tableX that is the UNION of tableX_1
> ... tableX_N (tableX is not a real table, it's just a logical table).
>
> Now, use Slony-I. For each dbY (where 1 <= Y <= N), make dbY a master
> for tableX_Y (for all X where 1 <= X <= M) and a slave for tableX_Z (for
> all X,Z where 1 <= X <= M, Z != Y).
>
> Now, use a rule that replaces all INSERTs to tableX (where 1 <= X <= M)
> on dbY (where 1 <= Y <= N) with INSERTs to tableX_Y.
>
> That was my attempt at being unambiguous. In general what I mean is that
> each database is master of one piece of a table, and slave to all the
> other pieces of that table, and then you have a view which is the union
> of those pieces. That view is the logical table. Then have a RULE which
> makes INSERTs go to the physical table for which that database is master.
>
> The advantages: if one machine goes down, the rest keep going, and
> merely miss the updates from that one site to that table. If one machine
> makes an insert to the table, it quickly propogates to the other
> machines and transparently becomes a part of the logical tables on those
> machines.
>
> The disadvantages: UPDATEs are difficult, and might end up with a
> complicated set of rules/procedures/triggers. You may have to program
> the application defensively in case the database is unable to update a
> remote database for various reasons (if the record to be updated is a
> part of a table for which another database is master). Also, since the
> solution is asynchronous, the databases may provide different results to
> the same query.
>
> In general, this solution does not account for all kinds of data
> constraints. The conflict resolution is very simplified because it's
> basically just the union of data. If that union could cause a constraint
> violation itself, this solution might not be right for you. For
> instance, let's say you're tracking video rentals, and store policy says
> that you only rent one video per person. However, maybe they go to store
> 1 and rent a video, and run to store 2 and rent a video before store 1
> sends the INSERT record over to store 2. Now, when they finally do
> attempt to UNION the data for the view, you have an inconsistant state.
>
> Many applications can get by just fine by UNIONing the data like that,
> and if not, perhaps work around it.
>
> I hope this is helpful. Let me know if there's some reason my plan won't
> work.
>
> Regards,
>     Jeff Davis
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461