Replication documentation addition

Hello Bruce,

Bruce Momjian wrote:

Here is a new replication documentation section I want to add for 8.2:

ftp://momjian.us/pub/postgresql/mypatches/replication

Comments welcomed.

Thank you, that sounds good. It's targeted to production use and
currently available solutions, which makes sense in the official manual.

You are explaining the sync vs. async categorization, but I sort of
asked myself where the explanation of single vs multi-master has gone. I
then realized, that you are talking about read-only and a "read/write
mix of servers". Then again, you are mentioning 'Multi-Master
Replication' as one type of replication solutions. I think we should be
consistent in our naming. As Single- and Multi-Master are the more
common terms among database replication experts, I'd recommend to use
them and explain what they mean instead of introducing new names.

Along with that, I'd argue that this Single- or Multi-Master is a
categorization as Sync vs Async. In that sense, the last chapter should
probably be named 'Distributed-Shared-Memory Replication' or something
like that instead of 'Multi-Master Replication', because as we know,
there are several ways of doing Multi-Master Replication (Slony-II /
Postgres-R, Distributed Shared Memory, 2PC in application code or the
above mentioned 'Query Broadcast Replication', which would fall into a
Multi-Master Replication model as well)

Also in the last chapter, instead of just saying that "PostgreSQL does
not offer this type of replication", we could probably say that
different projects are trying to come up with better replication
solutions. And there are several proprietary products based on
PostgreSQL which do solve some kinds of Multi-Master Replication. Not
that I want to advertise for any of them, but it just sounds better than
the current "no, we don't offer that".

As this documentation mainly covers production-quality solutions (which
is absolutely perfect), can we document the status of current projects
somewhere, probably in a wiki? Or at least mention them somewhere and
point to their websites? It would help to get rid of all those rumors
and uncertainties. Or are those intentional?

Just my two cents.

Regards

Markus

Ühel kenal päeval, T, 2006-10-24 kell 00:20, kirjutas Bruce Momjian:

Here is a new replication documentation section I want to add for 8.2:

ftp://momjian.us/pub/postgresql/mypatches/replication

This is how data partitioning is currently described there

Data Partitioning
-----------------

Data partitioning splits the database into data sets. To achieve
replication, each data set can only be modified by one server. For
example, data can be partitioned by offices, e.g. London and Paris.
While London and Paris servers have all data records, only London can
modify London records, and Paris can only modify Paris records. Such
partitioning is usually accomplished in application code, though rules
and triggers can help enforce partitioning and keep the read-only data
sets current. Slony can also be used in such a setup. While Slony
replicates only entire tables, London and Paris can be placed in
separate tables, and inheritance can be used to access from both tables
using a single table name.

Maybe another use of partitioning should also be mentioned. That is ,
when partitioning is used to overcome limitations of single servers
(especially IO and memory, but also CPU), and only a subset of data is
stored and processed on each server.

