Since each server in a multi-master cluster can accept writes, any server can abort a transaction because of a concurrent update — in the same way as it happens on a single server between different backends. To ensure high availability and data consistency on all cluster nodes, multimaster uses logical replication and the three-phase E3PC commit protocol.

When Postgres Pro Enterprise loads the multimaster shared library, multimaster sets up a logical replication producer and consumer for each node, and hooks into the transaction commit pipeline. The typical data replication workflow consists of the following phases:

If a node crashes or gets disconnected from the cluster between the PREPARE and COMMIT phases, the PRECOMMIT phase ensures that the survived nodes have enough information to complete the prepared transaction. The PRECOMMITTED messages help avoid the situation when the crashed node has already committed or aborted the transaction, but has not notified other nodes about the transaction status. In a two-phase commit (2PC), such a transaction would block resources (hold locks) until the recovery of the crashed node. Otherwise, data inconsistencies can appear in the database when the failed node is recovered, for example, if the failed node committed the transaction, but the survived node aborted it.

To complete the transaction, the backend must receive a response from the majority of the nodes. For example, for a cluster of 2N+1 nodes, at least N+1 responses are required. Thus, multimaster ensures that your cluster is available for reads and writes while the majority of the nodes are connected, and no data inconsistencies occur in case of a node or connection failure.

Since multimaster allows writes to each node, it has to wait for responses about transaction acknowledgment from all the other nodes. Without special actions in case of a node failure, each commit would have to wait until the failed node recovery. To deal with such situations, multimaster periodically sends heartbeats to check the node state and the connectivity between nodes. When several heartbeats to the node are lost in a row, this node is kicked out of the cluster to allow writes to the remaining alive nodes. You can configure the heartbeat frequency and the response timeout in the multimaster.heartbeat_send_timeout and multimaster.heartbeat_recv_timeout parameters, respectively.

For example, suppose a five-node multi-master cluster experienced a network failure that split the network into two isolated subnets, with two and three cluster nodes. Based on heartbeats propagation information, multimaster will continue accepting writes at each node in the bigger partition, and deny all writes in the smaller one. Thus, a cluster consisting of 2N+1 nodes can tolerate N node failures and stay alive if any N+1 nodes are alive and connected to each other.

In case of a partial network split when different nodes have different connectivity, multimaster finds a fully connected subset of nodes and disconnects nodes outside of this subset. For example, in a three-node cluster, if node A can access both B and C, but node B cannot access node C, multimaster isolates node C to ensure that both A and B can work.

For clusters with an even number of nodes, you can set up a lightweight referee node that does not hold the data, but acts as a tie-breaker during symmetric node partitioning. For details, see Section F.30.3.3.

For alive nodes, there is no way to distinguish between a failed node that stopped serving requests and a network-partitioned node that can be accessed by database users, but is unreachable for other nodes. To avoid conflicting writes to nodes in different network partitions, multimaster only allows writes to the nodes that see the majority of other nodes. If you try to access a disconnected node, multimaster returns an error message indicating the current status of the node. To prevent stale reads, read-only queries are also forbidden. Thus, if you would like to continue using a disconnected node outside of the cluster in the standalone mode, you have to uninstall the multimaster extension on this node, as explained in Section F.30.4.5.

Each node maintains a data structure that keeps the information about the state of all nodes in relation to this node. You can get this data by calling the mtm.status() and the mtm.nodes() functions.

When a failed node connects back to the cluster, multimaster starts automatic recovery:

Automatic recovery requires presence of all WAL files generated after node failure. If a node is down for a long time and WAL files are no longer available, you may have to exclude this node from the cluster and manually restore it from one of the working nodes using pg_basebackup. For details, see Section F.30.4.3.

Suppose you are setting up a cluster of three nodes, with node1, node2, and node3 host names. After installing Postgres Pro Enterprise on all nodes, you need to initialize data directory on each node, as explained in Section 18.2. If you would like to set up a multi-master cluster for an already existing mydb database, you can load data from mydb to one of the nodes once the cluster is initialized, or you can load data to all new nodes before cluster initialization using any convenient mechanism, such as pg_basebackup or pg_dump.

Once the data directory is set up, complete the following steps on each cluster node:

