PostgreSQL logical replication in practice (publish/subscribe, CDC, cross-version zero-downtime major upgrade, v18)

Physical replication (the previous article) was HA of "the whole cluster, same version." But the field has other demands — "stream just this table to another system," "do a major upgrade with no downtime," "distribute changes to downstream as events (CDC)."

What answers these is logical replication. This article explains its mechanism and setup, and its biggest application, the cross-version zero-downtime major upgrade, faithful to the official docs. It also thoroughly crushes the constraint of "what isn't replicated," which directly leads to incidents. It's a deep dive of production-operations guide §7.

Rules for this article: specs, constraints, upgrade procedures, and PostgreSQL 18 changes are based on the PostgreSQL 18 official documentation (as of June 2026). Since logical replication has many constraints and is incident-prone, I treat what isn't replicated especially accurately.

1. Logical vs physical: what's different

The official definition:

Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication.

The difference in a table.

The uses the official docs list include "firing a trigger on the subscriber as each individual change arrives" (= CDC/event-driven), "consolidating multiple DBs into one (for analytics, etc.)", and "replication between different major versions" (= upgrade).

2. Build it with publish / subscribe

Logical replication is a model of publisher (publish) and subscriber (subscribe). The minimal setup:

Publisher side

# postgresql.conf：論理デコードに必要
wal_level = logical

-- 複製したいテーブルの集合を「パブリケーション」として定義
CREATE PUBLICATION app_pub FOR TABLE users, orders;
-- スキーマ単位・全テーブルも可
-- CREATE PUBLICATION all_pub FOR ALL TABLES;
-- CREATE PUBLICATION prod_pub FOR TABLES IN SCHEMA production;

The default of the publish parameter is 'insert, update, delete, truncate' (all operations). You can also stream only inserts with WITH (publish = 'insert').

Subscriber side

-- スキーマは事前に用意しておく（DDLは複製されない。§3）
-- CREATE TABLE users (...); CREATE TABLE orders (...);

CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=publisher dbname=appdb user=replicator sslmode=verify-full'
  PUBLICATION app_pub;
-- 既定で初期データがコピーされ、その後ストリーミングが始まる

Official: a subscription by default copies the initial data (copy_data) and then streams subsequent changes. In PostgreSQL 18, the streaming default of CREATE SUBSCRIPTION changed from off to parallel.

3. Most important: what isn't replicated

Logical-replication incidents almost all come from not knowing "what isn't replicated." Let me accurately grasp the official constraints.

⚠ DDL (schema changes) isn't replicated

The database schema and DDL commands are not replicated. The initial schema can be copied by hand using pg_dump --schema-only. Subsequent schema changes would need to be kept in sync manually.

That is, even if you ALTER TABLE on the publisher, it's not reflected on the subscriber. Moreover, the official docs show an important order:

If the schema changes on the publisher and data arrives that doesn't fit the subscriber's table definition, replication will error out until the schema is updated. In many cases, intermittent errors can be avoided by applying additive schema changes to the subscriber first.

→ Add columns in the order "subscriber first → publisher after."

⚠ Sequences aren't replicated (the switchover landmine)

Sequence data is not replicated. The data in serial / identity columns is replicated as part of the table, but the sequence itself would still have the start value on the subscriber. … If you intend a switchover or failover, you need to update the sequences to the latest values.

This is the pitfall that most stabs people at upgrade switchover (handled in §4).

⚠ UPDATE/DELETE needs a primary key (replica identity)

Logical replication is based on the "replication identity (usually the primary key)." A table without a primary key or an explicit REPLICA IDENTITY can't replicate UPDATE/DELETE (INSERT is OK). An appropriate primary key on all tables — this is a design premise.

Other constraints

• Large objects aren't replicated (no workaround; store them in regular tables).
• Only tables are targets (views, materialized views, and foreign tables error).
• Partitioned tables are by default replicated from the leaf partitions (changeable with publish_via_partition_root).

4. Cross-version zero-downtime major upgrade

Logical replication's biggest practical value is making a major upgrade non-stop. A major upgrade has two paths (official §18.6).

Path A: pg_upgrade (in-place, short stop)

# 旧クラスタを停止し、新バージョンへインプレース変換
pg_upgrade \
  --old-datadir=/var/lib/postgresql/17/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --jobs=4 \        # PG18：チェックを並列化
  --swap            # PG18：ディレクトリ入替で最速（コピー/リンクより速い）

Official: "pg_upgrade can migrate an installation in-place from one major version to another. Especially with --link mode, it completes in minutes." PostgreSQL 18 improvements:

