Re: Big 7.4 items

Shridhar Daithankar wrote:
> 1) What kind of replication are we looking at? log file
> replay/synchronous etc.  If it is real time, like usogres( I
> hope I am in line with things here), that would be real good.
> Choice is always good.

Good.  This is the discussion we need.  Let me quote the TODO list
replication section first:
   * Add replication of distributed databases [replication]       o automatic failover       o load balancing       o
master/slavereplication       o multi-master replication       o partition data across servers       o sample
implementationin contrib/rserv       o queries across databases or servers (two-phase commit)       o allow replication
overunreliable or non-persistent links       o http://gborg.postgresql.org/project/pgreplication/projdisplay.php
 

OK, the first thing is that there isn't any one replication solution
that will behave optimally in all situations.  

Now, let me describe Postgres-R and then the other replication
solutions.  Postgres-R is multi-master, meaning you can send SELECT and
UPDATE/DELETE queries to any of the servers in the cluster, and get the
same result.  It is also synchronous, meaning it doesn't update the
local copy until it is sure the other nodes agree to the change. It
allows failover, because if one node goes down, the others keep going.

Now, let me contrast:

rserv and dbmirror do master/slave.  There is no mechanism to allow you
to do updates on the slave, and have them propagate to the master.  You
can, however, send SELECT queries to the slave, and in fact that's how
usogres does load balancing.

Two-phase commit is probably the most popular commercial replication
solution.  While it works for multi-master, it suffers from poor
performance and doesn't handle cases where one node disappears very
well.

Another replication need is for asynchronous replication, most
traditionally for traveling salesmen who need to update their databases
periodically.  The only solution I know for that is PeerDirect's
PostgreSQL commercial offering at http://www.peerdirect.com.  It is
possible PITR may help with this, but we need to handle propagating
changes made by the salesmen back up into the server, and to do that, we
will need a mechanism to handle conflicts that occur when two people
update the same records.  This is always a problem for asynchronous
replication.

> 2 If we are going to have replication, can we have built in load
> balancing? Is it a good idea to have it in postgresql or a
> separate application would be way to go?

Well, because Postgres-R is multi-master, it has automatic load
balancing.  You can have your users point to whatever node you want. 
You can implement this "pointing" by using dns IP address cycling, or
have a router that auto-load balances, though you would need to keep a
db session on the same node, of course.

So, in summary, I think we will eventually have two directions for
replication.  One is Postgres-R for multi-master, synchronous
replication, and PITR, for asynchronous replication.  I don't think
there is any value to use PITR for synchronous replication because by
definition, you don't _store_ the changes for later use because it is
synchronous.  In synchronous, you communicate your changes to all the
nodes involved, then commit them.

I will describe the use of 'spread' and the Postgres-R internal issues
in my next email.

-- Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073