0Pricing
PostgreSQL Performance & Query Optimization · Lezione

Aggiornamenti di versione principale con downtime quasi nullo

Usi la replica logica per effettuare l’upgrade tra versioni mantenendo l’applicazione online.

Aggiornamenti di versione principale con downtime quasi nullo è una lezione PostgreSQL Performance & Query Optimization gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento PostgreSQL Performance & Query Optimization, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso PostgreSQL Performance & Query Optimization include 4 lezioni in totale.

Parti di questa lezione non sono ancora state tradotte e vengono mostrate in inglese.

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.

Domande Frequenti

La lezione «Aggiornamenti di versione principale con downtime quasi nullo» è gratuita?

Sì — il testo completo di «Aggiornamenti di versione principale con downtime quasi nullo» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso PostgreSQL Performance & Query Optimization, passa a CoddyKit PRO. Il corso PostgreSQL Performance & Query Optimization include 4 lezioni in totale.

Cosa imparerò in «Aggiornamenti di versione principale con downtime quasi nullo»?

Usi la replica logica per effettuare l’upgrade tra versioni mantenendo l’applicazione online. Eserciti PostgreSQL Performance & Query Optimization con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare PostgreSQL Performance & Query Optimization?

Non è richiesta alcuna esperienza precedente. PostgreSQL Performance & Query Optimization su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.

Quanto tempo richiede la lezione «Aggiornamenti di versione principale con downtime quasi nullo»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione PostgreSQL Performance & Query Optimization?

Sì. Ogni lezione PostgreSQL Performance & Query Optimization include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Publication, subscription e replica identity
  2. Delega dei carichi di lettura e analitici
  3. Aggiornamenti di versione principale con downtime quasi nullo
  4. Monitoraggio del replication lag e del bloat degli slot
← Torna a PostgreSQL Performance & Query Optimization