Re: Big 7.4 items

Jan Wieck wrote:
> Bruce Momjian wrote:
> 
> > OK, the first thing is that there isn't any one replication solution
> > that will behave optimally in all situations.
> 
> Right
> 
> > 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.
> 
> Wrong
> 
> It is asynchronous without the need of 2 phase commit. It is group

Well, Darren's PDF at:
ftp://gborg.postgresql.org/pub/pgreplication/stable/PostgreSQLReplication.pdf.gz

calls Postgres-R "Type: Embedded, Peer-to-Peer, Sync".  I don't know
enough about replication so I will let you fight it out with him.  ;-)

> communication based and requires the group communication system to
> guarantee total order. The tricky part is, that the local transaction
> must be on hold until the own commit message comes back without a prior
> lock conflict by a replication transaction. If such a lock confict
> occurs, the replication transaction wins and the local transaction rolls
> back.

Yep, that's the tricky part.

> > 
> > 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.
> 
> But you cannot use the result of such a SELECT to update anything. So
> you can only phase out complete read only transaction to the slaves.
> Requires support from the application since the load balancing system
> cannot know automatically what will be a read only transaction and what
> not.

Good point.  It has to be a read-only session because you can't jump
nodes during a session.  That definately limits its usefulness.

> > 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.
> 
> PITR doesn't help here at all, since PeerDirect's replication is trigger
> and control table based. What makes our replication system unique is
> that it works bidirectional in a heterogenious world.

I was only suggesting that PITR _may_ help as an archive method for the
changes.  PeerDirect stores those changes in a table?

> > I will describe the use of 'spread' and the Postgres-R internal issues
> > in my next email.
> 
> The last time i was playing with spread (that was at Great Bridge in
> Norfolk), it was IMHO useless (for Postgres-R) because it sometimes
> dropped messages when the network load got too high. This occured
> without any indication, no error, nothing. This is not exactly what I
> understand as total order. I hope they have made some substantial
> progress on that.

That's a serious problem, clearly.  Hopefully it is either fixed or it
will get fixed.  We can't use it without reliability.

--  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