Big 7.4 items

On 13 Dec 2002 at 1:22, Bruce Momjian wrote:

Replication

I have talked to Darren Johnson and I believe 7.4 is the time to
merge the Postgres-R source tree into our main CVS. Most of the
replication code will be in its own directory, with only minor
changes to our existing tree. They have single-master
replication working now, so we may have that feature in some
capacity for 7.4. I know others are working on replication
solutions. This is probably the time to decide for certain if
this is the direction we want to go for replication. Most who
have have studied Postgres-R feel it is the most promising
multi-master replication solution for reliably networked hosts.

Comments?

Some.

1) What kind of replication are we looking at? log file replay/syncnronous 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..

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?

And where are nested transactions?

Bye
Shridhar

--
Booker's Law: An ounce of application is worth a ton of abstraction.

On Fri, 2002-12-13 at 06:22, Bruce Momjian wrote:

I wanted to outline some of the big items we are looking at for 7.4:
Point-In-Time Recovery (PITR)

J. R. Nield did a PITR patch late in 7.3 development, and Patrick
MacDonald from Red Hat is working on merging it into CVS and
adding any missing pieces. Patrick, do you have an ETA on that?

How hard would it be to extend PITR for master-slave (hot backup)
repliaction, which should then amount to continuously shipping logs to
slave and doing nonstop PITR there :)

It will never be usable for multi-master replication, but somehow it
feels that for master-slave replication simple log replay would be most
simple and robust solution.

--
Hannu Krosing <hannu@tm.ee>

Bruce Momjian wrote:

I wanted to outline some of the big items we are looking at for 7.4:

Win32 Port:

Katie Ward and Jan are working on contributing their Win32
port for 7.4. They plan to have a patch available by the end of
December.

Point-In-Time Recovery (PITR)

J. R. Nield did a PITR patch late in 7.3 development, and Patrick
MacDonald from Red Hat is working on merging it into CVS and
adding any missing pieces. Patrick, do you have an ETA on that?

Replication

I have talked to Darren Johnson and I believe 7.4 is the time to
merge the Postgres-R source tree into our main CVS. Most of the
replication code will be in its own directory, with only minor
changes to our existing tree. They have single-master
replication working now, so we may have that feature in some
capacity for 7.4. I know others are working on replication
solutions. This is probably the time to decide for certain if
this is the direction we want to go for replication. Most who
have have studied Postgres-R feel it is the most promising
multi-master replication solution for reliably networked hosts.

Comments?

What about distributed TX support:

http://groups.google.com/groups?hl=en&amp;lr=&amp;ie=UTF-8&amp;oe=UTF-8&amp;threadm=20021106111554.69ae1dcd.pgsql%40snaga.org&amp;rnum=2&amp;prev=/groups%3Fq%3DNAGAYASU%2BSatoshi%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den

Mike Mascari
mascarm@mascari.com

How hard would it be to extend PITR for master-slave (hot backup)
repliaction, which should then amount to continuously shipping logs to
slave and doing nonstop PITR there :)

I have not looked at the PITR patch yet, but it might be
possible to use the same PITR format to queue/log writesetswith postgres-r, so we can have multi-master replication
and PITR from the same mechanism.

Darren

Bruce Momjian wrote:

Win32 Port:

Katie Ward and Jan are working on contributing their Win32
port for 7.4. They plan to have a patch available by the end of
December.

I have .Net Studio available to me, so if you need help in merging or testing
or whatever, let me know.

Point-In-Time Recovery (PITR)

J. R. Nield did a PITR patch late in 7.3 development, and Patrick
MacDonald from Red Hat is working on merging it into CVS and
adding any missing pieces. Patrick, do you have an ETA on that?

As Hannu asked (and related to your question below), is there any thought of
extending this to allow simple log based replication? In many important
scenarios that would be more than adequate, and simpler to set up.

Replication