SELECT mtm.make_table_local('table_name') 

While you can use multimaster in the default configuration, you may want to tune several parameters for faster failure detection or more reliable automatic recovery.

By default, multimaster uses a majority-based algorithm to determine whether the cluster nodes have a quorum: a cluster can only continue working if the majority of its nodes are alive and can access each other. For clusters with an even number of nodes, this approach is not optimal. For example, if a network failure splits the cluster into equal parts, or one of the nodes fails in a two-node cluster, all the nodes stop accepting queries, even though at least half of the cluster nodes are running normally.

To enable a smooth failover for such cases, you can set up a standalone referee node to assign the quorum status to a subset of nodes that constitutes half of the cluster.

A referee is a voting node used to determine which subset of nodes has a quorum if the cluster is split into equal parts. The referee node does not store any cluster data, so it is not resource-intensive and can be configured on virtually any system with Postgres Pro Enterprise installed.

To avoid split-brain problems, you can only have a single referee in your cluster.

To set up a referee for your cluster:

The first subset of nodes that gets connected to the referee wins the voting and starts working. The other nodes have to go through the recovery process to catch up with them and join the cluster. Under heavy load, the recovery can take unpredictably long, so it is recommended to wait for all data nodes going online before switching on the load when setting up a new cluster. Once all the nodes get online, the referee discards the voting result, and all data nodes start operating together.

In case of any failure, the voting mechanism is triggered again. At this time, all nodes appear to be offline for a short period of time to allow the referee to choose a new winner, so you can see the following error message when trying to access the cluster: [multimaster] node is not online: current status is "disabled".

multimaster provides several functions to check the current cluster state.

To check node-specific information, use mtm.status():

SELECT * FROM mtm.status();

To get the list of all nodes in the cluster together with their status, use mtm.nodes():

SELECT * FROM mtm.nodes();

For details on all the returned information, see Section F.30.5.2.

If a cluster node is disabled, any attempt to read or write data on this node raises an error by default. If you need to access the data on a disabled node, you can override this behavior at connection time by setting the application_name parameter to mtm_admin. In this case, you can run read and write queries on this node without multimaster supervision.

With the multimaster extension, you can add or drop cluster nodes without stopping the database service. When adding a new node, you need to load all the data to this node using pg_basebackup from any cluster node, and then start this node.

Suppose we have a working cluster of three nodes, with node1, node2, and node3 host names. To add node4, follow these steps:

To remove the node from the cluster:

If you would like to return the node to the cluster later, you will have to add it as a new node, as explained in Section F.30.4.3.

If you would like to continue using the node that has been removed from the cluster in the standalone mode, you have to drop the multimaster extension on this node and clean up all multimaster-related subscriptions and uncommitted transactions to ensure that the node is no longer associated with the cluster.

Once all these steps are complete, you can start using the node in the standalone mode, if required.

You can check that the data is the same on all cluster nodes using the mtm.check_query(query_text) function.

As a parameter, this function takes the text of a query you would like to run for data comparison. When you call this function, it takes a consistent snapshot of data on each cluster node and runs this query against the captured snapshots. The query results are compared between pairs of nodes. If there are no differences, this function returns true. Otherwise, it reports the first detected difference in a warning and returns false.

To avoid false-positive results, always use the ORDER BY clause in your test query. For example, suppose you would like to check that the data in a my_table is the same on all cluster nodes. Compare the results of the following queries:

postgres=# SELECT mtm.check_query('SELECT * FROM my_table ORDER BY id');
 check_query 
-------------
 t
(1 row)

postgres=# SELECT mtm.check_query('SELECT * FROM my_table');
WARNING: mismatch in column 'b' of row 0: 256 on node0, 255 on node1
 check_query 
-------------
 f
(1 row)

Even though the data is the same, the second query reports an issue because the order of the returned data differs between cluster nodes.

By default, any DDL statement is executed on all cluster nodes, except the following statements that can only act locally on a given node:

The replication mechanism is based on logical decoding and an earlier version of the pglogical extension provided for community by the 2ndQuadrant team.

The three-phase E3PC commit protocol is described in:

Parallel replication and recovery mechanism is similar to the one described in: