From markus@bluegap.ch Tue Jul 25 14:56:27 2006 X-Original-To: pgsql-docs-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id D93109FB27F for ; Tue, 25 Jul 2006 11:56:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 14279-02-7 for ; Tue, 25 Jul 2006 14:56:13 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id BF4079FB2E8 for ; Tue, 25 Jul 2006 08:26:58 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 83AF730E11; Tue, 25 Jul 2006 13:26:57 +0200 (MET DST) From: Markus Schiltknecht X-Newsgroups: pgsql.docs Subject: Re: Replication - super-draft Date: Tue, 25 Jul 2006 13:33:44 +0200 Organization: Hub.Org Networking Services Lines: 349 Message-ID: <44C60198.6070008@bluegap.ch> References: <> <6064hmd7vn.fsf_-_@dba2.int.libertyrms.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: Chris Browne User-Agent: Thunderbird 1.5.0.4 (X11/20060713) In-Reply-To: <6064hmd7vn.fsf_-_@dba2.int.libertyrms.com> To: pgsql-docs@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.72 tagged_above=0 required=5 tests=SARE_SPEC_REPLICA X-Spam-Level: X-Archive-Number: 200607/20 X-Sequence-Number: 3610 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 the advantage of capturing updates at the end of > processing when all column values have been finalized. The > use of transaction visibility (MVCC) and ordering can provide > strong guarantees on consistency. > > Of course, firing a trigger for each tuple update > comes at a not inconsiderable cost: a statement that touches > 10,000 tuples will fire the trigger 10,000 times, and > transform, on the subscriber, into 10,000 SQL > statements. > > Statement capture almost exactly reverses the > issues, as compared to triggers. > > There are no strong guarantees on consistency: any > sort of nondeterministic query can corrupt > things by introducing differences between nodes. Here are > four examples of cases where naive statement capture is sure > to get things wrong: > > > INSERT INTO mytable (txntime, product, quantity, taxes, total) values (now(), 'AB-275', 10, 45, 250.00); > Some replication systems parse the queries, replacing date requests with timestamps. > > INSERT INTO table2 (random() * > 50); > In this case, nondeterminism is fairly much the > point! > > Any use of sequnce values as defaults, > particularly with per-connection value cacheing, will open > up occasions for values to diverge between > nodes. > INSERT INTO tab1 (txn_type, > tdate, quantity, units, price) SELECT * FROM tab2 ORDER BY > txn_type limit 50; > > There are many variations on this which will turn > out badly: > > If there are default fields in tab1 > that are set using sequences, the only way to even > hope for the same ordering is to have > an ORDER BY clause that ensures > identical ordering on both hosts. > If the ordering isn't a suitable > total ordering, the requests for data from tab2 may > find different data on different > hosts. > Columns with a default > of now() will be troublesome as > mentioned earlier, and this makes the problem harder > because unlike in the earlier query, where one might > substitute '2006-09-02 04:42:23-00' > for now(), this requires a > substantial rewriting of the query. > > > > > > > > > > > > > > > > PostgreSQL Replication Systems and Their Uses > > Based on the preceding taxonomy, we may categorize various > replication systems, which should be helpful in determining what they > may be best used for, and whether they are compatible with > your use case. > > Slony-I > > Slony-I is a single-master to multiple subscriber asynchronous > replication system that captures updates using triggers. > > For many systems, it is not clear how to initialize replication > on a new node some time after a system has been set up in production. > Slony-I was specifically designed to provide the ability to introduce > new nodes without the need to interrupt activity on the master > node. > > It has, a particular merit, that, by only using components > internal to PostgreSQL, it is compatible with multiple versions of > PostgreSQL. This lends it especially to assisting at upgrading systems > from one version of PostgreSQL to another without requiring a long > outage. > > It suffers from three particular problems: > > > Despite improvements from earlier versions, it is fairly complex to configure and administer. > It can only replicate changes that can be captured using triggers. > There is a handling for sequences, which comes via polling, but Slony-I does not provide an automatic way to replicate other sorts of objects. > The handling of DDL changes is somewhat fragile, and exists as something of a bag on the side. > There has been loose discussion as to how to address that; useful comprehensive answers have not emerged. > > > > > > pgpool > > pgpool was initially created by > Tatsuo Isshii as a portable alternative to Java connection pool > modules. He subsequently observed that it wouldn't take very much > effort to extend it to create a simple replication system: if it is > forwarding SQL queries to a PostgreSQL instance, extending that to > two databases is very straightforward. > > It suffers, by nature, from the problems associated with > replicating using capture of SQL statements; any sort of > nondeterminism in the replicated statements will cause the databases > to diverge. > > On the other hand, it is very easy to install and configure; > for users with simple requirements, that can suffice. > > A pgpool-2 is under way which > introduces a more sophisticated query parser to try to address the > nondeterminism issues; that may limit ongoing support for the legacy > version. > > > > PITR - Point In Time Recovery > > If you have a database cluster that supports a large number of > database instances (e.g. - varying values for > PGDATABASE), connection-managing systems like pgpool and systems like > Slony-I which require a manager process for each database for each > node that is replicated will turn out quite badly. > > For instance, if you have a database cluster that hosts 300 > databases, as would be the case in a "web hosting" situation, for > Slony-I to replicate all of this data, it would have to have 300 slon > processes for each node. > > PITR is likely to be more suitable in this case; that doesn't > provide you with a usable replica running, but it can recover *all* of > the tables in *all* of the databases on the backend. > Postgres-R > > This has been a research project at McGill University, > building a multimaster synchronous replication system which uses a > group communications system (e.g. - Spread) to control propagation > of update requests, which it captures via > adding hooks to the database engine to detect > changes. > > Being a research project, the key has been to learn about > replication as opposed to provide a production > grade replication system. For a considerable period of > time it was only at all usable on rather old releases of > PostgreSQL. > > The handling of DDL changes has long been somewhat > controversial; several attempts to implement DDL handlers have been > made, none of which has stuck. > > > > Slony-II > > This project inherited directly from Postgres-R, with an > intent to create a multimaster synchronous replication system atop a > group communications system, but then to proceed to something more > of production grade. > > The notable distinction from Postgres-R was that, in order to > find conflicts earlier, and to diminish the amount of work needing > to be done at the synchronization point, Slony-II would try to > publish and promote lock requests as soon as possible. > > Unfortunately several problems emerged: > > > > The available open source group communications > systems turn out to neither be fast enough nor reliable enough for > the purpose. > > One of the goals was for there to be as little > need as possible to modify applications to deal with > replication. > > Unfortunately, there turn out to be some cases where > competing updates (e.g. - for updates to account balances) would > cause multimaster replication to reject transactions due to > concurrency problems with high frequency. > > > > > Unless some fundamentally better group communications system > emerges, it is unlikely that Slony-II can progress further any time > soon. > > > > pgcluster > > Nothing to say yet... > > From markus@bluegap.ch Tue Jul 25 18:50:22 2006 X-Original-To: pgsql-docs-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CC2549FA666 for ; Tue, 25 Jul 2006 15:50:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 81743-03 for ; Tue, 25 Jul 2006 15:50:19 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 45DFC9FA65C for ; Tue, 25 Jul 2006 15:50:18 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id A80D830E10; Tue, 25 Jul 2006 20:50:17 +0200 (MET DST) From: Markus Schiltknecht X-Newsgroups: pgsql.docs Subject: Re: Replication - super-draft Date: Tue, 25 Jul 2006 20:50:18 +0200 Organization: Hub.Org Networking Services Lines: 48 Message-ID: <44C667EA.7080309@bluegap.ch> References: <6064hmd7vn.fsf_-_@dba2.int.libertyrms.com> <44C60198.6070008@bluegap.ch> <60hd15br5y.fsf@dba2.int.libertyrms.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: Chris Browne User-Agent: Thunderbird 1.5.0.4 (X11/20060615) In-Reply-To: <60hd15br5y.fsf@dba2.int.libertyrms.com> To: pgsql-docs@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.499 tagged_above=0 required=5 tests=AWL, SARE_SPEC_REPLICA X-Spam-Level: X-Archive-Number: 200607/21 X-Sequence-Number: 3611 Hi, Chris Browne wrote: > Under conditions where one expects to see a lot of conflicting > updates, pushing out locks earlier would allow sooner discovery of > these conflicts; whether this improves or worsens total performance is > at least a bit ambiguous. That's a good point, yes. Given one gets lots of conflicts, one should probably go for async (or single master) replication anyway. (Or see the performance degrade below single-node operation, which is imaginable for both approaches, IMHO. Finally, only benchmarking will tell.) [ In single node operation, we have READ COMMITTED vs. SERIALIZABLE, which is pessimistic vs. optimistic. Correct me if I'm wrong, but in general I thought, optimistic locking (SERIALIZABLE) is prefered and leads to better performance. That's why came to think the same holds true for replication. Or is READ COMMITTED better if you have lots of conflicting transactions? Any numbers on that? ] Coming back to thinking about the replicaiton doc... the draft describes the different types of replication very well. But no all users will immediately see what each type is good for. Therefore we should probably also cover some use cases, i.e. what type of replication to use when. The most frequently asked use case probably being a hot-backup with failover capability -> single-master, async / sync, depending on your needs. Another use case could probably describe a multi-master scenario and outline pros and cons of that (perhaps gently touching the above issue?) Another issue that comes to mind: PgCluster2 targets shared-disk clusters, a significant difference to shared-nothing clusters. Maybe that gives another paragraph under 'categorization'? Given that additional categorization: should the use cases be added per replication solution? >> 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. > > That is indeed useful editorial guidance... Sorry, I must have felt super clever. ;-) Although... as a non native english speaker, I would still like to know the preferred spelling. Regards Markus