• Statistics carry-over (disable with --no-statistics) — prevents "empty statistics making the planner misbehave" after the upgrade.
• Parallel checks via --jobs — fast for DBs with many objects.
• --swap — the fastest mode that swaps directories (but the old cluster is destructively modified and can't be started afterward).

--swap caveat (official): "Once the file transfer begins, the old cluster is destructively modified and can no longer be started safely." A prior backup is essential.

Path B: switch over with logical replication (a few seconds of stop)

To perfect "nearly non-stop," logically replicate to a new-version instance and switch over once it catches up. Official:

Since logical replication supports replication between different major versions, you can create a standby on the updated version. … Once it catches up to the primary (old version), switch the primary so the standby becomes the new primary, and stop the old instance. This switchover takes a few seconds of downtime.

The gist of the procedure (official §29.13, adapted for practice):

1. 新バージョン(18)で空のインスタンスを構築（wal_level=logical）
2. パブリッシャ(旧17)の現スキーマを pg_dump --schema-only で新18へ適用（DDLは複製されないため）
3. 旧17に CREATE PUBLICATION、新18に CREATE SUBSCRIPTION → 初期コピー＋ストリーミング開始
4. レプリケーション遅延が0に追いつくのを待つ（pg_stat_subscription / lag を監視）
5. 【切替】アプリを一時停止 → 残りの変更が新18へ届くのを確認
6. 【最重要】シーケンスを手で進める（複製されないため）
7. アプリの接続先を新18へ向けて再開 → 旧17を停止

Forget the sequence update of step 6 and, right after switchover, a primary-key collision (duplicate ID) breaks production. Per the official instruction, copy the sequences' current values with pg_dump, or set a value sufficiently larger than the table's maximum with setval.

-- 切替時：各シーケンスをテーブルの最大値より先へ進める（ID衝突を防ぐ）
SELECT setval('users_id_seq', (SELECT max(id) FROM users));
SELECT setval('orders_id_seq', (SELECT max(id) FROM orders));
-- 多数あるなら information_schema から生成して一括実行する

Which to choose: if the acceptable stop is "minutes," pg_upgrade (simple). If the acceptable stop is "a few seconds" and downtime directly affects the business, logical replication (more effort but nearly non-stop). Consider pg_upgrade first, and move to logical replication if its stop time is too long — this is the realistic order of judgment.

5. CDC: distribute changes as events

Logical replication's foundation (logical decoding) can also be used for change data capture (CDC). That's the "fire a trigger on the subscriber as each change arrives" the official docs list as a use. Furthermore, tools like Debezium subscribe to a logical-decoding slot and stream row changes to Kafka, etc., as events.

This achieves "DB change → search-index update / cache invalidation / audit log / microservice integration" without embedding double-writes in the app code. The app just writes to the DB. Downstream receives changes from the WAL — a powerful pattern that can also be an alternative to the Transactional Outbox (for reliability design, also see the Transactional Outbox article).

6. Monitoring

-- サブスクライバ側：適用の遅延と状態
SELECT subname, received_lsn, latest_end_lsn,
       last_msg_receipt_time, latest_end_time
FROM pg_stat_subscription;

-- PG18：競合がログと統計に記録される
SELECT * FROM pg_stat_subscription_stats;   -- 競合カウント（apply_error_count 等）

PostgreSQL 18 logs logical-replication conflicts and visualizes them with new columns of pg_stat_subscription_stats. Logical replication of generated columns (publish_generated_columns) and two-phase commit switching in ALTER SUBSCRIPTION were also added.

7. Summary

• Logical replication = row-level, table-level, cross-version, bidirectional. A different thing from physical (whole-cluster, same-version).
• Build with publish/subscribe (wal_level=logical). Streams after the initial copy.
• Always grasp what isn't replicated: DDL, sequences, large objects, views. UPDATE/DELETE needs a primary key.
• Cross-version non-stop upgrade: pg_upgrade (minutes, fastest with PG18's --swap) or logical replication + switchover (a few seconds). Never forget the sequence update at switchover.
• It can also be used for CDC (Debezium, etc.). Distribute changes as events without app double-writes.

Once "evolve" is settled, head to the technique for doing daily schema changes without stopping — zero-downtime schema migration (lock-safe DDL).

References (PostgreSQL 18 official documentation)

• Chapter 29. Logical Replication
• 29.4. Restrictions (what isn't replicated)
• 29.13. Upgrade (upgrade with logical replication)
• 18.6. Upgrading a PostgreSQL Cluster
• pg_upgrade (--swap / --jobs / statistics carry-over)