PostgreSQL replication copies WAL changes between independent database instances, supporting redundancy and high availability. Logical replication is useful, but turning two unidirectional subscriptions into homemade multi-master replication creates distributed-systems tradeoffs and operational hazards. section 5.5
Common failure modes include:
- Allowing replicated transactions to return to their origin causes changes to ping-pong across nodes. section 5.5
- Using identity columns independently can produce duplicate primary keys when both nodes insert rows. section 5.5
- Renaming a replicated column on one node breaks logical replication when the publisher still sends the original column. section 5.5
A tempting two-way setup
Create publications on alpha and beta, then create subscriptions with origin=NONE. Initial data may copy successfully, but later inserts can fail with duplicate-key errors, while incompatible schema changes stop the logical replication worker. 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, table, and sample data used to begin a logical-replication scenario. section 5.5
