The multimaster extension takes care of the database replication in a fully automated way. You can perform write transactions on any node and
work with temporary tables on each cluster node simultaneously. However, make sure to take the following replication restrictions into account:
- Microsoft Windows operating system is not supported.
- 1C solutions are not supported
- multimaster can replicate only one database in a cluster. If it is required to replicate the contents of several databases, you can either transfer all data into different schemas within a single database or create a separate cluster for each database and set up multimaster for each cluster.
- Large objects are not supported. Although creating large objects is allowed, multimaster cannot replicate such objects, and their OIDs may conflict on different nodes, so their use is not recommended.
- Since multimaster is based on logical replication and Paxos over three-phase commit protocol, its operation is highly affected by network latency. It is not recommended to set up a multimaster cluster with geographically distributed nodes.
- Using tables without primary keys can have negative impact on performance. In some cases, it can even lead to inability to restore a cluster node, so you should avoid replicating such tables with multimaster.
- Unlike in vanilla PostgreSQL, read committed isolation level can cause serialization failures on a multi-master cluster (with an SQLSTATE code '40001') if there are conflicting transactions from different nodes, so the application must be ready to retry transactions. Serializable isolation level works only with respect to local transactions on the current node.
- Sequence generation. To avoid conflicts between unique identifiers on different nodes, multimaster modifies the default behavior of sequence generators. By default, ID generation on each node is started with this node number and is incremented by the number of nodes. For example, in a three-node cluster, 1, 4, and 7 IDs are allocated to the objects written onto the first node, while 2, 5, and 8 IDs are reserved for the second node. If you change the number of nodes in the cluster, the incrementation interval for new IDs is adjusted accordingly. Thus, the generated sequence values are not monotonic. If it is critical to get a monotonically increasing sequence cluster-wide, you can set the multimaster.monotonic_sequences to true.
- Commit latency. In the current implementation of logical replication, multimaster sends data to subscriber nodes only after the local commit, so you have to wait for transaction processing twice: first on the local node, and then on all the other nodes simultaneously. In the case of a heavy-write transaction, this may result in a noticeable delay.
- Logical replication does not guarantee that a system object OID is the same on all cluster nodes, so OIDs for the same object may differ between multimaster cluster nodes. If your driver or application relies on OIDs, make sure that their use is restricted to connections to one and the same node to avoid errors. For example, the Npgsql driver may not work correctly with multimaster if the NpgsqlConnection.GlobalTypeMapper method tries using OIDs in connections to different cluster nodes.
- Replicated non-conflicting transactions are applied on the receiving nodes in parallel, so such transactions may become visible on different nodes in different order.
CREATE INDEX CONCURRENTLY
and REINDEX CONCURRENTLY
are not supported.COMMIT AND CHAIN
feature is not supported.