liveBook · Manning

Schema inconsistency

As explained in PostgreSQL Mistakes and How to Avoid Them

Schema inconsistency is a condition in which replicated database nodes have different definitions for corresponding database objects.

Schema inconsistency occurs when replicated PostgreSQL nodes define corresponding objects differently. In bidirectional logical replication, a seemingly harmless rename can leave one node unable to apply changes. section 5.5

A column rename breaks replication

Both nodes initially contain support.tickets with an opened_at column. After beta runs ALTER TABLE support.tickets RENAME opened_at TO logged_at, inserts still succeed locally, but replication fails because the target relation is missing replicated column opened_at. The logical replication worker exits with an error.

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Demonstrates a column rename, divergent queries, and the resulting logical replication worker failure.
Examples
Uses support.tickets, an opened_at-to-logged_at rename, divergent queries, and a missing replicated-column log entry.
What it assumes
Assumes familiarity with PostgreSQL tables, primary keys, SQL DDL, and logical replication subscriptions.
What you can do after
Diagnose replication failures by comparing target schemas, local query results, and PostgreSQL logical replication logs.
How it connects
Connects schema inconsistency to logical replication, multi-master replication, primary keys, consistency, availability, and partition tolerance.
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

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.