A multi-master system lets multiple independent database nodes accept writes and exchange changes. In PostgreSQL, the practical shape is replication connections among separate instances: a shared-nothing design intended for redundancy and high availability. That apparent simplicity hides distributed-systems tradeoffs: under a network partition, availability and consistency cannot both be retained; even without a partition, latency is traded against consistency. section 5.5
A tempting homemade design creates unidirectional logical replication in both directions. It is not merely “two subscriptions”: publications, subscriptions, replication origins, initial data, write conflicts, generated identifiers, and schema changes must all remain compatible. For production use, bidirectional replication carries a long list of prerequisites and caveats; an established multi-master solution is safer than assembling one ad hoc. section 5.5 section 5.7
The support-ticket loop
Consider alpha and beta, each with a support.tickets table whose integer primary key is generated as an identity. After alpha publishes its tables and beta subscribes with origin filtering disabled, beta can receive alpha’s rows. Reversing the connection makes both nodes writable, but a local insert on beta may generate id 1 even though replicated data already uses it, producing a duplicate-key error. Identity allocation is therefore a coordination problem, not a replication detail. section 5.5 section 5.5
CREATE PUBLICATION multi_alpha FOR ALL TABLES;
CREATE PUBLICATION
CREATE TABLE support.tickets (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
content text,
status smallint,
opened_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
closed_at timestamptz
);
CREATE TABLE
INSERT INTO support.tickets (content) VALUES ('first'), ('second');
INSERT 0 2
SELECT id, content FROM support.tickets;
id | content
----+---------
1 | first
2 | second
(2 rows)
This ticket table exposes the central conflict surface: independently generated primary-key values can collide once both nodes accept writes. section 5.5
Common failure modes make the design operationally fragile:
- Replicating transactions back to their origin creates a ping-pong feedback loop. Without filtering by replication origin, extraneous WAL accumulates until disks can fill. section 5.5
- Independent writes can violate a unique constraint when nodes generate overlapping identity values. section 5.5
- Changing a replicated table on one node can stop apply on another. Renaming opened_at to logged_at leaves the target missing the replicated column, and the logical replication worker exits. section 5.5
- Distributed serialization anomalies and the CAP and PACELC tradeoffs remain design constraints, rather than problems solved by adding replication links. section 5.7
Treat multi-master replication as a narrowly justified distributed-system design, not a shortcut to write-anywhere PostgreSQL. Before adopting it, account for conflict behavior, replication-loop prevention, schema coordination, and the availability, consistency, and latency tradeoffs. Where the requirement is real, prefer a maintained multi-master solution whose design addresses those concerns. section 5.5 section 5.7