I have talked to Darren Johnson and I believe 7.4 is the time to
merge the Postgres-R source tree into our main CVS. Most of the
replication code will be in its own directory, with only minor
changes to our existing tree. They have single-master
replication working now, so we may have that feature in some
capacity for 7.4. I know others are working on replication
solutions. This is probably the time to decide for certain if
this is the direction we want to go for replication. Most who
have have studied Postgres-R feel it is the most promising
multi-master replication solution for reliably networked hosts.

I'd question if we would want the one-and-only builtin replication method to
be dependent on an external communication library (Spread). I would like to
see Postgres-R merged, but I'd also like to see a simple log-based option.

Comments?

I'd also second Mike Mascari's question -- whatever happened to the person
working on two-phase commit? Is that likely to be done for 7.4? Did he ever
send in a patch?

Joe

Shridhar Daithankar wrote:

And where are nested transactions?

I didn't mention nested transactions because it didn't seem to be a
_big_ item like the others.

-- 
  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, Pennsylvania 19073

On Friday 13 December 2002 17:51, Bruce Momjian wrote:

Shridhar Daithankar wrote:

And where are nested transactions?

I didn't mention nested transactions because it didn't seem to be a
_big_ item like the others.

This is big item

regards
Haris Peco

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/slave replication
o multi-master replication
o partition data across servers
o sample implementation in contrib/rserv
o queries across databases or servers (two-phase commit)
o allow replication over unreliable 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, Pennsylvania 19073

Hannu Krosing wrote:

On Fri, 2002-12-13 at 06:22, Bruce Momjian wrote:

I wanted to outline some of the big items we are looking at for 7.4:
Point-In-Time Recovery (PITR)

J. R. Nield did a PITR patch late in 7.3 development, and Patrick
MacDonald from Red Hat is working on merging it into CVS and
adding any missing pieces. Patrick, do you have an ETA on that?

How hard would it be to extend PITR for master-slave (hot backup)
repliaction, which should then amount to continuously shipping logs to
slave and doing nonstop PITR there :)

It will never be usable for multi-master replication, but somehow it
feels that for master-slave replication simple log replay would be most
simple and robust solution.

Exactly. See my previous email. We eventually have two replication
solutions: one, Postgres-R for multi-master, and PITR used for for
asynchonous master/slave.

-- 
  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, Pennsylvania 19073

Mike Mascari wrote:

What about distributed TX support:

http://groups.google.com/groups?hl=en&amp;lr=&amp;ie=UTF-8&amp;oe=UTF-8&amp;threadm=20021106111554.69ae1dcd.pgsql%40snaga.org&amp;rnum=2&amp;prev=/groups%3Fq%3DNAGAYASU%2BSatoshi%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den

OK, yes, that is Satoshi's 2-phase commit implementation. I will
address 2-phase commit vs Postgres-R in my next email about spread.

-- 
  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, Pennsylvania 19073

darren@up.hrcoxmail.com wrote:

How hard would it be to extend PITR for master-slave (hot backup)
repliaction, which should then amount to continuously shipping logs to
slave and doing nonstop PITR there :)

I have not looked at the PITR patch yet, but it might be possible
to use the same PITR format to queue/log writesetswith postgres-r,
so we can have multi-master replication and PITR from the same
mechanism.

Yes, we do need a method to send write sets to the various nodes, and
PITR may be a help in getting those write sets. However, it should be
clear that we really aren't archiving-replaying them like you would
think for PITR. We are only grabbing stuff from the PITR to send to
other nodes. We may also be able to use PITR to bring nodes back up to
date if they have fallen out of communication.

--
  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, Pennsylvania 19073

Okay. But please keep in mind that a 2-phase commit implementation is used for more than just replication. Any distributed TX will require a 2PC protocol. As an example, for the DBLINK implementation to ultimately be transaction safe (at least amongst multiple PostgreSQL installations), the players in the distributed transaction must all be participants in a 2PC exchange. And a participant whose communications link is dropped needs to be able to recover by asking the coordinator whether or not to complete or abort the distributed TX. I am 100% ignorant of the distributed TX standard Tom referenced earlier, but I'd guess there might be an assumption of 2PC support in the implementation. In other words, I think we still need 2PC, regardless of the method of replication. And if Satoshi Nagayasu has an implementation ready, why not investigate its possibilities?

