PostgreSQL Multi master using Bucardo

• Bucardo is an asynchronous PostgreSQL replication system, allowing for both multi-master and multi-slave operations.
• Bucardo is very good at replicating data among Postgres databases (as well as replicating to other things, such as MariaDB, Oracle, and Redis!).
• It was developed at Backcountry.com by Jon Jensen and Greg Sabino Mullane of End Point Corporation, and is now in use at many other organizations.
• Bucardo is free and open source software released under the BSD license.
• Bucardo requires a dedicated database and runs as a Perl daemon that communicates with this database and all other databases involved in the replication. All the specific information that the daemon needs is stored in the main bucardo database, including a list of all the databases involved in the replication and how to reach them, all the tables that are to be replicated, and how each is to be replicated.
• Multi-master replication uses two or more databases, with conflict resolution (either standard choices or custom subroutines) to handle the same update on both sides.
• Master-slave replication involves one or more sources going to one or more targets. The source must be PostgreSQL, but the targets can be PostgreSQL, MySQL, Redis, Oracle, MariaDB, SQLite, or MongoDB.

Bucardo Workflow

The first step in running Bucardo is to add two or more databases to the main bucardo database. Once this is done, information on which tables are to be replicated are added, as well as any groupings of tables. Then the syncs are added. Syncs are named replication actions, copying a specific set of tables from one server to another server or group of servers.

Once Bucardo has been set up, triggers begin storing information about which rows were changed in all the tables of interest. For a swap sync (multi-master), the process goes like this

• A change is made to the table and gets recorded in the bucardo_delta table.
• A notice is sent to the main Bucardo daemon, letting it know that the table has changed.
• The daemon notifies the controller for that sync and returns to listening.
• The controller creates a “kid” to handle the replication, or signals an existing one.
• The kid starts a new transaction and disables triggers and rules on the tables in question.
• It then gathers a list of which rows have changed since the last replication, and then compares the two to figure out what should be done.
• If there is a conflict, then either the standard conflict handler, or a custom one, set per table, is run to sort things out.
• Triggers and rules are re-enabled, and the transaction commits.
• If the transaction fails, then any custom exception handlers are run.
• The child signals to the controller that it has finished.

Reference : https://www.waytoeasylearn.com/category/bucardo/