Replication documentation addition
in [PgSql]
|
Prev: Lock partitions
Next: Piggybacking vacuum I/O
From: Bruce Momjian on 23 Oct 2006 23:55 Please disregard. I am redoing it and will post a URL with the most recent version. --------------------------------------------------------------------------- Bruce Momjian wrote: > > Here is my first draft of a new replication section for our > documentation. I am looking for any comments. > > --------------------------------------------------------------------------- > > Replication > =========== > > Database replication allows multiple computers to work together, making > them appear as a single computer to user applications. This might > involve allowing a backup server to take over if the primary server > fails, or it might involve allowing several computers to work together > at the same time. > > It would be ideal if database servers could be combined seamlessly. Web > servers serving static web pages can be combined quite easily by merely > load-balancing web requests to multiple machines. In fact, most > read-only servers can be combined relatively easily. > > Unfortunately, most database servers have a read/write mix of requests, > and read/write servers are much harder to combine. This is because > though read-only data has to be placed on each each server only once, a > write to any server has to be seen by all other servers so that future > read requests to those servers return consistent results. > > This "sync problem" is the fundamental difficulty of doing database > replication. Because there is no single solution that limits the impact > of the sync problem for all workloads, there are multiple replication > solutions. Each solution addresses the sync problem in a different way, > and minimizes its impact for a specific workload. > > This section first outlines two important replication capabilities, and > then outlines various replication solutions. > > Sychronous vs. Asynchronous Replication > --------------------------------------- > > The term sychronous replication means that a query is not considered > committed unless all servers have access to the committed records. In > that case, a failover to a backup server will lose no data records. > Asynchronous replication has a small delay between the time of commit > and its propogation to backup servers, opening the possibility that some > transactions might be lost in a switch to a backup server. Asynchronous > is used when sychronous replication would be too slow. > > Full vs. Partial Replication > ---------------------------- > > The term full replication means only a full database cluster can be > replicated, while partial replication means more fine-grained control > over replicated objects is possible. > > Shared Disk Failover > -------------------- > > This replication solution avoids the sync problem by having only one > copy of the database. This is possible because a single disk array is > shared by multiple servers. If the main database server fails, the > backup server is able to mount and start the database as though it was > restarting after a database crash. This shared hardware functionality > is common in network storage devices. This allows sychronous, full > replication. > > Warm Standby Using Point-In-Time Recovery > ----------------------------------------- > > A warm standby server (add doc xref) can be kept current by reading a > stream of WAL records. If the main server fails, the warm standby > contains almost all of the data as the main server, and can be used as > the new database server. This allows asychronous, full replication. > > Point-In-Time Recovery [Asychronous, Full] > ---------------------- > > A Point-In-Time Recovery is the same as a Warm Standby server except > that the standby server must go though a full restore and archive > recovery operation, delaying how quickly it can be used as the main > database server. This allows asychronous, full replication. > > Continuously Running Failover Server > ------------------------------------ > > A continuously running failover server allows the backup server to > answer read-only queries while the master server is running. It > receives a continuous stream of write activity from the master server. > Because the failover server can be used for read-only database requests, > it is ideal for data warehouse queries. Slony offers this as > asychronous, partial replication. > > Data Partitioning > ----------------- > > Data partitioning partitions 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 main office, 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 such 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 pull from both tables at > the same time. > > Query Broadcast Replication > --------------------------- > > This involves sending write queries to multiple servers. Read-only > queries can be sent to a single server because there is no need for all > servers to process it. This can be complex to setup because functions > like random() and CURRENT_TIMESTAMP will have different values on > different servers, and sequences should be consistent across servers. > Pgpool implements this type of replication. > > Multi-Master Replication > ------------------------ > > In multi-master replication, each server can accept write requests, and > these write requests are broadcast to all other servers before the > transaction commits. Under heavy load, this type of replication can > cause excessive locking and performance degradation. It is implemented > by Oracle in their RAC product. PostgreSQL does not offer this type of > replication, though PostgreSQL two-phase commit can be used to implement > this in application code. > > Performance > ----------- > Performance must be considered in any repliacation choice. There is > usually a tradeoff between functionality and performance. For example, > full sychronousreplication over a slow network might
From: Hannu Krosing on 24 Oct 2006 09:29 Ü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
From: Markus Schiltknecht on 24 Oct 2006 09:54 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo(a)postgresql.org so that your message can get through to the mailing list cleanly
From: Josh Berkus on 24 Oct 2006 14:37 Bruce, > Here is my first draft of a new replication section for our > documentation. I am looking for any comments. Hmmm ... while the primer on different types of replication is fine, I think what users were really looking for is a listing of the different replication solutions which are available for PostgreSQL and how to get them. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo(a)postgresql.org so that your message can get through to the mailing list cleanly
From: "Luke Lonergan" on 24 Oct 2006 20:56
Bruce, > -----Original Message----- > From: pgsql-hackers-owner(a)postgresql.org > [mailto:pgsql-hackers-owner(a)postgresql.org] On Behalf Of Bruce Momjian > Sent: Tuesday, October 24, 2006 5:16 PM > To: Hannu Krosing > Cc: PostgreSQL-documentation; PostgreSQL-development > Subject: Re: [HACKERS] Replication documentation addition > > > OK, I have updated the URL. Please let me know how you like it. There's a typo on line 8, first paragraph: "perhaps with only one server allowing write rwork together at the same time." Also, consider this wording of the last description: "Single-Query Clustering..." Replaced by: "Shared Nothing Clustering ----------------------- This allows multiple servers with separate disks to work together on a each query. In shared nothing clusters, the work of answering each query is distributed among the servers to increase the performance through parallelism. These systems will typically feature high availability by using other forms of replication internally. While there are no open source options for this type of clustering, there are several commercial products available that implement this approach, making PostgreSQL achieve very high performance for multi-Terabyte business intelligence databases." - Luke ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org |