Mike Mascari
mascarm@mascari.com

----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>

Joe Conway wrote:

Bruce Momjian wrote:

Win32 Port:

Katie Ward and Jan are working on contributing their Win32
port for 7.4. They plan to have a patch available by the end of
December.

I have .Net Studio available to me, so if you need help in merging or testing
or whatever, let me know.

OK, Jan, let him know how he can help.

Point-In-Time Recovery (PITR)

J. R. Nield did a PITR patch late in 7.3 development, and Patrick
MacDonald from Red Hat is working on merging it into CVS and
adding any missing pieces. Patrick, do you have an ETA on that?

As Hannu asked (and related to your question below), is there any thought of
extending this to allow simple log based replication? In many important
scenarios that would be more than adequate, and simpler to set up.

Yes, see previous email.

I'd question if we would want the one-and-only builtin replication method to
be dependent on an external communication library (Spread). I would like to
see Postgres-R merged, but I'd also like to see a simple log-based option.

OK, let me reiterate I think we will have two replication solutions in
the end --- on Postgres-R for multi-master/synchronous, and PITR for
master/slave asynchronous replication.

Let me address the Spread issue and two-phase commit. (Spread is an
open source piece of software used by Postgres-R.)

In two-phase commit, when one node is about to commit, it gets a lock
from all the other nodes, does its commit, then releases the lock. (Of
course, this is simplified.) It is called two-phase because it says to
all the other nodes "I am about to do something, is that OK?", then when
gets all OK's, it does the commit and says "I did the commit".

Postgres-R uses a different mechanism. This method is shown on page 22
and 24 and following of:

ftp://gborg.postgresql.org/pub/pgreplication/stable/PostgreSQLReplication.pdf.gz

The basic difference is that Spread groups all the write sets into a
queue who's ordering is the same on all the nodes. Instead of asking
for approval for a commit, a node puts its commit in the Spread queue,
and then waits for its own commit to come back in the queue, meaning all
the other nodes saw its commit too.

The only tricky part is that while reading the other node's write sets
before its own arrives, it has to check to see if any of these conflict
with its own write set. If it conflicts, it has to assume the earlier
write set succeeded and its own failed. It also has to check the write
set stream and apply only those changes that don't conflict.

As stated before in Postgres-R discussion, this mechanism hinges on
being able to determine which write sets conflict because there is no
explicit "I aborted", only a stream of write sets, and each node has to
accept the non-conflicting ones and reject the conflicting ones.

I'd also second Mike Mascari's question -- whatever happened to the person
working on two-phase commit? Is that likely to be done for 7.4? Did he ever
send in a patch?

I have not seen a patch from him, but it is very likely he could have
one for 7.4. This is why it is good we discuss this now and figure out
where we want to go for 7.4 so we can get started.

-- 
  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, Pennsylvania 19073

Mike Mascari wrote:

Okay. But please keep in mind that a 2-phase commit implementation
is used for more than just replication. Any distributed TX will
require a 2PC protocol. As an example, for the DBLINK implementation
to ultimately be transaction safe (at least amongst multiple
PostgreSQL installations), the players in the distributed
transaction must all be participants in a 2PC exchange. And a
participant whose communications link is dropped needs to be
able to recover by asking the coordinator whether or not to
complete or abort the distributed TX. I am 100% ignorant of the
distributed TX standard Tom referenced earlier, but I'd guess
there might be an assumption of 2PC support in the implementation.
In other words, I think we still need 2PC, regardless of the
method of replication. And if Satoshi Nagayasu has an implementation
ready, why not investigate its possibilities?

This is a good point. I don't want to push Postgres-R as our solution.
Rather, I have looked at both and like Postgres-R, but others need to
look at both and decide so we are all in agreement when we move forward.

--
  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, Pennsylvania 19073

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

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.

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

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>

Mike Mascari wrote:

Okay. But please keep in mind that a 2-phase commit implementation
is used for more than just replication.

