Menu
Can someone guide me to do multi-master replication with PostgreSQL.
I have done master-slave replication but how do I get master-master replication.
I have tried to use pg-pool-II but I couldn't find any good documentation for it.
Let me know what can I do to achieve master-master replication in PostgreSQL. thanks.
548
PostgreSQL has built-in single-master replication, but unfortunately, there is no multiple-master replication in mainline PostgreSQL. There are some Multimaster replication solutions available, some of these are in the form applications and some are PostgreSQL forks.
Multi-Master Replication is where data replication is done in such a way that data is replicated to a group of computers known as master systems and anyone can update database and update done by anyone is replicated to other systems as well. Here all members deal with client equally as writes can be done by any system.
In PostgreSQL, there are two types of replication features: streaming replication (physical replication) that collectively replicates a database cluster, and logical replication that replicates in units of tables and databases.
There is one popular product commercially available at the moment that supports multi-master replication in PostgreSQL. It is called Bidirectional replication created by 2ndQuadrant. The earlier versions of BDR were open-source, but not the latest ones.
You don't need multi-master for high-availability. @a_horse_with_no_name If my master server fails I want my replicated master to finish the job since I cannot grant the standby server that privilege. There is no free (or open source) master-master replication solution for current Postgres versions.
Replication is the crucial pillar of horizontal scalability, and PostgreSQL supports unidirectional master-slave replication, which is enough for many use-cases. Database replication replicates the data on other servers and stores it on more than one node.
When the primary server fails, it is not necessary to wait for a physical standby to be promoted. It is recommended that PostgreSQL users not complicate the design by going for multi-master replication where single-master replication is enough. This is because multi-master replication makes the system complicated and messy.
It's very difficult to make real multi-master replication on PostgreSQL. There was no good solutions. May be you don't really need master-master replication and there is a room for vertical scaling (better disk system, more CPUs, more memory).
But if you want to do this I'd recommend to have a look at PostgreSQL-XC, looks like it's stable now.
168
As far as I know you need a third party solution like Bucardo to perform a multi master replication in PostgreSQL.
Take a look at this tutorial for an example of configuring Bucardo and PostgreSQL for a multi master replication.
11
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
