Spock 5 ‐ 6 upgrade procedure

Scope. A Spock mesh, one node at a time, no code changes. Any old major, any supported Spock version. Everything the procedure assumes of the cluster is a numbered requirement in Part 1 — check all of them before you start, not after.

Why there are preconditions at all. pg_upgrade destroys three things.

The inbound position — can be preserved exactly, by recording it before the upgrade and putting it back afterwards.
The outbound position — not preserved by this procedure: a slot can only be created at the current position, so the debt must be zero first. (From an old cluster of PostgreSQL 17 or later pg_upgrade does migrate logical slots, so N's outbound slot would in fact survive. Step 6 drops it deliberately anyway, so that the procedure is the same on every old major instead of branching on one.)
The commit-timestamp SLRU — cannot be preserved at all, and there is nothing to record. pg_commit_ts is never copied, so the new cluster starts with an empty SLRU and a fresh oldestCommitTsXid/newestCommitTsXid range. Every xid from before the upgrade falls outside that range, and its commit timestamp reads back as zero.

Part 1 — Required state of node N before pg_upgrade

#	requirement	check
0	A baseline is recorded	ACE `repset-diff` (data) and `spock-diff` (nodes, subscriptions, repset membership). Convergence is not demanded: pre-existing divergence passes through the upgrade untouched, and the fence neither creates nor heals it. What the baseline buys is attribution — step 12 can then claim "no new divergence", which is the only claim it could ever support
1	`forward_origins` is `'{}'` everywhere — the procedure assumes a node's outbound slots carry only what that node originated itself	`SELECT count(*) FROM spock.subscription WHERE sub_forward_origins <> '{}'::text[]` → `0`, on every node. Anything else and requirement 5 stops meaning what step 2 checks: N's slots would also owe its peers changes that originated on a third node, and a replacement slot created at N's current position drops those as well — with no record of what went missing
2	DDL replication off, cluster-wide	`SHOW spock.enable_ddl_replication` → `off` on every node
3	Peers retain WAL for N while it is away	`SHOW max_slot_wal_keep_size` → `-1` on every peer
4	N is off the write path — no application traffic reaching it	no user write transactions in `pg_stat_activity`; N's own commits have stopped
5	Every peer has consumed everything N produced	each of N's slots at the sync-event LSN from step 2 below
6	N's subscriptions are disabled and its apply workers are gone	`sub_show_status()` shows none `replicating`; no `spock apply%` in `pg_stat_activity`
7	The fence record is complete — two parts, both to a file	(a) N's inbound positions: one line per subscription, count must match `spock.subscription`; a missing line means a subscription with no origin, which must be diagnosed, not upgraded. (b) the definitions of the peer subscriptions step 9 will drop and recreate — recreating one from a template instead of from the record silently changes what replicates

Part 2 — Procedure

Fence

Take N off the write path. An application/pooler change, not a database one. Confirm N's own commits have stopped.
On N, emit a sync event, note the LSN as LSN0; then on every peer wait for it:
```
-- N:
SELECT spock.sync_event();
-- each peer:
CALL spock.wait_for_sync_event(NULL, 'N'::name, 'LSN0'::pg_lsn, 60);
```
The first argument is an OUT parameter; NULL is the conventional placeholder. Pass a timeout. It defaults to 0, which means wait forever — a peer that never confirms would hang this step and step 5 would never be reached.

Then confirm on N that every slot reached LSN0:
```
SELECT slot_name, confirmed_flush_lsn FROM pg_replication_slots ORDER BY 1;
```
Repeat until every slot is at or past LSN0. The two conditions are not the same instant: wait_for_sync_event proves the peer applied the event, whereas confirmed_flush_lsn on this side only moves once the peer's feedback arrives and the walsender processes it. A single sample straight after the wait can still show slots behind.

On N, disable each subscription and wait until the workers are actually gone:

SELECT sub_name FROM spock.subscription ORDER BY 1;
-- for each name:
SELECT spock.sub_disable('sub_n2_n1', false);

-- repeat until both are 0:
SELECT count(*) FROM spock.sub_show_status() WHERE status = 'replicating';
SELECT count(*) FROM pg_stat_activity WHERE application_name LIKE 'spock apply%';

On N — record the inbound positions to a file or a database table:
```
\o /path/to/fence-state.txt
SELECT s.sub_name || '|' || s.sub_slot_name || '|' ||
       coalesce(o.remote_lsn::text, 'MISSING')
FROM spock.subscription s
LEFT JOIN pg_replication_origin_status o ON o.external_id = s.sub_slot_name
ORDER BY 1;
\o
```
On each peer — record the definition of the subscription step 9 will drop, which is the other half of requirement 7:
```
SELECT sub_name, sub_replication_sets, sub_forward_origins, sub_apply_delay,
       sub_force_text_transfer, sub_enabled, sub_skip_lsn
FROM spock.subscription WHERE sub_name = 'sub_N_<peer>';
```
Add sub_skip_schema to that list where the column exists. It arrived in spock--5.0.1--5.0.2.sql, so every Spock from 5.0.2 onward has it — probe for it rather than deciding by major version (see step 9). sub_skip_lsn is recorded for information only — sub_create has no parameter for it and recreation resets it to 0/0, so a non-zero value has to be re-applied by hand.
STOP AND VERIFY. Run every check in Part 1. If any fails, do not continue — see If a check fails.

Upgrade

On each peer, drop the subscription to N — while N is still running. Then stop N cleanly and upgrade. Keep spock in shared_preload_libraries throughout.
```
-- on each peer, before N is stopped:
SELECT spock.sub_drop('sub_N_<peer>');
```
Why here rather than in step 9. Step 3 stops N applying; until this runs, nothing stops the peers pulling from N, so the fence is only half a fence. Every peer's apply worker is retrying N's address in a loop, and whatever comes up at that address collects those connections — including the short-lived server pg_upgrade starts for its own checks, which then fails with replication slot "…" is active for PID, and the upgraded instance itself, reached before step 8 has restored anything.

Nothing is lost by dropping this early: requirement 5 already drove every peer's debt on N to zero, and requirement 7(b) already recorded what step 9 rebuilds from. Dropping while N is still reachable is also what lets spock.sub_drop() remove the slot on N — it can only do that while it can still connect to the provider, and otherwise logs could not drop slot "…" on provider, you will probably have to drop it manually and carries on. N then enters pg_upgrade with no logical slots at all.

N must stay off the write path until step 11. It is startable and writable the moment this step finishes, but each peer's replacement slot is not created until step 9 — so anything written to N in between is before those slots' starting point and is skipped, exactly as an unfenced write would be.
Spock upgrades its own catalog on startup, so no manual ALTER EXTENSION spock UPDATE is needed. The database manager compares pg_extension.extversion against the library's version and runs the upgrade itself, before it launches any apply worker.

Re-establish the cluster-wide settings on N before step 8. pg_upgrade does not carry postgresql.auto.conf, so anything set with ALTER SYSTEM is back at its default on the new cluster — requirement 2's spock.enable_ddl_replication = off among it. Requirement 2 is violated again the moment N starts, and nothing later in this procedure looks at it again.

Restore

On N — recreate each origin and put it back exactly where it was, from the recorded file:
```
SELECT pg_replication_origin_create('spk_regression_n2_sub_n2_n1');
SELECT pg_replication_origin_advance('spk_regression_n2_sub_n2_n1', '0/1B93898');
```
Confirm each landed — comparing as pg_lsn, not as text:
```
SELECT o.external_id, o.remote_lsn = '0/1B93898'::pg_lsn AS ok
FROM pg_replication_origin_status o;
```
PostgreSQL 18 pads the low word to eight hex digits, so a position recorded on an older major as 0/1B93898 reads back as 0/01B93898. It is the same value; a text comparison, or reading the two side by side, calls every correctly restored origin wrong.
On each peer — recreate the subscription dropped in step 6. Nothing else recreates it, and its slot on N is gone:
```
SELECT spock.sub_create(
    subscription_name     := 'sub_N_<peer>',
    provider_dsn          := '<N dsn>',
    -- from the requirement 7(b) record, NOT from sub_create's defaults
    replication_sets      := <recorded sub_replication_sets>,
    forward_origins       := <recorded sub_forward_origins>,
    apply_delay           := <recorded sub_apply_delay>,
    force_text_transfer   := <recorded sub_force_text_transfer>,
    skip_schema           := <recorded sub_skip_schema>,   -- Spock 6 only
    synchronize_structure := false,
    synchronize_data      := false,
    enabled               := true);
```
The five replication options come from the record; the last three are fixed by the procedure. Passing the literal defaults instead is the quiet way to lose data here: replication_sets defaults to {default,default_insert_only,ddl_sql}, so a subscription that carried anything else comes back carrying less, with no error and no log line.

The parameter set is version-dependent, but not along the Spock 5 / Spock 6 line. Both the sub_skip_schema column and the skip_schema argument arrived in spock--5.0.1--5.0.2.sql, so every Spock from 5.0.2 onward has them and only 5.0.0 and 5.0.1 do not. Ask the peer what it supports (pg_attribute for the column, pg_proc.proargnames for the argument) rather than assuming a fixed list — which is what makes this correct without knowing the version at all.

synchronize_data := false is safe only because requirement 5 held.

On N — re-enable:

SELECT spock.sub_enable('sub_n2_n1', true);

Put N back on the write path.

Verify before moving on

On every node:
```
SELECT subscription_name, status FROM spock.sub_show_status();   -- all 'replicating'
```
Drive a spock.sync_event() round trip across every edge first and wait for it. Then compare contents across all nodes for the replicated tables. Row count, distinct key count and a checksum are the minimum; equal counts alone would not catch re-application.

Use pgEdge ACE (https://github.com/pgEdge/ace). It is the intended companion: Spock reserves its pgedge_ace schema as strictly node-local, so its own state never replicates or travels with add_node.
- repset-diff — runs table-diff over every table in a replication set and aggregates. Exactly the right scope: anything outside a replication set is legitimately different between nodes.
- spock-diff — catches a configuration mistake, which step 9 makes easy: the replication_sets array is retyped by hand there, and getting it wrong leaves a subscription that replicates, but not everything it used to.
- mtree table-diff with mtree build / mtree update — Merkle-tree comparison, for when a full compare per node is too expensive to repeat.
Compare against the requirement 0 baseline: the question is whether the upgrade changed anything, not whether the cluster is perfect. If the baseline was not clean, table-rerun against the saved diff file shows whether those same rows are still the only ones out of step.

Note that ACE's exit status is not its verdict. repset-diff returns success even when it finds differing tables — it fails only on a table it could not compare — and writes a <schema>_<table>_diffs-<timestamp>.json report instead. spock-diff writes its report whether or not anything mismatched, so read the per-pair mismatch flag inside it rather than treating the file's existence as a finding.
Start the next node. When every node is done, restore spock.enable_ddl_replication and max_slot_wal_keep_size to their previous values cluster-wide.

If a check fails

Point of no return is step 6. Before it, undo by re-enabling the subscriptions and putting N back on the write path.

Fallback — rebuild instead of upgrade

If N cannot be taken off the write path, skip all of the above: drop N's subscriptions on the peers, build a fresh cluster on the new major, and re-add N with samples/Z0DAN/zodan.sql's spock.add_node(). No position reasoning at all, at the cost of a full resync.

Navigation Menu

Uh oh!