Learning center

A column added during logical replication stops the subscriber

While a table is being moved with logical replication, the old and new databases are two schemas that have to stay compatible. Logical replication does not carry ALTER TABLE. When the old database gained a column and received a row that used it, the subscriber stopped applying changes, retried every few seconds, and the publisher kept its WAL. Add the column on the subscriber first.

The failure#

orders replicates from the old database to the new one, both PostgreSQL 16.15, as in set sequences before cutting over to a logically replicated database. A release still writing to the old database ships a migration:

-- old database (publisher)
ALTER TABLE orders ADD COLUMN state text;
INSERT INTO orders (status, state) VALUES ('paid', 'PAID');
INSERT INTO orders (status) VALUES ('paid');

The new database's log:

ERROR:  logical replication target relation "public.orders" is missing replicated column: "state"

The subscriber's table still had 1,005 rows. The apply worker restarted and failed again: within about 20 seconds pg_stat_subscription_stats.apply_error_count was 5. Every later change to any table in the subscription waited behind the failing one. We inserted 1,000 more orders on the old database, and the publisher's slot held 139 kB of WAL that the subscriber had not confirmed:

SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))
FROM pg_replication_slots;

That figure grows with all write traffic on the old database, not only on orders. A slot that stays behind for a weekend can fill the old database's disk.

Recovery#

Add the same column on the subscriber:

-- new database
ALTER TABLE orders ADD COLUMN state text;

Within 8 seconds the worker applied the backlog: 2,007 rows on both sides, one with a state, and the slot was 0 bytes behind. Nothing was lost, because the publisher kept the changes in its slot. The logical replication restrictions state that schema and DDL changes are not replicated and that the subscriber's tables must have the columns the publisher sends.

The order for schema changes during a move#

Both databases need every migration while the move is in progress:

  1. Additive changes on the subscriber first. A new column, or a new table added to the publication, goes to the new database before the old one. A subscriber can have columns the publisher does not send.
  2. Removals on the publisher first. Drop a column on the old database before the new one, so the subscriber never receives a column it no longer has.
  3. Type changes need both at once, or a new column and a switch, because each side must accept what the other sends.

The easiest rule during a move is to freeze schema changes on the moved tables until the cutover. If the deploy pipeline runs migrations against one database, it does not know the second one exists.

Watch for it#

Alert on apply_error_count rising in pg_stat_subscription_stats, and on the publisher's slot falling behind:

SELECT subname, apply_error_count, sync_error_count FROM pg_stat_subscription_stats;

Pass the rehearsal when you run each pending migration in the order above against both a publisher and a subscriber in staging, insert a row that uses the change, and both sides show the same row within seconds. The other steps of the move are in moving data while two application releases share one database.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading