Ibrar Ahmed joined Percona in July 2018. Prior to joining Percona, Ibrar spent 10 years as a senior database architect at EnterpriseDB. Ibrar has 18 years of software development experience. Ibrar has written several books on PostgreSQL.
Bo Wei, executive Director of Training and Certification of China PG Branch, is a senior database engineer. He has more than 10 years of experience in database operation and maintenance management and training. He has a good command of PostgreSQL architecture deployment and performance optimization, and is committed to promoting the development of PostgreSQL in China.
Because of the volume of data, scalability has become one of the hottest topics in the database world. Scalability can be achieved horizontally or vertically. Vertical scalability means adding more resources/hardware to an existing node to enhance the database’s ability to store and process more data, for example, adding CPUS, memory, or disks to an existing node. Each DBMS engine improves vertical scalability by improving locking/mutex mechanisms and concurrency so that newly added resources can be used more efficiently. The database engine provides configuration parameters that help make more efficient use of available hardware resources.
It is not always possible to add new hardware because of the cost of hardware and the limitations of adding new hardware to existing nodes. Therefore, horizontal scalability is required, which means adding more nodes to existing network nodes rather than enhancing the capabilities of existing nodes. In contrast to vertical scalability, horizontal scalability is difficult to implement. This requires more development work.
PostgreSQL provides a fairly rich set of capabilities for both vertical and horizontal scalability. It supports computers with multiple processors and large amounts of memory, and provides configuration parameters to manage the use of these resources. PostgreSQL’s new parallelism features make vertical scalability more prominent, but horizontal scalability is not lacking either. Replication is a key pillar of horizontal scalability, and PostgreSQL supports one-way master-slave replication, which is sufficient for many use cases.
A key concept
Database replication
Database replication replicates data to other servers and stores it on multiple nodes. In this process, database instances are moved from one node to another and replicated precisely. Data replication is used to improve data availability, which is a key feature of HA. There is usually a complete database instance, or some frequently used or needed objects that are copied to another server. Replication provides multiple consistent copies of a database, which not only provides high availability, but also improves query performance.
Synchronous replication
There are two policies for writing data to disks: Synchronous and asynchronous. Synchronous replication means writing data to both the master and slave servers, in other words, “synchronous replication” means committing to wait for writes/flushes at the remote end. Synchronous replication is used in high-end transactional environments with immediate failover requirements.
Asynchronous replication
Asynchronous means that data is first written to the host and then copied to the slave. In the case of a crash, data loss may occur, but asynchronous replication provides little overhead and is therefore acceptable in most cases. It does not overburden the host. Failover from master to slave databases takes longer than synchronous replication. In short, the main difference between synchronous and asynchronous is when data is written to the master and slave servers.
A single master replicates
Single master replication means that data changes are only allowed on a single node and those changes are replicated to one or more nodes. Data updates and inserts can only be performed on the primary node. In this case, the application needs to route traffic to the primary server, which adds complexity to the application. Because only one master node is responsible for writing data, there is no chance of collisions. In most cases, single-master replication is sufficient for applications because it is not that complex to configure and manage. But in some cases, a single master replicate is not enough; you need a multi-master replicate.
The master replicate more
Multi-master replication means that there are multiple nodes acting as master nodes. Data is replicated between nodes and can be updated and inserted across a set of master nodes. In this case, there are multiple copies of the data. The system is also responsible for resolving any conflicts that occur between concurrent changes. There are two main reasons for having multiple master replicates. One is high availability and the second is performance. In most cases, certain nodes are dedicated to intensive write operations, and certain nodes are dedicated to certain nodes or for failover.
Advantages and disadvantages of multi-master replication
Advantages:
- In case one host fails, the other host can still provide update and insert services.
- The primary nodes are located in several different locations, so the chance of failure of all the primary nodes is very small.
- Data updates can be performed on multiple servers.
- Applications do not need to route traffic only to a single host.
Disadvantages:
- The main disadvantage of multi-master replication is its complexity.
- Conflict resolution is difficult because you can write on multiple nodes at the same time.
- Human intervention is sometimes required in conflict situations.
- The possibility of inconsistent data.
As we have already discussed, single-master replication is sufficient in most cases and is strongly recommended, but in some cases, multi-master replication is still required. PostgreSQL has a built-in single-master replication, but unfortunately there is no multi-master replication in the PostgreSQL master version. There are several multi-master replication solutions available, some of which are in the form of applications and some of which are PostgreSQL branches. These spinoffs have their own small communities and are mostly managed by a single company rather than the PostgreSQL international community.
These solutions come in many categories, including open source/closed source, priority, free and paid.
- BDR (Two-way replication)
- xDB
- PostgreSQL-XL
- PostgreSQL-XC / PostgreSQL-XC2
- Rubyrep
- Bucardo
These are some of the key features of all replication solutions
1.BDR (bidirectional replication)
BDR is a multi-master replication solution and comes in different versions. Earlier versions of BDR were open source, but its latest version is closed source. This solution was developed by 2ndQuadrant and is one of the most elegant multi-master solutions to date. BDR provides asynchronous multi-master logical replication. This is based on the PostgreSQL logical decoding function. Because BDR applications essentially replay transactions on other nodes, the replay operation may fail if there is a conflict between the transaction being applied and the transaction committed on the receiving node.
2.xDB
EnterpriseDB developed its own two-way replication solution in Java, called xDB. It’s based on its own protocol. Because it is a closed source solution, no design information is known to the outside world.
- Developed and maintained by EnterpriseDB.
- Develop in Java.
- The source code is closed source.
- The xDB Replication Server contains multiple executable programs.
- This is a completely closed source proprietary software.
- When you develop in Java, people complain about performance.
- Failover time is unacceptable.
- The user interface is used to configure and maintain the replication system.
3.PostgreSQL XC/XC2
Postgresql-xc was developed by EnterpriseDB and NTT. It is a synchronous replication solution. Postgres-xc is an open source project designed to provide a writable, scalable, synchronous, symmetric, and transparent PostgreSQL clustering solution. Over the years, I have never seen much development of PostgresQL-XC on EnterpriseDB and NTT. At present, Huawei is working on this. Some performance improvements have been reported for OLAP, but not for TPS.
4.PostgreSQL XL
It is a branch of PostgresQL-XC, currently supported by 2ndQuadrant. It will lag behind the community PostgreSQL version iteration. It is based on PostgreSQL 10.6, which is not compatible with PostgreSQL’s latest version, PostgresQL-12. We know it’s postgresQL-XC based, which is pretty good when we talk about OLAP, but not very good for high TPS.
Note: All PostgreSQL XC/XC2/XL are considered “PostgreSQL derivatives” and are not synchronized with current PostgreSQL development.
5.Rubyrep
It is an asynchronous master/master replication developed by Arndt Lehmann. It claims to have the simplest configuration features and can run across platforms (including Windows). It always runs on two servers, called “left” and “right” in Rubyrep terminology. Therefore, it would be more appropriate to call it a “2-master” setup rather than a “multi-master” setup.
- Rubyrep can continuously replicate changes between the left and right databases.
- Automatically set up the necessary triggers, log tables, etc
- Automatically discover newly added tables and synchronize table contents
- Automatically reconfigure the sequence to avoid duplicate key collisions
- Tracks changes to primary key columns
- Master/slave replication can be implemented simultaneously
- Provide pre-established conflict resolution: left/right wins; Early/late change wins
- Custom conflict resolution can be specified through Ruby code snippets
- Replication decisions can optionally be recorded in the RubyREP event log table
Note: — In terms of development, this project has not been started for the last three years.
6.Bucardo
Bucardo is a flip-flop based replication solution developed by Jon Jensen and Greg Sabino Mullane at End Point. Bucardo’s solution, which has been around for nearly 20 years, was originally designed as a “lazy” asynchronous solution that eventually replicates all changes. There is a Perl daemon that listens for NOTIFY requests and performs operations on them. Changes made on the table are recorded in the table (bucardo_delta) and notified to the daemon. The daemon notifies the controller, which starts a child process to synchronize table changes. If there are conflicts, they are handled using standard or custom conflict handlers.
- Trigger based replication
- Have conflict resolution strategies
- Dependencies on Perl 5, DBI, DBD :: Pg, DBIx :: Safe.
- Installation and configuration are complex.
- Replication often breaks and errors occur.
conclusion
Single-host replication is sufficient in most cases, and it has been observed that people are configuring multi-host replication and overcomplicating their designs. It is highly recommended to design the system and try to avoid multi-master replication and only use it when there is no other way. There are two reasons: first, it makes the system too complex and difficult to debug; Second, because there is no community-maintained multi-master replication available, you will not be able to get support from the PostgreSQL community.
Original link:www.percona.com/blog/2020/0…
For more exciting content, please pay attention to the following platforms and websites:
PostgreSQL Branch official official number (technical articles, technical activities) : open source software Alliance PostgreSQL branch
China Postgre SQL Branch technical q&A community: www.pgfans.cn
Official website of China Postgre SQL Branch: www.postgresqlchina.com