Logical Upgrade Paths, Cross-Platform Migration, Downtime Minimization, and Validation

Learning outcomes

ServiceHub must move from one operating-system platform to another while also changing PostgreSQL major version. pg_upgrade is designed around compatible physical storage and local cluster conversion; a logical migration reconstructs database objects and rows through SQL/protocol-level representations, making it the natural cross-platform path. The cost is more data movement and a different cutover design.

1. Inventory what must survive a logical migration

SELECT current_database(),       pg_encoding_to_char(d.encoding) AS encoding,       d.datlocprovider,       d.datcollate,       d.datctype,       d.datlocale,       d.datcollversionFROM pg_database AS dWHERE d.datname = current_database();SELECT extname, extversionFROM pg_extensionORDER BY extname;SELECT count(*) AS large_object_countFROM pg_largeobject_metadata;SELECT schemaname, sequencename, last_valueFROM pg_sequencesWHERE schemaname = 'app'ORDER BY sequencename;

Encoding and database locale/provider choices are database-creation properties. ICU/libc versions can alter sort behavior. Extensions must exist on the target platform/major. Sequences and large objects need explicit attention in logical-replication designs.

2. Create the Chapter 23 source dataset

DROP TABLE IF EXISTS app.ch23_order CASCADE;SET ROLE servicehub_owner;CREATE TABLE app.ch23_order (  order_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  tenant_id integer NOT NULL,  customer_id bigint NOT NULL,  status text NOT NULL CHECK (status IN ('queued','assigned','completed','cancelled')),  amount numeric(12,2) NOT NULL CHECK (amount >= 0),  created_at timestamptz NOT NULL,  note text NOT NULL);INSERT INTO app.ch23_order(tenant_id,customer_id,status,amount,created_at,note)SELECT (g % 32) + 1,       10000 + (g % 4000),       (ARRAY['queued','assigned','completed','cancelled'])[(g % 4)+1],       ((g % 50000) / 100.0)::numeric(12,2),       TIMESTAMPTZ '2026-01-01 00:00+00' + (g || ' seconds')::interval,       'upgrade-change-lab-' || gFROM generate_series(1,100000) AS g;CREATE INDEX ch23_order_tenant_status_idxON app.ch23_order (tenant_id, status);RESET ROLE;ANALYZE app.ch23_order;

SELECT count(*) AS orders,       sum(amount) AS amount_sum,       count(*) FILTER (WHERE status='completed') AS completed_orders,       min(created_at) AS first_created_at,       max(created_at) AS last_created_atFROM app.ch23_order;SELECT last_value, is_calledFROM app.ch23_order_order_id_seq;

Store these values with the change ticket. They are simple business invariants, not a cryptographic proof of every byte.

3. Dump/restore: use the newer pg_dump for cross-version migration

PostgreSQL's guidance is to use the newer version's pg_dump when moving to a newer server. A newer pg_dump can read many older server versions, while an older pg_dump refuses to dump a server newer than itself.

# NEW_BIN points to the target PostgreSQL major's tools.# Cluster-global roles/tablespaces are NOT in a per-database pg_dump."$NEW_BIN/pg_dumpall" \  --dbname="service=servicehub-lab-admin" \  --globals-only \  --file=servicehub_ch23_globals.sql"$NEW_BIN/pg_dump" \  --dbname="service=servicehub-lab-admin" \  --format=custom \  --statistics \  --file=servicehub_ch23.dump"$NEW_BIN/pg_restore" --list servicehub_ch23.dump | head -n 40

pg_dump covers one database. PostgreSQL roles and tablespaces are cluster-global, so inventory/provision them separately; pg_dumpall --globals-only is one free local way to capture those definitions. Review the globals script before applying it to a target because role attributes, memberships, tablespace paths, and ownership policy may intentionally differ. Using pg_restore --no-owner does not remove the need for a deliberate target-role and ACL strategy.

A whole-database dump includes table data, sequence values and large objects by default. PostgreSQL 18 can also include optimizer statistics with --statistics; extended/custom/cumulative statistics still require appropriate post-restore analysis.

4. Restore into a clean target database—not on top of unknown state

"$NEW_BIN/dropdb" --if-exists servicehub_ch23_restore"$NEW_BIN/createdb" --template=template0 servicehub_ch23_restore"$NEW_BIN/pg_restore" \  --dbname=servicehub_ch23_restore \  --no-owner \  --exit-on-error \  servicehub_ch23.dump

For a real cross-platform migration, create the target database with deliberate encoding/locale-provider settings that satisfy application requirements. Do not assume the target OS's default locale is equivalent to the source.

5. Validate restored objects and business invariants

