Hello Chris,
thank you for putting this together. It seems to be the replication
guide we are looking for: an overview over the principles and solutions
of replication for PostgreSQL. Good work!
Some suggestions I came up with when reading: when preparing my
presentation I came to the conclusion that first introducing 'single- vs
multi-master' before 'sync vs async' is easier. Especially because of
the 'after-commit-conflicting-transactions' problem that rises in async,
multi-master solutions, YMMV.
Being the Postgres-R (8) guy, I of course have some additions to that
paragraph: You mention Postgres-R being only usable on rather old
releases. Please also mention that I'm actively working on a production
grade Postgres-R for an up-to-date PostgreSQL version. There already is
a downloadable, working prototype.
Concerning Slony-II: AFAIK there is no mechanism to detect conflicts
earlier for normal transactions (maybe for DDL or TABLE LOCKS or
something, but mentioning that here is misleading). Such a distributed
locking mechanism would increase network traffic - and decrease total
performance.
Because Slony-II and Postgres-R are that similar, they also share the
'problems that emerged'. Because Slony-II and Postgres-R are coceptually
mostly identical, I'd propose to merge those two paragraphs. You might
want to add that both projects were created independently, but their
authors currently plan to join their work and efforts.
A nit-picking detail: I'm unsure about the correct english spelling of
single- and multi-master ('single master', 'single-master' or
'singlemaster'), but please make sure to use a consistent spelling.
Regrards
Markus
Chris Browne wrote:
Here is a "real drafty beginning" to a set of docs on replication.
Bruce was soliciting this at the Code Sprint... Nobody really promised it
Here's a beginning to it. It's based on my impressions; I don't care
how much it gets "hacked up" from here. Hopefully it will be quicker
to start with this than to start from the beginning with something
else.
Replication
replication
People frequently ask about what replication options are
available for PostgreSQL.
Unfortunately, there are so many approaches and models to this
that are useful for different purposes that things tend to get
confusing.
At perhaps the most primitive level, one might
use tools,
whether
or to create additional
copies of databases. This doesn't provide
any way to keep the replicas up to date; to bring the state of
things to a different point in time requires bringing up another
copy. There is no way, with these tools, for updates on
a master system to automatically propagate to the
replicas.
Categorization of Replication Systems
Looking at replication systems, there are a number of ways in
which they may be viewed:
Synchronous versus asynchronous
Synchronous systems are ones where updates must be
accepted on all the databases before they are permitted
to COMMIT.
Asynchronous systems propagate updates to the other
databases later. This permits the possibility that one
database may have data significantly behind others. Whether
or not being behind is acceptable or not will depend on the
nature of the application.
Single master versus multimaster.
That is, whether there is a single database
considered master, where all update operations
are required to be submitted, or the alternative, multimaster,
where updates may be submitted to any of several
databases.
Multimaster replication is vastly more complex and
expensive, because of the need to deal with the possibility of
conflicting updates. The simplest example of this is where a
replicated database manages inventory; the question is, what
happens when requests go to different database nodes
requesting ` a particular piece of inventory?
Synchronous multimaster replication introduces the need
to distribute locks across the systems, which, in research work
done with Postgres-R and Slony-II, has proven to be very
expensive.
Asynchronous multimaster replication introduces the
possibility that conflicting updates will be accepted by
multiple nodes, as they don't know, at COMMIT
time, that the updates conflict. It is then necessary to have
some sort of conflict resolution system, which can't really be
generalized as a generic database facility. An instance of this
that is commonly seen is in the PalmOS
HotSync system; the general policy
when conflicts are noticed is to allow both conflicting records
to persist until a human can intervene. That may be quite
acceptable for an address book; it's not
fine for OLTP systems.
Update capture methods
Common methods include having triggers on tables,
capturing SQL statements, and capturing transaction log (WAL)
updates
Triggers, as used in eRServer and Slony-I,
have
