skip to content

Moving a 3 TB PostgreSQL 14 e-learning database to 18 through logical replication with seconds of downtime, what is the cutover sequence and what does replication not carry?

level: seniorimportance: should knowfreq 40%

answer

  1. wal_level needs a restart on 14
  2. schema and roles from 18's tools
  3. freeze DDL until cutover
  4. sequences do not travel
  5. confirmed LSN, then repoint

basics

~20 s

Load roles and schema into 18, subscribe it to 14 and freeze DDL; at cutover stop writes, wait until the final LSN is confirmed, setval the sequences and repoint clients. DDL, sequence values, large objects and materialized views are not replicated.

solid answer

~50 s

Weeks ahead, set `wal_level = logical` on the 14 primary (a restart) and make sure every published table has a primary key or replica identity. Build the 18 cluster with a fresh `initdb`, load roles with 18's `pg_dumpall --globals-only` and the schema with `pg_dump --schema-only`, then publish on 14 and subscribe from 18 (setup belongs to the logical-replication topic). The initial copy of 3 TB takes a long time while the slot holds WAL on 14. Freeze DDL from then on, because schema changes are not replicated. At cutover: stop writers, note `pg_current_wal_lsn()` on 14 and wait until the slot's `confirmed_flush_lsn` reaches it, copy sequence values with `setval` (18 does not replicate sequences), refresh materialized views, move any large objects separately, analyze, repoint the applications and drop the subscription. The outage is the write freeze plus that short checklist.

code

sql · 13 lines
sql
-- on the 14 publisher, after the writers are stopped
SELECT pg_current_wal_lsn();

SELECT slot_name, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name = 'elearning_to_18';

-- still on 14: generate setval calls to run on the 18 subscriber
SELECT format('SELECT setval(%L, %s, true);',
              quote_ident(schemaname) || '.' || quote_ident(sequencename),
              last_value)
FROM pg_sequences
WHERE last_value IS NOT NULL;

go deeper

for a junior

Know that logical replication works between different PostgreSQL majors, which is why it can upgrade with only a brief switch-over.

for a middle

Explain the setup order: wal_level on the publisher, schema on the subscriber first, then the initial copy and catch-up.

for a senior

Run the cutover as a checklist: write freeze, LSN confirmation, sequences, materialized views, large objects and repointing, with a DDL freeze for the whole window.

for a principal

Judge when seconds of downtime justify weeks of dual-running effort and WAL risk, against a minutes-long pg_upgrade --link outage.

