A shared-nothing architecture gives each PostgreSQL node independent storage and processing resources. Redundancy and high availability therefore depend on replication connections between separate database instances rather than directly shared state. section 5.5
The architecture makes distributed tradeoffs and operational hazards explicit:
- CAP theorem and PACELC describe unavoidable choices involving consistency, availability, latency, and network partitions. section 5.5
- Bidirectional logical replication can create feedback loops when nodes replicate transactions that originated elsewhere, eventually filling disks. section 5.5
- Independent schemas can diverge: renaming a replicated column causes the target replication worker to fail. section 5.5
The tempting multi-master setup
Two PostgreSQL instances can subscribe to one another with logical replication, but concurrent identity values may collide, producing duplicate-key errors; schema changes can separately stop replication. 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)
Inspect the publication and ticket-table setup used to establish the replication scenario. section 5.5
