0Pricing
PostgreSQL Performance & Query Optimization · 课时

近乎零停机的主版本升级

使用逻辑复制跨版本升级，同时保持应用在线。

近乎零停机的主版本升级 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Why pg_upgrade Is Not Enough

A major PostgreSQL version jump (e.g. 14 to 17) changes the on-disk catalog format, so the old data directory can't be opened by the new binary directly.

  • pg_dump/pg_restore works but locks you out for the full dump+load window — hours on a large database.
  • pg_upgrade --link is fast but still requires the application to be fully stopped during the swap, and it operates in place.

Logical replication breaks this trade-off: it streams row-level changes from the old cluster into a freshly initialized new-version cluster while the application keeps writing. Downtime shrinks to a single, planned cutover of a few seconds.

The Core Idea: Two Clusters, One Stream

The upgrade runs as a publisher / subscriber pair across versions:

  • Publisher = your current production cluster (old version).
  • Subscriber = a new cluster running the target major version, initialized empty.

Logical replication is version-independent: it ships decoded logical changes (INSERT/UPDATE/DELETE), not physical WAL pages, so a v14 publisher can feed a v17 subscriber. Once the subscriber is fully caught up and lag is near zero, you flip the application's connection string to the new cluster.

Preparing the Publisher

Logical decoding must be enabled on the old cluster before you start. Set wal_level = logical (requires a restart — plan it during an earlier maintenance window) and give yourself enough replication headroom.

Verify the running configuration with a quick query so you don't discover the wrong setting mid-cutover.

-- postgresql.conf on the OLD (publisher) cluster
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10

-- Confirm after restart:
SHOW wal_level;
SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level', 'max_replication_slots', 'max_wal_senders');

Creating the Publication

A publication defines which tables are streamed. For a full-database upgrade you publish all tables.

  • FOR ALL TABLES captures every existing and future table automatically.
  • Tables included in a publication must have a replica identity so UPDATE/DELETE can be replayed — a primary key is the default and best choice.

Tables that lack a primary key need REPLICA IDENTITY FULL or a unique index, otherwise UPDATEs/DELETEs on them will fail to replicate.

-- On the OLD cluster
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- Find tables with no usable replica identity BEFORE you start
SELECT n.nspname AS schema, c.relname AS table
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relreplident = 'd'          -- default (uses PK)
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.oid AND i.indisprimary
  )
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

Seeding the New Cluster's Schema

Logical replication copies data, never DDL. You must create the schema on the new subscriber yourself before subscribing.

  • Dump the schema only with pg_dump --schema-only from the old cluster and restore it on the new one.
  • A key optimization: load the schema without indexes and foreign keys first, let the initial data copy run fast, then add indexes afterward. This dramatically speeds the bulk copy.
# Run from a shell with access to both clusters
pg_dump --schema-only --no-owner \
  -h old-host -U postgres appdb > schema.sql

# Restore schema into the NEW v17 cluster
psql -h new-host -U postgres -d appdb -f schema.sql

Creating the Subscription

On the new cluster, a subscription connects to the publisher, performs an initial data copy, then streams ongoing changes.

  • copy_data = true (the default) triggers the initial snapshot of existing rows.
  • The subscription automatically creates a replication slot on the publisher to track its position.

For large databases, raise max_sync_workers_per_subscription so multiple tables copy in parallel.

-- On the NEW (subscriber) cluster
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-host port=5432 dbname=appdb user=repl password=secret'
  PUBLICATION upgrade_pub
  WITH (copy_data = true, streaming = on);

Monitoring Initial Sync and Lag

Don't guess when it's safe to cut over — measure it. Two signals matter:

  • Per-table sync state: every table must reach the r (ready) state in pg_subscription_rel.
  • Replication lag: the byte distance between the publisher's current WAL position and what the subscriber has confirmed.

Watch lag from the publisher via pg_replication_slots / pg_stat_replication.

-- On the SUBSCRIBER: are all tables done copying?
SELECT srsubid, srrelid::regclass AS table, srsubstate
FROM pg_subscription_rel
WHERE srsubstate <> 'r';   -- empty result = all ready

-- On the PUBLISHER: how far behind is the subscriber?
SELECT slot_name,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

The Sequence Trap

This is the single most common way to corrupt data during a logical-replication upgrade: sequences are NOT replicated.

