Are you working with customers whose databases are updated by users in multiple locations? Are your users are in multiple time zones? If the answer is “yes”, the challenge for the DBA is how to manage replicated systems that allow for quick database access and for rapid, asynchronous replication.In PostgreSQL, multimaster replication is provided by a tool called BDR, which is fully supported (24/7) by Cybertec.

Where PostgreSQL multimaster replication is used

While single-master replication is clearly more desirable if you are looking for high-availability in a single location or if you need more database performance, multimaster replication is an excellent choice for geographically-distributed databases that may only be loosely connected over unreliable network connections.

A typical-use case:

Imagine your company is selling products in Berlin (Germany) and in Seattle (USA) but management wants to see information about all sales in London. This is the ideal use case for PostgreSQL multimaster replication.

PostgreSQL Replication Multimaster

Data will be replicated back and forth to ensure that all data eventually end up in all locations. It is interesting to note that replication can happen in a very selective way. There is no need to replicate entire databases or even entire database instances. You can also replicate a subset of data in cases where not all data are needed in every location.

How multimaster replication works

In PostgreSQL, multimaster replication is built on a feature called “Logical Decoding”. The idea is to become attached to the PostgreSQL transaction log and to transform the changes stored in the transaction log into a more readable format. In cases of multimaster replication, the transaction log is transformed to SQL and is replayed on all other nodes in the cluster.

Handling replication conflicts

If you have decided to use BDR, you should keep in mind that multimaster replication requires sophisticated conflict handling. If the same data are changed in two locations, PostgreSQL has to decide which transaction to keep. Conflict handling is an absolute must in every multimaster system and should be taken into consideration when using this replication technique.

PostgreSQL Replication Connection and API URL

In contrast to single-master setups, multimaster setups can actually roll back transaction AFTER the client has received a COMMIT, and this might come as a surprise to some developers and applications.

Conflicts are an important property of multimaster replication in PostgreSQL. BDR allows you to write your own conflict handlers (here is how it works), and thus provides a great deal of flexibility to developers and administrators alike. Cybertec will help you build your own conflict handlers.

Understanding divergence conflicts

Multimaster replication is a valuable tool for distributing data on the network, however, not all conflicts can be resolved under all circumstances. That is why we wish to caution you: If you are using multimaster replication for the wrong purposes and in a wrong way, data on your nodes can actually diverge. If you are looking for a High-Availability solution, consider checking out our page on Patroni and streaming replication.

More about Patroni

Professional help

Contact us today to receive your personal offer from Cybertec. We offer timely delivery, professional handling, and over 17 years of PostgreSQL experience.

Contact us