skip to content

In PostgreSQL logical replication, which changes never reach the subscriber, and how do you roll out an ALTER TABLE ADD COLUMN without breaking replication?

level: seniorimportance: should knowfreq 52%

answer

  1. rows travel, definitions do not
  2. sequences stay at their start
  3. subscriber first for additions
  4. missing replicated column error

basics

~20 s

Only row changes travel: DDL, sequence state, large objects and views do not. Add a column on the subscriber first and the publisher second, because a subscriber may have extra columns but errors when a replicated column is missing.

solid answer

~40 s

PostgreSQL logical replication decodes row changes (`INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`) of published tables, nothing else. Schema changes are never sent; sequences keep their start value on the subscriber even though the ids arrive inside rows; large objects are not replicated; views, materialized views and foreign tables cannot be published. Because columns match by name and a subscriber table may have extra columns, an additive change goes to the subscriber first and the publisher second. Done the other way round, the first row carrying the new column stops the apply worker with `logical replication target relation "sales.orders" is missing replicated column`, and it retries in a loop while the slot holds WAL. Removals go the other way: publisher first. Before the subscriber ever takes writes, set each sequence past the replicated maximum with `setval`.

code

sql · 9 lines
sql
-- 1. analytics server (subscriber) first
ALTER TABLE sales.orders ADD COLUMN discount_code text;

-- 2. orders server (publisher) second
ALTER TABLE sales.orders ADD COLUMN discount_code text;

-- before the subscriber ever takes inserts into sales.orders
SELECT setval(pg_get_serial_sequence('sales.orders', 'order_id'),
              (SELECT max(order_id) FROM sales.orders));

go deeper

for a junior

Recall that logical replication carries row changes only: table definitions and sequence positions have to be handled separately.

for a middle

Explain why columns are matched by name, why extra subscriber columns are fine, and why a missing one stops the apply worker.

for a senior

Show a deploy order that never breaks replication: additions subscriber-first, removals publisher-first, new tables with REFRESH PUBLICATION. Align sequences before the subscriber takes writes.

for a principal

Weigh the operational tax of manual schema sync against the freedom it gives the analytics side, and decide who owns the two-server migration process.

## What travels and what does not PostgreSQL logical replication decodes **row changes** from the WAL: the effects of `INSERT`, `UPDATE`, `DELETE` and `TRUNCATE` on published tables. Anything that is not a row change in a published table stays behind. | Not replicated | Effect on the analytics server | What to do | |---|---|---| | DDL and the schema itself | tables, columns, indexes and constraints are never created or altered | apply schema changes on both sides yourself | | Sequence state | ids arrive inside rows, but the sequence objects stay at their start value | set them before the subscriber ever takes writes | | Large objects | not sent at all | keep such data in ordinary `bytea` columns | | Views, materialized views, foreign tables | cannot be added to a publication | publish the base tables and build views on the subscriber | Two more behaviours catch teams out: - `TRUNCATE` **is** replicated by default. If a truncated table on the subscriber is referenced by a foreign key from a table outside the subscription, applying the truncate fails. - The apply worker runs with `session_replication_role = replica`, so ordinary triggers on the subscriber's tables **do not fire**. A trigger meant to maintain an analytics summary must be marked `ENABLE REPLICA TRIGGER` or `ENABLE ALWAYS TRIGGER`. For triggers enabled to fire there, apply fires only row triggers; the initial copy, implemented like `COPY`, fires both row and statement `INSERT` triggers. Not replicating DDL is also a feature here: the analytics server can carry its own indexes, extra columns and summary tables without the publisher knowing. ## Why a schema change can stop replication The subscriber matches columns by name. Suppose `discount_code` is added to `sales.orders` on the publisher first. The next change to that table is decoded with the new column, and the apply worker on the analytics server fails with: `ERROR: logical replication target relation "sales.orders" is missing replicated column: "discount_code"` The worker exits, the launcher restarts it after `wal_retrieve_retry_interval` (default 5 s), and it fails on the same change again. Every table in that subscription stops, and the publisher's slot keeps WAL until the subscriber's table is fixed; how far that retention may grow is a slot-management question. Spotting the stall is straightforward if you look: - the subscriber's log repeats the same `ERROR` every few seconds; - `pg_stat_subscription_stats.apply_error_count` keeps climbing; - `pg_stat_subscription` shows no row for the subscription's apply worker between restarts, because a crashed or disabled subscription has none; - on the publisher, the subscription's slot stops confirming progress. ## A safe rollout order 1. **Additive changes, subscriber first.** `ADD COLUMN` on the analytics server, then on the orders server. Until the publisher has the column, the subscriber fills it from its default. 2. **Removals, publisher first.** Drop the column on the publisher, then on the subscriber. Watch extra subscriber columns that are `NOT NULL` without a default: inserted rows would violate them. 3. **Type changes:** values travel in text form, so widening the subscriber first (for example `integer` to `bigint`) keeps working. 4. **New tables:** create the table on the subscriber, `ALTER PUBLICATION ... ADD TABLE` on the publisher, then `ALTER SUBSCRIPTION ... REFRESH PUBLICATION`. A `FOR ALL TABLES` publication includes new tables automatically, but the subscriber still needs the table and the refresh. Migration tooling usually runs against one database; for a replicated table the deploy has to name both servers and their order. ## Sequences before the subscriber takes writes For a read-only analytics copy, stale sequences do no harm. They matter once the subscriber must accept inserts into replicated tables, as in a switchover or migration. Each sequence must then be set at or above the replicated maximum: - `setval` with `pg_get_serial_sequence`, which works for identity and `serial` columns, and `max()` of the column; - or copy the publisher's values from `pg_sequences.last_value`, adding a safety margin for inserts that were still arriving. The full cutover procedure for an upgrade through logical replication belongs to major-version upgrades.

  • Why do the order_id values arrive correctly if sequences are not replicated?
    The publisher's sequence generated each id when the row was inserted, and the id is just a column value in the decoded row, so it is replicated like any other column. What stays behind is the sequence object on the subscriber, which still shows its start value; its next `nextval` would collide with ids already present.
  • A trigger on the analytics server's sales.orders table never fires for replicated rows. Why, and how do you fix it?
    The apply worker runs with `session_replication_role = replica`, and triggers in the default enabled state fire only when the role is `origin` (the default) or `local`. Mark the trigger with `ALTER TABLE sales.orders ENABLE REPLICA TRIGGER` (or `ENABLE ALWAYS TRIGGER` to fire in both modes). Only row triggers fire during apply, so a statement-level trigger still will not.

  • ALTER TABLE on the publisher is replicated to the subscriber like any change.
  • Identity values arrive, so the subscriber's sequences are up to date too.
  • Adding the column on the publisher first is safe; the subscriber ignores unknown columns.
  • TRUNCATE is not replicated, so truncating a published table is harmless.
  • Triggers on subscriber tables fire for replicated rows exactly as for local writes.