As an example of this type of partitioning you could mention Bizgres MPP
(a PG-based commercial product, http://www.greenplum.com ), which
partitions data to use I/O and CPU of several DB servers for processing
complex OLAP queries, and Pl_Proxy
( http://pgfoundry.org/projects/plproxy/ ) which does the same for OLTP
loads.

I think the "official" term for this kind of "replication" is
Shared-Nothing Clustering.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

Hannu Krosing wrote:

I think the "official" term for this kind of "replication" is
Shared-Nothing Clustering.

Well, that's just another distinction for clusters. Most of the time
it's between Shared-Disk vs. Shared-Nothing. You could also see the very
Big Irons as a Shared-Everything Cluster.

While it's certainly true, that any kind of data partitioning for
databases only make sense for Shared-Nothing Clusters, I don't think
it's a 'kind of replication'. AFAIK most database replication solutions
are built for Shared-Nothing Clusters. (With the exception of
PgCluster-II, I think).

Regards

Markus

On Tue, 2006-10-24 at 00:20 -0400, Bruce Momjian wrote:

Here is a new replication documentation section I want to add for 8.2:

ftp://momjian.us/pub/postgresql/mypatches/replication

Comments welcomed.

It's a very good start to a complete minefield of competing solutions.

My first thought would be to differentiate between clustering and
replication, which will bring out many differences.

My second thought would be to differentiate between load balancing,
multi-threading, parallel query, high availability and recoverability,
which would probably sort out the true differences in the above mix. But
that wouldn't help most people and almost everybody would find fault.

IMHO most people I've spoken to take "replication" to mean an HA
solution, so perhaps we should cover it in those terms.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

OK, I have updated the URL. Please let me know how you like it.

---------------------------------------------------------------------------

Hannu Krosing wrote:

?hel kenal p?eval, T, 2006-10-24 kell 00:20, kirjutas Bruce Momjian:

Here is a new replication documentation section I want to add for 8.2:

ftp://momjian.us/pub/postgresql/mypatches/replication

This is how data partitioning is currently described there

Data Partitioning
-----------------

Data partitioning splits the database into data sets. To achieve
replication, each data set can only be modified by one server. For
example, data can be partitioned by offices, e.g. London and Paris.
While London and Paris servers have all data records, only London can
modify London records, and Paris can only modify Paris records. Such
partitioning is usually accomplished in application code, though rules
and triggers can help enforce partitioning and keep the read-only data
sets current. Slony can also be used in such a setup. While Slony
replicates only entire tables, London and Paris can be placed in
separate tables, and inheritance can be used to access from both tables
using a single table name.

Maybe another use of partitioning should also be mentioned. That is ,
when partitioning is used to overcome limitations of single servers
(especially IO and memory, but also CPU), and only a subset of data is
stored and processed on each server.

As an example of this type of partitioning you could mention Bizgres MPP
(a PG-based commercial product, http://www.greenplum.com ), which
partitions data to use I/O and CPU of several DB servers for processing
complex OLAP queries, and Pl_Proxy
( http://pgfoundry.org/projects/plproxy/ ) which does the same for OLTP
loads.

I think the "official" term for this kind of "replication" is
Shared-Nothing Clustering.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies O?
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

I have changed the text to reference "fail over" and "load balancing".
I think it makes it clearer. Let me know what you think. I am hesitant
to mention commercial PostgreSQL products in our documentation.

---------------------------------------------------------------------------

Markus Schiltknecht wrote:

Hello Bruce,

Bruce Momjian wrote:

Here is a new replication documentation section I want to add for 8.2:

ftp://momjian.us/pub/postgresql/mypatches/replication

Comments welcomed.

Thank you, that sounds good. It's targeted to production use and
currently available solutions, which makes sense in the official manual.

You are explaining the sync vs. async categorization, but I sort of
asked myself where the explanation of single vs multi-master has gone. I
then realized, that you are talking about read-only and a "read/write
mix of servers". Then again, you are mentioning 'Multi-Master
Replication' as one type of replication solutions. I think we should be
consistent in our naming. As Single- and Multi-Master are the more
common terms among database replication experts, I'd recommend to use
them and explain what they mean instead of introducing new names.

Along with that, I'd argue that this Single- or Multi-Master is a
categorization as Sync vs Async. In that sense, the last chapter should
probably be named 'Distributed-Shared-Memory Replication' or something
like that instead of 'Multi-Master Replication', because as we know,
there are several ways of doing Multi-Master Replication (Slony-II /
Postgres-R, Distributed Shared Memory, 2PC in application code or the
above mentioned 'Query Broadcast Replication', which would fall into a
Multi-Master Replication model as well)

Also in the last chapter, instead of just saying that "PostgreSQL does
not offer this type of replication", we could probably say that
different projects are trying to come up with better replication
solutions. And there are several proprietary products based on
PostgreSQL which do solve some kinds of Multi-Master Replication. Not
that I want to advertise for any of them, but it just sounds better than
the current "no, we don't offer that".

As this documentation mainly covers production-quality solutions (which
is absolutely perfect), can we document the status of current projects
somewhere, probably in a wiki? Or at least mention them somewhere and
point to their websites? It would help to get rid of all those rumors
and uncertainties. Or are those intentional?

Just my two cents.

Regards

Markus

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Simon Riggs wrote:

On Tue, 2006-10-24 at 00:20 -0400, Bruce Momjian wrote:

Here is a new replication documentation section I want to add for 8.2:

ftp://momjian.us/pub/postgresql/mypatches/replication

Comments welcomed.

It's a very good start to a complete minefield of competing solutions.

My first thought would be to differentiate between clustering and
replication, which will bring out many differences.

I have gone with "fail-over" and "load balancing" in the updated text.

My second thought would be to differentiate between load balancing,
multi-threading, parallel query, high availability and recoverability,
which would probably sort out the true differences in the above mix. But
that wouldn't help most people and almost everybody would find fault.

Yep.

IMHO most people I've spoken to take "replication" to mean an HA
solution, so perhaps we should cover it in those terms.

Yes, I removed any reference to replication. It seemed too general.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

I have updated the text. Please let me know what else I should change.
I am unsure if I should be mentioning commercial PostgreSQL products in
our documentation.

---------------------------------------------------------------------------

Hannu Krosing wrote:

?hel kenal p?eval, T, 2006-10-24 kell 00:20, kirjutas Bruce Momjian:

Here is a new replication documentation section I want to add for 8.2:

ftp://momjian.us/pub/postgresql/mypatches/replication

This is how data partitioning is currently described there

Data Partitioning
-----------------

Data partitioning splits the database into data sets. To achieve
replication, each data set can only be modified by one server. For
example, data can be partitioned by offices, e.g. London and Paris.
While London and Paris servers have all data records, only London can
modify London records, and Paris can only modify Paris records. Such
partitioning is usually accomplished in application code, though rules
and triggers can help enforce partitioning and keep the read-only data
sets current. Slony can also be used in such a setup. While Slony
replicates only entire tables, London and Paris can be placed in
separate tables, and inheritance can be used to access from both tables
using a single table name.

Maybe another use of partitioning should also be mentioned. That is ,
when partitioning is used to overcome limitations of single servers
(especially IO and memory, but also CPU), and only a subset of data is
stored and processed on each server.

As an example of this type of partitioning you could mention Bizgres MPP
(a PG-based commercial product, http://www.greenplum.com ), which
partitions data to use I/O and CPU of several DB servers for processing
complex OLAP queries, and Pl_Proxy
( http://pgfoundry.org/projects/plproxy/ ) which does the same for OLTP
loads.

I think the "official" term for this kind of "replication" is
Shared-Nothing Clustering.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies O?
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

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

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce,

I have updated the text.  Please let me know what else I should change.
I am unsure if I should be mentioning commercial PostgreSQL products in
our documentation.

I think you should mention the postgresql-only ones, but just briefly with a
link. Bizgres MPP, ExtenDB, uni/cluster, and Mammoth Replicator.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus wrote:

Bruce,

I have updated the text. Please let me know what else I should change.
I am unsure if I should be mentioning commercial PostgreSQL products in
our documentation.

I think you should mention the postgresql-only ones, but just briefly with a
link. Bizgres MPP, ExtenDB, uni/cluster, and Mammoth Replicator.

And to further this I would expect that it would be a subsection.. e.g;
a <sect2> or <sect3>. I think the open source version should absolutely
get top billing though.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:

Josh Berkus wrote:

Bruce,

I have updated the text. Please let me know what else I should change.
I am unsure if I should be mentioning commercial PostgreSQL products in
our documentation.

I think you should mention the postgresql-only ones, but just briefly with a
link. Bizgres MPP, ExtenDB, uni/cluster, and Mammoth Replicator.

And to further this I would expect that it would be a subsection.. e.g;
a <sect2> or <sect3>. I think the open source version should absolutely
get top billing though.

I am not inclined to add commercial offerings. If people wanted
commercial database offerings, they can get them from companies that
advertize. People are coming to PostgreSQL for open source solutions,
and I think mentioning commercial ones doesn't make sense.

If we are to add them, I need to hear that from people who haven't
worked in PostgreSQL commerical replication companies.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Oct 24, 2006, at 8:48 PM, Bruce Momjian wrote:

Joshua D. Drake wrote:

Josh Berkus wrote:

Bruce,

I have updated the text. Please let me know what else I should
change.
I am unsure if I should be mentioning commercial PostgreSQL
products in
our documentation.

I think you should mention the postgresql-only ones, but just
briefly with a
link. Bizgres MPP, ExtenDB, uni/cluster, and Mammoth Replicator.

And to further this I would expect that it would be a subsection..
e.g;
a <sect2> or <sect3>. I think the open source version should
absolutely
get top billing though.

I am not inclined to add commercial offerings. If people wanted
commercial database offerings, they can get them from companies that
advertize. People are coming to PostgreSQL for open source solutions,
and I think mentioning commercial ones doesn't make sense.

If we are to add them, I need to hear that from people who haven't
worked in PostgreSQL commerical replication companies.

I'm not coming to PostgreSQL for open source solutions. I'm coming
to PostgreSQL for _good_ solutions.

I want to see what solutions might be available for a problem I have.
I certainly want to know whether they're freely available, commercial
or some flavour of open source, but I'd like to know about all of them.

A big part of the value of Postgresql is the applications and extensions
that support it. Hiding the existence of some subset of those just
because of the way they're licensed is both underselling postgresql
and doing something of a disservice to the user of the document.

Cheers,
Steve

Steve Atkins wrote:

If we are to add them, I need to hear that from people who haven't
worked in PostgreSQL commerical replication companies.

I'm not coming to PostgreSQL for open source solutions. I'm coming
to PostgreSQL for _good_ solutions.

I want to see what solutions might be available for a problem I have.
I certainly want to know whether they're freely available, commercial
or some flavour of open source, but I'd like to know about all of them.

A big part of the value of Postgresql is the applications and extensions
that support it. Hiding the existence of some subset of those just
because of the way they're licensed is both underselling postgresql
and doing something of a disservice to the user of the document.

OK, does that mean we mention EnterpriseDB in the section about Oracle
functions? Why not mention MS SQL if they have a better solution? I
just don't see where that line can clearly be drawn on what to include.
Do we mention Netiza, which is loosely based on PostgreSQL? It just
seems very arbitrary to include commercial software. If someone wants
to put in on a wiki, I think that would be fine because that doesn't
seems as official.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Oct 24, 2006, at 9:20 PM, Bruce Momjian wrote:

Steve Atkins wrote:

If we are to add them, I need to hear that from people who haven't
worked in PostgreSQL commerical replication companies.

I'm not coming to PostgreSQL for open source solutions. I'm coming
to PostgreSQL for _good_ solutions.

I want to see what solutions might be available for a problem I have.
I certainly want to know whether they're freely available, commercial
or some flavour of open source, but I'd like to know about all of
them.

A big part of the value of Postgresql is the applications and
extensions
that support it. Hiding the existence of some subset of those just
because of the way they're licensed is both underselling postgresql
and doing something of a disservice to the user of the document.

OK, does that mean we mention EnterpriseDB in the section about Oracle
functions? Why not mention MS SQL if they have a better solution? I
just don't see where that line can clearly be drawn on what to
include.
Do we mention Netiza, which is loosely based on PostgreSQL? It just
seems very arbitrary to include commercial software. If someone wants
to put in on a wiki, I think that would be fine because that doesn't
seems as official.

Good question. The line needs to be drawn somewhere. It's basically
your judgement, tempered by other peoples feedback, though. If it
were me, I'd ask myself "Would I mention this product if it were open
source? Would mentioning it help people using the document?".

Cheers,
Steve

Hi,

I also wrote Bruce about that.

It happens that, if you 'freely advertise' commercial solutions (rather
than they doing so by other vehicles) you will always happen to be an
'updater' to the docs if they change their product lines, if they change
their business model, if and if.

If you cite a commercial solution, as a fair game you should cite *all*
of them. If one enterprise has the right to be listed in the
documentation, all of them might, as you will never be favouring one of
them.

That's the main motivation to write this. Moreover, if there are also
commercial solutions for high-end installs and they are cited as
providers to those solutions, it (to a point) disencourages those of
gathering themselves and writing open source extensions to PostgreSQL.

As Bruce stated, then should the documentation contemplate
EnterpriseDB's Oracle functions? Should PostgreSQL also come with it?
Wouldn't it be painful to make, say, another description for an
alternate product other than EnterpriseDB if it arises?

If people (who read the documentation) professionally work with
PostgreSQL, they may already have been briefed by those commercial
offerings in some way.

I think only the source and its tightly coupled (read: can compile along
with, free as PostgreSQL) components should be packaged into the tarball.

However, I find Bruce's unofficial wiki idea a good one for comparisons.

Regards,
Cesar

Steve Atkins wrote:

Hi,

Bruce Momjian wrote:

I have updated the text. Please let me know what else I should change.
I am unsure if I should be mentioning commercial PostgreSQL products in
our documentation.

I support your POV and vote for not including any pointers to commercial
extensions in the official documentation. If at all, they should go to
'external-projects.sgml', where PostGIS, PgAdmin and other projects are
mentioned.

I can't really get excited about the exclusion of the term
'replication', because it's what most people are looking for. It's a
well known term. Sorry if it sounded that way, but I've not meant to
avoid that term.

The newly created terms 'Query Broadcast Load Balancing' or even worse
'Multi-Master Load Balancing' are more confusing than helpful, because
these terms do not exist. (See the googlefight in [1]Googlefight: "Multi-Master Load Balancing" vs "Multi-Master Replication": http://tinyurl.com/y3k76r)

Can we name the chapter "Fail-over, Load-Balancing and Replication
Options"? That would fit everything and contain the necessary buzz words.

Also, I'm still missing Multi- vs Single-Master, which are also commonly
used terms.

IMHO, it does not make sense to speak of a synchronous replication for a
'Shared Disk Fail Over'. It's not replication, because there's no replica.

The Data Partitioning paragraph should probably mention it's close
relation with data partitioning across table spaces (and make the
differences clear).

What you call 'Query Broadcast Load Balancing' is also a multi-master
replication, thus naming only the later 'Multi-Master Load Balancing'
misleading.

I'd propose to add a subsection 'Synchronous, Multi-Master Replication'
and explain the different possibilities on how to do that:

* Query-Based
* with 2PC
* Distributed SHMEM
* (perhaps mention the optimized Postgres-R algorithm ;-)

What you called 'Single-Query Clustering' is probably better known as
'Parallel Query Execution'. It can be combined with all types of
replication (every combination of async / sync and Single- /
Multi-Master). It's maybe load balancing, but it depends on some form of
replication to distribute the data first.

I liked Chris Browns documentation in [2]Chris Browns proposal for a replication documentation: http://archives.postgresql.org/pgsql-patches/2006-08/msg00026.php which was clearer regarding
replication (which can be used to do fail-over, load-balancing,
data-partitioning or parallel query execution). I'd like to keep all
those things a little more separate to get them clear.

Regards

Markus

[1]: Googlefight: "Multi-Master Load Balancing" vs "Multi-Master Replication": http://tinyurl.com/y3k76r
Replication": http://tinyurl.com/y3k76r

[2]: Chris Browns proposal for a replication documentation: http://archives.postgresql.org/pgsql-patches/2006-08/msg00026.php
http://archives.postgresql.org/pgsql-patches/2006-08/msg00026.php

Bruce Momjian wrote:

OK, does that mean we mention EnterpriseDB in the section about Oracle
functions? Why not mention MS SQL if they have a better solution? I
just don't see where that line can clearly be drawn on what to include.
Do we mention Netiza, which is loosely based on PostgreSQL? It just
seems very arbitrary to include commercial software. If someone wants
to put in on a wiki, I think that would be fine because that doesn't
seems as official.

I agree that the commercial offerings shouldn't be named directly in the
docs, but it should be mentioned that some commercial options are
available and a starting point to find more information.

If potential new users look through the docs and it says no options
available for what they want or consider they will need in the future
then they go elsewhere, if they know that some options are available
then they will look further if they want that feature.

something like
"There are currently no open source solutions available for this option
but there are some commercial offerings. More details of some available
solutions can be found at postgresql.org/support/...."

--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

On Wed, Oct 25, 2006 at 08:22:25PM +0930, Shane Ambler wrote:

Bruce Momjian wrote:

OK, does that mean we mention EnterpriseDB in the section about Oracle
functions? Why not mention MS SQL if they have a better solution? I
just don't see where that line can clearly be drawn on what to include.
Do we mention Netiza, which is loosely based on PostgreSQL? It just
seems very arbitrary to include commercial software. If someone wants
to put in on a wiki, I think that would be fine because that doesn't
seems as official.

I agree that the commercial offerings shouldn't be named directly in the
docs, but it should be mentioned that some commercial options are
available and a starting point to find more information.

If potential new users look through the docs and it says no options
available for what they want or consider they will need in the future
then they go elsewhere, if they know that some options are available
then they will look further if they want that feature.

something like
"There are currently no open source solutions available for this option
but there are some commercial offerings. More details of some available
solutions can be found at postgresql.org/support/...."

I think this is probably the best compromise. Keep in mind that many
people who are looking at us will also be looking at MySQL, which is
itself a commercial offering. It's good to let folks know that with
PostgreSQL, they have more control over how much money they spend for
commercial add-ons and support.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Wed, Oct 25, 2006 at 11:38:11AM +0200, Markus Schiltknecht wrote:

I can't really get excited about the exclusion of the term
'replication', because it's what most people are looking for. It's a
well known term. Sorry if it sounded that way, but I've not meant to
avoid that term.

<snip>

IMHO, it does not make sense to speak of a synchronous replication for a
'Shared Disk Fail Over'. It's not replication, because there's no replica.

Those to statements are at odds with each other, at least based on
everyone I've ever talked to in a commercial setting. People will use
terms like 'replication', 'HA' or 'clustering' fairly interchangably.
Usually what these folks want is some kind of high-availability
solution. A few are more concerned with scalability. Sometimes it's a
combination of both. That's why I think it's good for the chapter to
deal with both aspects of this.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)