PostgreSQL replication cluster overview, PostgreSQL replication Cluster

PostgreSQL replication cluster overview, PostgreSQL replication Cluster

For pg replication, high availability, and load balancing clusters, write an overview for future reference.

Pg has the following various replication-Based Cluster solutions, most of which were used, and some were not sorted out at the time. There are also many cluster configuration documents on the Internet. After this document, find a complete point to focus on the backup files, so you do not need to check them everywhere.

Built-in stream replication-based functions, trigger master-slave replication slony, postgresql-II based on SQL replication, and others such as synchronizing multiple master replication EDB MMR (this also supports asynchronous replication, I 've been playing for a long time. I need to find the record of the current year and summarize it later.) asynchronous multi-master replication of Bucardo, file system-level replication of DRDB, shared disk failure to switch to NAS, depending on the situation.

Slony-I master-slave replication: the master server asynchronously sends data to the slave server. Based on triggers, table row-level replication; the master server may overhead; transactions can be terminated without waiting for other servers; only asynchronous replication may result in data loss; the slave server supports read-only queries; Table-level granularity; no conflict; developers need to maintain that all write requests are sent to the master, and all read requests go to the slave machine ???

Now we have the built-in stream replication function, so we should use slony in the future!

Pgpool-II: Copy SQL statements; support for multiple masters; no master server overhead; all servers can end transactions; no data loss; Support for read-only queries from the server; database-level replication ???; Conflict resolution mechanism required

Stream Replication
Copy wal logs; no master server overhead; asynchronous replication does not need to wait for other servers to end the transaction; synchronous replication does not lose data; the slave server supports read-only queries; instance-level granularity; no conflict

In addition, plproxy is a database component developed by skype. Although plproxy can only do horizontal sharding, put it here. Plproxy is a database component developed by skype. During use, you need to consider bandwidth and local cache. It limits the number of proxy databases to the power of 2. Proxy implements the forwarding function on user-defined functions. This requires that a large number of business logic needs to be put on the PostgreSQL server end in practical applications, reducing flexibility. It also brings challenges to possible database migration in the future.

Refer:

Http://www.postgresql.org/docs/9.3/static/different-replication-solutions.html
Http://www.postgresql.org/docs/9.3/static/hot-standby.html
Http://www.slony.info/documentation/cmds.html
Http://www.pgpool.net/mediawiki/index.php/Main_Page
Http://plproxy.projects.pgfoundry.org/doc/tutorial.html#_putting_it_all_together

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

Please refer to the following source for reprinting:
Blog.csdn.net/beiigang

Postgresql cluster replication Solution

Yuytu
References: yu
 
What is the main idea of using PostgreSQL clusters? The standby function of PostgreSQL is currently feasible. Let's take a look at the introduction of the original document.