This is a good point. I don't want to push Postgres-R as our solution.
Rather, I have looked at both and like Postgres-R, but others need to
look at both and decide so we are all in agreement when we move forward.

After having read your post regarding Spread, I see that it is an alternative to 2PC as a distributed TX protocol. If I understand you correctly, a DBLINK implementation built atop Spread would also be possible. Correct? The question then is, do other RDBMS expose a 2PC implementation which could not then be leveraged at a later time? For example imagine:

1. 7.4 includes a native 2PC protocol with:

CREATE DATABASE LINK accounting
CONNECT TO accounting.acme.com:5432
IDENTIFIED BY mascarm/mascarm;

SELECT *
FROM employees@accounting;

INSERT INTO employees@accounting
VALUES (1, 'Mike', 'Mascari');

That would be great, allowing PostgreSQL servers running in different departments to participate in a distributed tx.

2. 7.5 includes a DBLINK which supports PostgreSQL participating in a heterogenous distributed transaction (with say, an Oracle database):

CREATE DATABASE LINK finance
CONNECT TO <oracle names entry>
IDENTIFIED BY mascarm/mascarm
USING INTERFACE 'pg2oracle.so';

INSERT INTO employees@finance
VALUES (1, 'Mike', 'Mascari');

I guess I'm basically asking:

1) Is it necessary to *choose* between support for 2PC and Spread (Postgres-R) or can't we have both? Spread for Replication, 2PC for non-replicating distributed TX?

2) Do major SQL DBMS vendors which support distributed options expose a callable interface into a 2PC protocol that would allow PostgreSQL to participate? I could check on this...

3) Are there any standards (besides ODBC, which, the last time I looked just had COMMIT/ABORT APIs), that have been defined and adopted by the industry for distributed tx?

Again, I'd guess most people want:

1) High performance Master/Master replication *and* (r.e. Postgres-R)
2) Ability to participate in distrubuted tx's (r.e. 2PC?)

Mike Mascari
mascarm@mascari.com

On Fri, 2002-12-13 at 13:20, Bruce Momjian wrote:

Let me address the Spread issue and two-phase commit. (Spread is an
open source piece of software used by Postgres-R.)

Note that while Spread is open source in the sense that "the source is
available", it's license is significantly more restrictive than
PostgreSQL's:

http://www.spread.org/license/

Just FYI...

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Neil Conway wrote:

On Fri, 2002-12-13 at 13:20, Bruce Momjian wrote:

Let me address the Spread issue and two-phase commit. (Spread is an
open source piece of software used by Postgres-R.)

Note that while Spread is open source in the sense that "the source is
available", it's license is significantly more restrictive than
PostgreSQL's:

http://www.spread.org/license/

Interesting. It looks like a modified version of the old BSD license
where you are required to mention you are using Spread. I believe we
can get that reduced. (I think Darren already addressed this with
them.) We certainly are not going to accept software that requires all
PostgreSQL user sites to mention Spread.

The whole "mention" aspect of the old BSD license was pretty ambiguous,
and I assume this is similar.

-- 
  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, Pennsylvania 19073

I wrote:

I guess I'm basically asking:

1) Is it necessary to *choose* between support for 2PC and Spread (Postgres-R) or can't we have both? Spread for Replication, 2PC for non-replicating distributed TX?

2) Do major SQL DBMS vendors which support distributed options expose a callable interface into a 2PC protocol that would allow PostgreSQL to participate? I could check on this...

3) Are there any standards (besides ODBC, which, the last time I looked just had COMMIT/ABORT APIs), that have been defined and adopted by the industry for distributed tx?

Answer:

The Open Group's Open/XA C193 specificiation for API for distributed transactions:

http://www.opengroup.org/public/pubs/catalog/c193.htm

I couldn't find any draft copies on the web, but a good description at the Sybase site:

http://manuals.sybase.com/onlinebooks/group-xs/xsg1111e/xatuxedo/@ebt-link;pt=61?target=%25N%13_446_START_RESTART_N%25

The standard is 2PC based.

Mike Mascari
mascarm@mascari.com