## Why this route Logical replication ships **row changes** decoded from WAL, and it works between **different major versions**. That makes it the one route where the old 14 cluster keeps serving traffic while the 18 cluster is built and filled; the manual describes the final switch as costing only several seconds of downtime. The price is that you, not the tool, carry everything logical replication does not. ## Preparing the 14 publisher - `wal_level = logical`: the default is `replica`, and the setting takes effect only at server start, so schedule this restart early. - Enough `max_replication_slots` and `max_wal_senders` for the subscription and its initial-sync workers; both also need a restart to change. - Every published table needs a primary key or another replica identity so UPDATE and DELETE can be applied; finding tables without one is the logical-replication topic's business, but the audit belongs in the upgrade plan. - Disk headroom in `pg_wal`, because the slot keeps WAL until the subscriber confirms it. ## Building the 18 subscriber 1. Run `initdb` for 18. This route rewrites every row, so 18's default of data checksums on can simply stay. 2. Load roles and tablespaces with 18's `pg_dumpall --globals-only` against the old server. 3. Load the schema with 18's `pg_dump --schema-only` (functions, views, types, indexes and constraints all come with it). 4. Create the publication on 14 and the subscription on 18; the subscription's initial copy loads every table, then streams changes. 5. Watch progress: on 14, `pg_replication_slots` shows the slot's `confirmed_flush_lsn` and WAL retention; on 18, `pg_stat_subscription` shows the apply workers. For 3 TB the initial copy can take many hours, and the WAL generated meanwhile piles up behind the slot on 14. ## What logical replication does not carry | Item | Why | Handling | |---|---|---| | DDL | Schema and DDL commands are not replicated | Freeze schema changes, or apply each to the subscriber first | | Sequence values | Sequence data is not replicated in 18; rows arrive but sequences stay near their start | `setval` each sequence at cutover | | Large objects | Not replicated, and there is no workaround besides normal tables | Copy them separately during the freeze | | Materialized views | Only tables can be published | `REFRESH MATERIALIZED VIEW` on 18 | | Roles and grants on globals | Cluster-wide objects | `pg_dumpall --globals-only` | | Planner and activity statistics | Rows are inserted afresh | Analyze before or right after the switch | Identity columns are backed by sequences, so the sequence gap bites even on a schema with no `serial` anywhere: the first insert after the switch fails with a duplicate key. ## The cutover 1. Stop the writers: put the applications in maintenance mode or pause them at the pooler, and confirm `pg_stat_activity` shows no writing sessions on 14. 2. On 14, read `pg_current_wal_lsn()` and wait until the slot's `confirmed_flush_lsn` reaches it. 3. Generate `setval` calls from 14's `pg_sequences` and run them on 18. `last_value` can be ahead of the last value handed out when sequences cache, which only skips numbers, never repeats them. 4. Refresh materialized views, copy large objects if any, and run an analyze pass. 5. Repoint connection strings or DNS to 18 and re-enable writes there. 6. Drop the subscription on 18 so the slot on 14 is removed and stops holding WAL. Keeping 14 as a way back, for example with reverse replication, is a rollback-planning decision rather than part of the cutover itself. ## Where the seconds go, and how to keep them seconds - **Write freeze to LSN confirmation.** With the subscriber already streaming, this is usually seconds; a lagging subscriber turns it into minutes, so confirm lag is near zero before you stop writes. - **Sequence sync.** One `setval` per sequence; generate the statements in advance so the window only runs them. - **Materialized views and large objects.** These can dominate the window; refresh large views ahead of time and accept a short staleness, or move large objects into ordinary `bytea` columns well before the upgrade. - **Rehearse.** Run the whole cutover against a staging copy first and time each step; the timings become the published downtime estimate. ## When the old side is 17 or later `pg_upgrade` migrates logical slots and subscriptions only from 17 or later sources; on a 14 cluster they are silently ignored. From 17 there is also a shortcut that skips the logical initial copy: turn a physical standby into a subscriber with `pg_createsubscriber`, `pg_upgrade` that subscriber to 18 while the publisher keeps taking writes, let it catch up, then cut over as above.

  • Why is the long initial copy a risk for the 14 primary, and what do you watch?
    The subscription's slot keeps all WAL from its creation until the subscriber confirms it, so a copy lasting many hours can fill `pg_wal` on 14. Watch the slot in `pg_replication_slots` and the disk, and size headroom before you start; how slots retain WAL and how to cap it belongs to the slots topic.
  • What changes in this plan if the old cluster is PostgreSQL 17 rather than 14?
    From 17 you can convert a physical standby into a subscriber with `pg_createsubscriber`, avoiding a logical copy of 3 TB. You then `pg_upgrade` that subscriber to 18, which keeps its subscription and replication origin because the source is 17 or later, while the publisher keeps taking writes. The cutover checklist itself is unchanged.

  • Logical replication carries ALTER TABLE and CREATE INDEX, so the 18 schema stays in sync by itself.
  • Sequences follow the rows, so identity columns continue correctly after the switch.
  • wal_level = logical can be applied on the 14 primary with a configuration reload.
  • pg_upgrade of the 14 cluster would also migrate its logical slots and subscriptions.
  • Large objects replicate along with the tables that store their OIDs.

Assumes PostgreSQL 18 as subscriber and 14 as publisher: logical replication in 18 replicates neither DDL nor sequences, and pg_upgrade keeps logical slots and subscriptions only from 17 or later sources.