SELECT count(*) AS orders,       sum(amount) AS amount_sum,       count(*) FILTER (WHERE status='completed') AS completed_orders,       min(created_at) AS first_created_at,       max(created_at) AS last_created_atFROM app.ch23_order;SELECT last_value, is_calledFROM app.ch23_order_order_id_seq;SELECT conname, contype, convalidatedFROM pg_constraintWHERE conrelid = 'app.ch23_order'::regclassORDER BY conname;SELECT indexrelid::regclass AS index_name,       indisvalid, indisreadyFROM pg_indexWHERE indrelid='app.ch23_order'::regclassORDER BY 1;

Compare source and target acceptance values, then execute representative reads/writes. A dump that restores without error can still have different collation behavior, extension semantics, planner choices or driver/application behavior.

6. Logical replication lowers cutover downtime—but it does not replicate everything

Built-in logical replication copies table rows and ongoing INSERT/UPDATE/DELETE/TRUNCATE changes through publications/subscriptions. It can replicate between different PostgreSQL majors, making it useful for low-downtime upgrades. But current PostgreSQL does not replicate schema/DDL, sequence state or large objects.

Core logical replication replicates table changes, not schema/DDL, sequence state, or PostgreSQL large objects. Those are separate migration workstreams. Schema must be made compatible on the subscriber before replicated changes require it; sequence values need an explicit cutover synchronization; applications using large objects need a separate copy/validation strategy or a modeled-table redesign.

7. A minimal logical-replication topology is more than CREATE SUBSCRIPTION

-- Requires wal_level=logical and replication-role/HBA configuration.CREATE PUBLICATION ch23_servicehub_pubFOR TABLE app.ch23_order;SELECT pubname, puballtablesFROM pg_publicationWHERE pubname='ch23_servicehub_pub';

-- Create compatible target schema/table FIRST.-- Connection string shown without a real password.CREATE SUBSCRIPTION ch23_servicehub_subCONNECTION 'host=127.0.0.1 port=55471 dbname=servicehub_lab user=ch23_repl'PUBLICATION ch23_servicehub_pub;SELECT subname, subenabledFROM pg_subscriptionWHERE subname='ch23_servicehub_sub';

This topology is optional/administrative because it requires two servers plus logical-replication authentication. The free mandatory lab is the dump/restore drill above. For production logical migration, apply additive target schema changes before publisher changes that would otherwise make incoming rows incompatible.

8. Sequence state creates a cutover hazard

Suppose the subscriber has caught up with every table row but its identity sequence still has an old value. After cutover, the first insert can collide with an existing primary key. During the final write freeze, capture source sequence state and apply it to the target.

SELECT last_value, is_calledFROM app.ch23_order_order_id_seq;

-- Substitute the captured values from the source:SELECT setval(  'app.ch23_order_order_id_seq',  100000,  true);SELECT last_value, is_calledFROM app.ch23_order_order_id_seq;

Do not hard-code 100000 in a real runbook; transfer the observed source state. Large objects require a separate migration mechanism because built-in logical replication does not carry them.

9. Write-freeze defines the clean rollback boundary

1. Prove target schema/extensions/locale/clients are ready while source remains primary.
2. Let initial copy and replication catch up.
3. Enter an explicit application write freeze.
4. Wait until replication shows no unapplied source changes.
5. Transfer/verify sequence state and any non-replicated objects.
6. Run business invariants and smoke writes against the target in a controlled gate.
7. Route production writes to the target only after the acceptance decision.

Before target writes begin, rollback can often mean “keep source primary and abandon/rebuild target.” After target accepts unique production writes, switching back to the old source without reverse replication/reconciliation loses or forks those writes. The rollback design changes at that moment.

10. Cross-platform validation includes collation semantics and query performance

SELECT c.oid::regcollation,       c.collprovider,       c.collversion,       pg_collation_actual_version(c.oid) AS actual_versionFROM pg_collation AS cWHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid)ORDER BY 1;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT tenant_id, status, count(*), sum(amount)FROM app.ch23_orderGROUP BY tenant_id, statusORDER BY tenant_id, status;

Repeat the same business queries on source and target. Identical rows do not guarantee identical sort order under a different collation library or identical performance under a different planner/OS/storage stack.

11. Cleanup

"$NEW_BIN/dropdb" --if-exists servicehub_ch23_restorerm -f servicehub_ch23.dump servicehub_ch23_globals.sql

Dump files contain data and executable database definitions; protect them as backups, not disposable build logs. Only delete after the lab or according to the real backup retention policy.

Authoritative references

Upgrade, compatibility, locking, and migration behavior is version-sensitive. These PostgreSQL primary sources define the mechanisms used here; always read the exact source/target release notes during a real change.

• PostgreSQL 18 — Upgrading a PostgreSQL Cluster
• PostgreSQL 18 — pg_dump
• PostgreSQL 18 — Logical Replication
• PostgreSQL 18 — Logical Replication Restrictions
• PostgreSQL 18 — pg_database Catalog