liveBook · Manning

DDL replication

As explained in PostgreSQL Mistakes and How to Avoid Them

DDL replication is the propagation of database structure changes, such as table or column alterations, between database nodes.

DDL replication propagates database structure changes, but inconsistent schemas can break logical replication. Renaming a column on one node leaves the other expecting the old replicated column. section 5.5

A column rename breaks replication

On beta, run `ALTER TABLE support.tickets RENAME opened_at TO logged_at;`. An insert on alpha succeeds, but beta reports that `support.tickets` is missing replicated column `opened_at`; the logical replication worker exits and restarts. section 5.5

ALTER TABLE support.tickets RENAME opened_at TO logged_at;
ALTER TABLE

Inspect the schema-changing statement that creates the mismatch. section 5.5

Safe schema changes require carefully managed orchestration across nodes, balancing conflict avoidance and replication availability. Consistent DDL replication remains a major obstacle to homemade multi-master PostgreSQL clusters. section 5.5

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Demonstrates a column rename, resulting insert failure, log diagnosis, and the need for coordinated orchestration.
Examples
Uses a PostgreSQL column rename, an insert on another node, and a replication-worker error log.
What it assumes
Assumes PostgreSQL logical-replication and multi-master fundamentals while illustrating the failure through executable SQL.
What you can do after
Diagnose replication failure caused by schema drift and recognize when coordinated schema-change orchestration is required.
How it connects
Connects DDL replication with logical replication, schema consistency, multi-master replication, replication conflicts, and uptime.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.