The schema dump recreates each sequence at its starting value, not its current value. If you cut over without fixing them, the new cluster will hand out IDs that already exist on the old one, causing duplicate-key violations and silent data collisions.

During cutover (after writes stop on the old cluster), advance every sequence on the new cluster past its old value. pg_dump --section=pre-data won't save you here — handle sequences explicitly.

-- Run on OLD cluster to generate setval statements,
-- then execute the output on the NEW cluster during cutover.
SELECT format(
  'SELECT setval(%L, %s, true);',
  schemaname || '.' || sequencename,
  COALESCE(last_value, 1)
)
FROM pg_sequences;

Rebuilding Indexes and Validating

If you deferred indexes/constraints to speed the copy (scene 5), now is the time to add them back — ideally while replication is still streaming, so they're ready before cutover.

  • Create indexes on the subscriber; ongoing changes keep flowing.
  • Re-add foreign keys and run ANALYZE so the new cluster's planner has fresh statistics — otherwise your first production queries hit bad plans.

Run a row-count and checksum comparison on a few critical tables to confirm fidelity before trusting the cutover.

-- On the NEW cluster, after the bulk copy is done
CREATE INDEX CONCURRENTLY idx_orders_customer
  ON orders (customer_id);

-- Refresh planner statistics for the new major version
ANALYZE;

-- Spot-check fidelity on a hot table
SELECT count(*), sum(hashtext(t::text)::bigint) AS checksum
FROM orders t;

The Cutover Sequence

Cutover is a tight, ordered checklist. The goal is to make sure no write is lost and no sequence collides.

  • Stop application writes to the old cluster (drain connections or set the old DB to default_transaction_read_only = on).
  • Wait until subscriber lag reaches zero — the new cluster has every committed change.
  • Run the setval statements to fix sequences.
  • Point the application's connection string at the new cluster and resume writes.

Done correctly, user-visible downtime is just the few seconds it takes to flip the connection string.

-- On the OLD cluster: stop new writes cleanly
ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

-- On the PUBLISHER: confirm zero outstanding lag
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';   -- proceed only when 0

Tearing Down Cleanly

After the application is live on the new cluster and you've verified writes are landing, decommission the replication plumbing so it doesn't leak resources.

  • Drop the subscription on the new cluster — this also removes the remote replication slot when reachable.
  • Drop the publication on the old cluster.
  • If a slot lingers (e.g. publisher was unreachable), drop it manually — an orphaned logical slot pins WAL forever and will eventually fill the old disk.

Keep the old cluster read-only and intact for a rollback window before retiring it.

-- On the NEW cluster
DROP SUBSCRIPTION upgrade_sub;

-- On the OLD cluster
DROP PUBLICATION upgrade_pub;

-- If a slot was left behind on the OLD cluster:
SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE slot_type = 'logical' AND active = false;

Quick Check

You're cutting over a v14 to v17 upgrade via logical replication. The bulk copy finished and lag is zero. What MUST you do during cutover that logical replication will not handle for you?

Recap: The Near-Zero-Downtime Playbook

Logical replication turns a multi-hour major upgrade into a seconds-long cutover. The disciplined sequence:

  • Prepare the old cluster: wal_level = logical, slots, and verify replica identity on every table.
  • Build a new-version cluster, restore schema-only (defer indexes/FKs for a fast copy).
  • Replicate: CREATE PUBLICATION on old, CREATE SUBSCRIPTION on new, monitor pg_subscription_rel and slot lag until ready.
  • Reconcile: rebuild indexes, re-add FKs, ANALYZE, and never forget to setval() the sequences.
  • Cut over: make old read-only, wait for zero lag, fix sequences, repoint the app.
  • Clean up: drop subscription/publication and any orphaned slots; keep the old cluster as a rollback safety net.

The sequence trap and an orphaned slot pinning WAL are the two mistakes that bite teams most — guard against both explicitly.

常见问题解答

「近乎零停机的主版本升级」课时是免费的吗？

是的 — 「近乎零停机的主版本升级」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「近乎零停机的主版本升级」这节课中我会学到什么？

使用逻辑复制跨版本升级，同时保持应用在线。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

「近乎零停机的主版本升级」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 发布、订阅与副本标识
  2. 卸载读取与分析工作负载
  3. 近乎零停机的主版本升级
  4. 监控复制延迟与复制槽膨胀
← 返回 PostgreSQL Performance & Query Optimization