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

> A practical guide to using PostgreSQL logical replication in production. It explains the difference from physical replication, building publish/subscribe, CDC (change data capture), and the cross-version zero-downtime major upgrade (a few seconds of downtime to 18). The most important constraint that DDL and sequences aren't replicated and the switchover pitfalls, pg_upgrade's --swap, and PostgreSQL 18 improvements — all faithful to the official docs.

- Published: 2026-06-22
- Author: 友田 陽大
- Tags: PostgreSQL, アーキテクチャ設計
- URL: https://tomodahinata.com/en/blog/postgresql-logical-replication-cdc-zero-downtime-upgrade-guide
- Category: PostgreSQL operations & reliability
- Pillar guide: https://tomodahinata.com/en/blog/postgresql-production-operations-guide

## Key points

- Logical replication is 'row-level replication based on a replication identity (usually the primary key).' Unlike physical (byte-level, whole-cluster, same-version), it can do table-level, cross-version, and bidirectional.
- The publish/subscribe model: CREATE PUBLICATION on the publisher, CREATE SUBSCRIPTION on the subscriber, wal_level=logical. After the initial copy, it streams.
- The most important constraints: DDL (schema changes) isn't replicated / sequences aren't replicated / UPDATE/DELETE needs a primary key (replica identity) / large objects and views aren't supported. You must manually advance sequences at switchover.
- Cross-version zero-downtime upgrade: logically replicate to a new-version instance and switch over once it catches up = officially 'a few seconds of downtime.' pg_upgrade (in-place) is fastest with PG18's --swap.
- PostgreSQL 18: logical replication of generated columns, the streaming default of CREATE SUBSCRIPTION becomes parallel, and conflict logging. pg_upgrade adds statistics carry-over, parallel checks (--jobs), and --swap.

---

Physical replication ([the previous article](/blog/postgresql-streaming-replication-high-availability-failover-guide)) 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](/blog/postgresql-production-operations-guide).

> **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.

| | Physical replication | Logical replication |
| --- | --- | --- |
| Unit | Whole cluster (byte-level) | **Table-level** (row changes) |
| Version | Same major version required | **Cross-version possible** (→ usable for upgrades) |
| Platform | Same | **Cross-platform possible** (Linux→Windows, etc.) |
| Direction | One-way | **Bidirectional, cascading possible** |
| Use | HA/DR, read replicas | Selective replication, CDC, cross-version migration, DB consolidation |

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

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

```sql
-- 複製したいテーブルの集合を「パブリケーション」として定義
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

```sql
-- スキーマは事前に用意しておく（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)

```bash
# 旧クラスタを停止し、新バージョンへインプレース変換
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):

```text
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`.

```sql
-- 切替時：各シーケンスをテーブルの最大値より先へ進める（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](/blog/transactional-outbox-pattern-reliable-event-publishing-guide)).

---

## 6. Monitoring

```sql
-- サブスクライバ側：適用の遅延と状態
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)](/blog/postgresql-zero-downtime-schema-migration-lock-safe-ddl-guide).

---

### References (PostgreSQL 18 official documentation)

- [Chapter 29. Logical Replication](https://www.postgresql.org/docs/18/logical-replication.html)
- [29.4. Restrictions (what isn't replicated)](https://www.postgresql.org/docs/18/logical-replication-restrictions.html)
- [29.13. Upgrade (upgrade with logical replication)](https://www.postgresql.org/docs/18/logical-replication-upgrade.html)
- [18.6. Upgrading a PostgreSQL Cluster](https://www.postgresql.org/docs/18/upgrading.html)
- [pg_upgrade (--swap / --jobs / statistics carry-over)](https://www.postgresql.org/docs/18/pgupgrade.html)