migrate

retrofit migrate emits a migration script between two cuts. Where snapshot emits the post-state of one cut, migrate emits the DDL that transitions one cut to another: forward by default, or the reverse with --down. Its output is the script a CI/CD pipeline applies to a live database.

Read concepts.md first for the oplog / cut vocabulary and snapshot.md for the post-state-emission cousin. This doc covers what migrate sees, what it produces, what it refuses, and why.

What migrate sees

Two cuts:

• --from: the cut you assume the live database is at. Default: the most recent Seal, or root when the oplog has no Seal yet (everything is unpublished, so the delta is the whole oplog).
• --to: the cut you want to land on. Default: last.

Together the defaults render the unpublished delta (last-seal-or-root, last]: everything authored since the last publication boundary. With no Seal that is (root, last], the whole oplog as a forward CREATE script (identical to migrate --from=root). So retrofit migrate | psql applies whatever is unpublished.

When the resolved window is well-formed but empty (at steady state right after a seal, or --from=seal --to=seal, or an empty oplog), migrate emits a disclosed no-op: exit 0, a valid migration document with the header marker -- no changes in the requested window: nothing to migrate plus the empty BEGIN;/COMMIT; wrapper, and a one-line stderr advisory nothing to migrate: the requested window is empty. Nothing-to-deploy is success, not a refusal. (Under --as=json the no-op is a type:"ok" envelope with no ddl stream object, plus the advisory as a prior stderr note line; the absence of that object is the no-op discriminator.) Refusals are reserved for malformed requests (range-inverted, opid-not-found, fork-in-range), which an empty window never is.

The marker rides the header, so --no-header drops it along with the banner, leaving an empty BEGIN;/COMMIT; with nothing to say it is empty on purpose. If you strip the header, do not read the emitted text to decide whether there was anything to deploy: read the stderr advisory, or --as=json and test for the absent ddl stream object.

Instead of naming a cut by hand you can pass --from-dump <file> to identify the cut the database is at by identify-ing a pg_dump --schema-only capture: migrate fingerprints the dump against the oplog and fills the window slot the database occupies with the cut it matches. Forward that is the from-cut, so the script starts where the database is. Under --down the database is assumed at the post-window state, so the dump fills --at instead and --from names the cut to return to (default: the latest Seal), so the two directions stay symmetric and neither needs a cut spelled out:

retrofit migrate --from-dump db.sql | psql          # forward, from where the DB is
retrofit migrate --down --from-dump db.sql | psql   # back to the latest seal

--from-dump is mutually exclusive with whichever flag names that same slot: --from forward, --at under --down.

Both flags accept opid prefixes (any unambiguous hex prefix) and the canonical anchors root / last / seal, each with an optional ^ / ~N decrement (always older): seal^ is the previous seal, last~2 is two ops back.

To check which cut a dump is at rather than migrate from it, use the identify verb directly. retrofit identify <dump> --expect <ref> reads the same reference grammar as --from and exits 0 only when the dump is at that cut, refusing expect-mismatch when it is at some other valid cut: a deployment gate in one command, with no need to read the emitted script. The one grammar difference is root: no dump is identified at the empty pre-schema state, so --expect root refuses expect-unidentifiable rather than reporting a mismatch it could never satisfy. A dump of a database that holds neither the schema nor any of its objects refuses from-dump-schema-absent on both verbs; build that database with retrofit migrate --from root.

retrofit migrate                                   # latest Seal → last; resolver picks schema
retrofit migrate --schema sales                    # explicit schema
retrofit migrate --from 988edcb6 --to 7a6b1234     # explicit range
retrofit migrate --from root --to last             # full history
retrofit migrate --from seal^ --to seal            # the last sealed batch
retrofit migrate --down --from seal^ --at seal     # reverse: undo the last sealed batch
retrofit migrate --from-dump db.sql                # resolve from-cut by identifying a dump
retrofit migrate --down --from-dump db.sql         # reverse from where the DB is, back to the latest seal
retrofit migrate --envelope=caller                 # you open the transaction; emit no wrapper
retrofit migrate --no-header                       # suppress engine-version banner
retrofit migrate --into-schema staging             # copy script: emit into a different schema
retrofit migrate --using=postgres:18               # one-shot emit-target override
retrofit migrate --label env:dev                   # include the env:dev dataset

An authored drop asks nothing. A range containing a DropFunction emits a bare DROP FUNCTION <schema-qualified sig>; (never CASCADE, so PostgreSQL’s own dependency check stays the apply-time backstop; no -- destructive: marker, since a function drop discards no data). Replay refuses a DropFunction while a live element calls the function, as it refuses a DropTable under a live dependent.

Schema selection follows the six-step resolver chain. --schema is a local flag on migrate.

Finding opids for --from / --to

The opid is the first field of each on-disk record: AddColumn(988edcb612ab, prev=…, …). Run retrofit op show --help-all for the full inventory of narrowing flags (--tail / --head / --filter / --touching / --framing, …).

Objects the project does not own: [[exclude]]

--from-dump matches the dump to a cut of the oplog through identify’s reader, and identify and migrate --from-dump apply the project’s [[exclude]] set to the dump first: every statement an entry owns (a migration runner’s history table, a provider’s extension or schema) is removed before the dump is fingerprinted, so those objects never read as divergence. Each applied entry is reported on stderr as an exclusion-applied advisory, and under --as=json the terminal record carries an excluded array. A kept statement that depends on an excluded table or extension refuses exclusion-dependent, and an entry naming an object the oplog already holds refuses exclusion-in-log; both exit 2 before anything is emitted. The oplog is never edited, and migrate renders from it unchanged. See getting-started.md for the retrofit.toml entry.

What migrate produces

A single migration script on stdout: BEGIN; + the forward DDL + COMMIT; under the default envelope. The DDL is the sequence of ALTER / DROP / CREATE statements that converts the --from cut into the --to cut. The envelope sets whether the pair is there.

Every emitted file opens with a header stating the engine version and the starting state the script assumes:

-- engine: postgres 17
-- assumes database is at 0f3c1a9b2d4e

The assumes database is at line names the --from cut’s operation id: the script’s statements are only correct against a database in exactly that state. When --from-dump fills the slot, the dump’s position is a seal run: two or more seals joined only by ops a schema-only dump cannot observe (for example a reference-data declaration), which the dump cannot tell apart. --from-dump takes the run’s oldest seal for --from and, under --down, its newest for --to; the from-dump-seal-run note names the run, and the window is refused when its data would not converge at every seal of the run. Under --down the starting state is the other end of the window, so the second line reads instead:

-- reverse of migration window assuming database is at the post-window state

--no-header suppresses this whole header: engine line, assumes line, and the disclosed-no-op marker together.

Every emitted file carries one obligation line naming its envelope:

-- envelope: own; apply this file outside any open transaction
-- envelope: caller; apply this file inside a transaction you open
-- envelope: independent; apply this file one statement at a time, in one session, stopping at the first error (psql: -v ON_ERROR_STOP=1); no other session writes the tables it rewrites

The line is a requirement on you, the consumer; it is not a description of the file, and not a directive any runner parses. It survives --no-header, because the invocation most likely to strip the banner is a scripted pipeline, which is exactly where a silent wrapper mismatch costs the most.

An independent file is not all-or-nothing, so its line also states the two assumptions it depends on: the runner stops at the first error, and no other session writes the tables the file rewrites, since no lock holds traffic off between statements. The file states -v ON_ERROR_STOP=1 and does not set it: \set ON_ERROR_STOP on is a psql command the other runners in the independent rows may not parse.

JSON mode wraps the whole document in {"type":"migrate","ddl":"<full doc>"} followed by {"type":"ok"}. The ddl field is the same string text mode would print.

Like snapshot, migrate is deterministic: same oplog + same (from, to) cuts + same flags = byte-identical output.

The -- Data: designation line

Every run of data rows migrate splices in, at an anchor or in the trailing band, opens under one comment line naming the table and why its rows are there:

-- Data: "shop"."statuses" (reference)
INSERT INTO "shop"."statuses" ("status_id", "name") VALUES (1, 'new');
-- Data: "shop"."statuses" (transition)
UPDATE "shop"."statuses" SET "name" = 'open' WHERE "status_id" = 1;
-- Data: "shop"."fixtures" (seed, label qa:golden)
INSERT INTO "shop"."fixtures" ("id", "kind") VALUES (1, 'golden');

The designation is reference, seed, label <label>, transition, cleanse or unclassified (the last only under --allow-unclassified-dml). A line sits above the body’s own comment lines and above a replace marker, and a splice never shares a run with the next one. A replace block that also carries another label’s rows opens that label’s line above them, inside the block, and its own line again below. It is a comment, so it adds no statement and changes no span.

Identity sequences on copy-based data-motion ops

An identity-bearing target created by CopyTable, SplitTable, or MergeTable receives copied rows but a freshly minted backing sequence. For these three copy-based data-motion operators, migrate follows the row copy with an annotated SELECT that uses pg_get_serial_sequence and setval to align each identity sequence to the target column’s max. When every copied value sits below the sequence’s own minimum (for example, all ids are 0 or negative), the sequence is set to that minimum instead, so the next insert receives it. A descending sequence (negative INCREMENT BY) is aligned to the column’s min instead, capped at the sequence’s own maximum, so the next insert receives a value below every copied one. Each target’s statement appears after its row copy:

• CopyTable emits the alignment after its single INSERT.
• SplitTable emits it per child, immediately after that child’s filtered INSERT.
• MergeTable emits it once, after the last source INSERT and before the consumed sources are dropped.

ExtractTable is excluded from this disposition; do not infer a universal tail for every data-motion operator or for the migration script as a whole. On an empty target the max is null, so setval is not applied and the new sequence’s declared START WITH remains untouched.

At op add, the data-motion-fresh-sequences advisory announces this alignment. For MergeTable it also warns about overlapping source identity ranges. They must be disjoint when the identity value alone is enforced unique by an inherited single-column primary key, UNIQUE constraint, or unique index: alignment does not deduplicate overlapping values, so PostgreSQL fails that merge at apply time. A composite key or index does not establish that condition by itself. Without a single-column enforcer, duplicate identity values can coexist; the alignment still advances the sequence past their maximum.

PostgreSQL sequence-value changes are generally non-transactional, but this alignment does not leave a standalone rollback residue under a supported envelope. With own or caller, the freshly created target and its sequence roll back together. With independent, the target creation, row copy, and setval are already committed before a later statement can fail. Do not extrapolate the general setval rule into a state where this freshly created target rolls back but its sequence survives.

The row-level security read guard on row-moving ops

A render that reads rows and then moves, copies, rewrites or drops them reads as the applying role. If row-level security applies to that role on a table the render reads, the read returns only the rows the policies show. PostgreSQL raises no error, and the render’s own counts see the same filtered rows. The rows the policies hid are then dropped or left out of the copy, and the apply still exits 0.

So each such render guards every table it reads. This covers CopyTable, CopyColumn, ExtractTable, JoinTable, SplitTable, MergeTable, InlineTable, the PartitionTable and DepartitionTable rebuilds, and each table an --into-schema copy reads. It also covers the reverses that read rows back: the ExtractTable fold-back and re-creation, and the full-outer JoinTable reverse. The guard sits after the render’s LOCKs and before its first read.

The guard asks PostgreSQL row_security_active('<schema>.<table>'::regclass) and raises an exception if it is true: row-level security filters <the carrier> of <table>. It runs before the render creates or changes anything.

• It never raises for a superuser or a role with BYPASSRLS.
• It raises for the table’s owner only when the table is FORCE ROW LEVEL SECURITY.
• It raises for any other role whenever row-level security is enabled on the table.

It raises even when the policies would hide no rows.

The guard does not depend on what the image shows. The image holds no role, a RunRawScript that enables row security leaves the image unchanged, and row security enabled outside the log never reaches it. A table that the image shows without row security therefore gets the same guard. Where the table has no row security, the check passes.

To apply past it, apply the script as a superuser or as a role with BYPASSRLS; the same file then copies every row. Row security that the image lacks is recorded with drift.

--down: reverse migration

--down emits the reverse of the (from, to] window: the DDL that undoes the forward migration, walking the same op range in inverse. Use it to roll a deploy back to --from.

--down does not invert the window op by op. It computes a ghost oplog: for each element the window touched, the net transition from its state at the tip back to its state at --from, written as ordinary catalog operations. The forward renderer renders that ghost, so a down statement is the statement the forward emits for the same operation, with the same qualification, locks, transaction wrapper and -- destructive: marker.

Each touched element returns to --from in one of these ways:

• An element live at both cuts takes its facet rows. It is matched by identity, whatever it is called now: a RenameColumn or RenameTable is renamed back, a cast is cast back, a nullability or default change is reverted. An op that changes no structure and moves no rows, such as ValidateConstraint, contributes zero statements: the down emits no DDL for it, and the report names it under reverse-noop.
• An element the window created is dropped. A standalone AddColumn reverses as a DROP COLUMN, and the report names the row values that drop destroys.
• An element the window dropped comes back empty. It is re-created from its definition at --from, without the rows the drop discarded, and the report says so.
• A structural op inverts as its paired operator, which keeps the rows: an ExtractTable as an InlineTable, a JoinTable as an UnjoinTable, a SplitTable as a MergeTable and back, a MoveColumn as the move back.
• A RunRawScript reverses by its declared reverse_body, emitted verbatim under its -- retrofit:raw marker and reported under reverse-opaque. An opaque DO block cannot be read, so nothing can check that the body undoes the forward one. A RunRawScript with no reverse_body blocks the window.

A window that the ghost cannot invert refuses migrate-non-reversible (exit 1) and renders nothing. The detail lists each block, naming the operation and the element. Each block’s Hint names a way back, usually --from <op> to leave the blocking operation outside the window, or a compensating forward. When --from <op> at the blocking operation would leave nothing to roll back, the Hint names only the compensating forward: no later operation in the window changes the schema (the blocking operation ends it, or only reference-data marks follow it), or what follows reverses to no statement (a validation left in place, a default set and dropped again) and no data record is anchored in it.

A relaxation’s reverse policy. A relaxation that removes a rule (DropNotNull, DropCheck, DropForeignKey, DropUnique, DropIndex, SetConstraintEnforced, CastColumn) takes the optional payload reverse, naming what the down does to rows written after the relaxation:

• reverse=refuse returns the rule as a guard: PostgreSQL checks the rows when the statement runs and refuses one that breaks it. Absent reverse reads as refuse wherever the rule can be refused, else as the arm’s one outcome; no segment is written for an absent policy, so every earlier op’s bytes are unchanged. op revise <op> reverse=refuse writes the segment, a byte change that re-mints the op.

• reverse=not_valid returns the rule NOT VALID, checking no row present; the reverse-guard item carries the query that shows the rows left unchecked. A DropNotNull has no NOT VALID form, so its down adds a carrier, CHECK (<column> IS NOT NULL) NOT VALID, named by the relaxation’s name= (by default <table>_<column>_not_null_check, written on the op at op add). The carrier is not validated; its item names the query and the ops that close it. name= without not_valid refuses reverse-policy-payload.

• reverse=coerce returns the rule by coercing the rows, where the operator’s arm renders one. The arm rewrites the rows that break the rule, then re-adds it, so the payload names the rewrite:

  • fill=<value> on DropNotNull sets each NULL to the value, and on DropForeignKey sets the key of each row with no parent to it (fill=null clears it).
  • --fill column=<table.column>,value=<value> on DropCheck, repeated for each column to set, each one the check reads, sets that column on each row the check rejects.
  • --keep <table.column> on DropUnique and DropIndex orders each set of duplicates, with keep_directions and keep_nulls_ordering beside it, then the primary key; the first copy survives and the key’s writable nullable columns (not generated, not a partition key) are set NULL on the rest; a key with none refuses reverse-policy-not-null.

  coerce without its payload, or a payload under any other policy, refuses reverse-policy-payload. The reverse operators (AddNotNull, AddCheck, AddForeignKey, AddUnique, CreateIndex) take the same payload, so op add <reverse operator> … <payload> rolls the rule forward with the same rewrite. Two relaxations of one rule whose payloads differ (a keep_directions of "" against asc, say) refuse reverse-policy-conflict. A payload op refuses under --into-schema (migrate-into-schema-row-motion-unsupported): its rewrite moves rows in place.

reverse outside the qualifier of the row that offers it, or on a companion DropIndex, refuses reverse-policy-undeclared at op add, op revise and replay, except that CastColumn’s widening qualifier is judged at op add and op revise only: a cast already in the log that a later classifier no longer counts as widening keeps its reverse at replay. A member whose arm the down does not render refuses reverse-policy-unrendered. A DropForeignKey that reverses NOT VALID on a partitioned table below PostgreSQL 18 refuses reverse-policy-partitioned-fk. A window that relaxes one rule under policies that differ refuses reverse-policy-conflict; its Hint names the ways out: op revise an unsealed relaxation to the other’s policy, or roll the rule forward with op add of its reverse operator.

op add and migrate --down report each op whose down meets rows that traffic wrote after it as one after-traffic-outcome line, naming the outcomes and the ways to keep those rows. No window refuses for loss; the report names every loss.

The database’s cut is the window’s tip: --at <opid> names it (an opid prefix or anchor, resolved as snapshot --at resolves it); under --from-dump the dump’s cut fills that slot; otherwise it is the log’s last op. A --to B before the tip is a slice, accepted only beside an explicit --at or --from-dump.

The report. The script’s header carries a report of what the down does, as -- comment lines after the -- envelope: line, under --no-header too. The same items go to stderr and, under --as=json, ride the notes array on the migrate record, each with its code and fields. For example:

-- reverse-unrecorded: this script is unrecorded: the oplog does not record the reverse it applies; the database sits at 71c197d33ac6
-- data-loss-on-apply-render-chosen: the reverse of AddColumn 8f2a7e316541 loses the rows of note (certain)
-- reverse-guard: DropNotNull 0a3c161a0df0 returns the rule on x as a guard
-- reverse-irreversible: t.x comes back nullable: its NOT NULL is not restored

• reverse-unrecorded: the oplog does not record the reverse. The Hint names how the log catches up.
• data-loss-on-apply-render-chosen: each step that destroys rows written since the forward, with the window operation it undoes, graded certain or potential. The Hint names the way to keep the rows: author a compensating forward instead of the down. The destroying statement also carries the forward’s -- destructive: marker (a cast’s -- destructive-conditional:). The forward prints that marker on every drop and cast whatever the loss, so read the report, not the marker, for what a step destroys.
• data-loss-on-apply: each element the down re-creates empty, as op add reports the operation the down undoes.
• reverse-guard: each rule the down re-imposes, such as a returned NOT NULL or a re-added check. PostgreSQL checks the rows when the statement runs and refuses one that breaks the rule; under own the file’s transaction then applies nothing. A rule returned NOT VALID checks no row present, and its item carries the query that shows the rows it leaves unchecked.
• reverse-irreversible: what the down does not bring back as --from held it, such as a re-created column whose NOT NULL is not restored.
• reverse-noop: what contributes nothing, such as a constraint left validated.
• reverse-opaque: a RunRawScript reversed by its unchecked reverse_body.
• reverse-slice: for a slice, the range it rolls back and the later operations it keeps.

--down --into-schema <schema> renders the forward --into-schema copy of the --from cut into a side schema beside the live one, a way to inspect the earlier shape before a down. The rows are copied from the live tables by element identity, and the live schema keeps every row. The side schema does not hold what the down destroys: it is the --from cut, so a column the window added is not in it. Like the forward copy, it is wrapped under own. It admits only a window whose copy matches every row and re-imposes nothing: every column live at --from is live at the tip on the same table with the same type, every rule --from holds is held at the tip, and the window holds no RunRawScript. Any other window refuses migrate-non-reversible, naming the first column or rule that fails.

--envelope: the transactional context

--envelope declares what transactional context you will apply the emitted file in. migrate cannot observe that context (it cannot see how your runner sends the file), so the value is an assertion you make, and retrofit emits accordingly.

own wraps on every path, --down and --into-schema included.

The fourth cell (a wrapper and an outer transaction already open) is not offered as a value. That is the state that produces SQLSTATE 25001, there is already a transaction in progress, and no value emits a wrapper into a context declared as already wrapped. The state is reachable by ordinary user error, which is why the obligation line under own states the requirement in the file.

The envelope also sets whether a mid-file transaction boundary is emitted where an operator needs one (see Segmented scripts). Under own the file carries that boundary. Under caller or independent it cannot: a bare mid-file COMMIT; would end your transaction early, leaving whatever already ran committed.

The two no-wrapper envelopes diverge from there, and they do not behave alike:

• caller refuses. You told retrofit a transaction of yours is open around the file, so the boundary it would need to place lands inside your transaction: dropping it emits a file that fails at apply, and emitting it commits your work early. The run refuses with migrate-tx-incompatible (class: tx-incompatible-unattested) and exits 1. No flag changes this: under caller retrofit owns no span to segment. The remedies are --envelope=own, narrowing --from/--to so the boundary-requiring operator falls outside the window, or splitting the migration into separate files whose boundaries your apply context cannot straddle.
• independent emits, and advises. Each statement already runs in its own implicit transaction, which satisfies the requirement, so nothing is refused and nothing needs a boundary emitted. The run exits 0 with a migrate-segmented-non-atomic advisory on stderr, stating that the file is not all-or-nothing under this envelope.

A window that resolves its boundary requirement without spending atomicity (a trailing commit boundary with nothing after it to read the effect) neither refuses nor advises segmentation under either value. It emits a per-operator boundary-requirement-* advisory instead, naming the operation and the resolved envelope, because the obligation is now your apply context’s to honor and retrofit cannot see whether it does.

--envelope is registered on migrate only. snapshot’s wrapper is a separate surface keyed on --include-drop, and it is unaffected.

Deferred foreign keys around a row-writing op

A foreign key declared DEFERRABLE INITIALLY DEFERRED queues its check to COMMIT. A row-writing op in the same transaction (a fold’s UPDATE or DELETE, a split’s INSERT) leaves such checks pending, and a later ALTER TABLE of the table then fails 55006 (pending trigger events).

Under own, migrate brackets each such op: a SET CONSTRAINTS <key>, … IMMEDIATE; line right after the op’s last LOCK TABLE (or opening the op), and a SET CONSTRAINTS <key>, … DEFERRED; line after the op’s last statement for each key that still stands. The keys are those the op’s writes can queue a check under, including the keys a referential action reaches. A key the render drops before its write is left out. A write left dangling under a bracketed key by an earlier op fails 23503 at the opening line, naming the key. The script raises one deferred-keys-bracketed note listing the keys by op.

Under caller, the script sets no constraint mode: the enclosing transaction owes it, and the script raises one deferred-keys-owed warning whose Hint composes the SET CONSTRAINTS … IMMEDIATE; head and … DEFERRED; tail to place around the script. Under independent nothing is queued past a statement, so nothing is emitted or raised.

An authored data record is not bracketed: migrate never rewrites its body. Where a record’s writes queue a check under such a key and a later op of the same transaction alters, truncates, drops or indexes the table the check sits on, the script carries a -- retrofit: comment above that op and raises one deferred-keys-pending warning per record, table and op, under own and caller. End the record with SET CONSTRAINTS <schema>.<key> IMMEDIATE;, naming the key rather than ALL, so its checks run before the op. A COMMIT between the two, or a bracket line naming the key, also runs them.

SET CONSTRAINTS matches every constraint of a name in the schema, so a key sharing its name with another constraint of the schema cannot be named (a partition’s clone of the key is not another constraint). Under own that refuses with migrate-deferred-key-name-ambiguous; rename one of the constraints and migrate again.

Setting it once, in the project config

[project]
engine = "postgres"
engine_version = "17"
envelope = "caller"

The key is project-scoped. Resolution is --envelope flag → [project] envelope → the built-in default own. Unlike engine_version, the envelope has no [[schema]] tier: the envelope is a property of one emitted file, and a single migrate invocation can emit a file spanning schemas, so a per-schema value would name a boundary the artifact does not have.

An unknown value fails before anything is emitted: unknown-envelope for the flag, config-invalid for [project] envelope.

Which value for which runner

Which value fits is yours to determine. The runner is not detected: each row below holds only under its stated condition, and the condition is yours to verify. A runner whose behaviour you cannot pin to one row belongs in the partly wrapped paragraph below, not in this table.

psql --single-transaction is not supported

Do not apply retrofit output through psql --single-transaction under any envelope where retrofit writes its own wrapper, which includes the default, own.

--single-transaction opens a transaction before the file’s first statement. The file’s own BEGIN; then raises WARNING: there is already a transaction in progress and is ignored, but retrofit’s COMMIT; is not ignored: it ends psql’s outer transaction early. A statement that fails after that point no longer rolls back the earlier work, and psql still exits 0. You get a partially applied schema and a success exit code. This holds even when the file carries no mid-file boundary at all.

If you need the file to apply as one transaction under psql, emit it under --envelope=caller and open the transaction yourself, or apply it under own with plain psql -X -v ON_ERROR_STOP=1 -f and let retrofit’s own wrapper be the transaction.

Pass -X and -v ON_ERROR_STOP=1; a startup file can undo the wrapper. psql reads its startup file (~/.psqlrc, or $PSQLRC) before a -f file unless -X is given. \set ON_ERROR_ROLLBACK on there makes psql take a savepoint before every statement inside a transaction block and roll back only to it on an error, so a failing statement (a guard’s RAISE EXCEPTION included) no longer aborts the wrapper’s transaction: the rest of the file runs, its COMMIT; commits, and psql exits 0 over a partial apply. This holds under own and under your own BEGIN; \i file.sql COMMIT; wrapper under caller. ON_ERROR_ROLLBACK interactive does not apply to -f; on does. A single-span own file carries no ON_ERROR_STOP guard of its own (only a segmented file or one bounded by --lock-timeout does), and retrofit does not set psql variables in a file other runners also read, so the invocation closes this: -X skips the startup file, and -v ON_ERROR_STOP=1 halts at the first error with exit 3, the wrapper’s transaction left uncommitted.

Partly wrapped runners are out of scope

Some runners wrap some statement groups and not others, at a cut retrofit cannot see: Flyway mixed=true, golang-migrate with x-multi-statement over a file whose statements it splits, Liquibase with per-changeset runInTransaction. For such a consumer neither caller nor independent is true of the whole file, and no fourth envelope value would fix it: the value describes the file, while the cut lives in your runner’s configuration.

The action is to split the migration into separate files whose boundaries the runner cannot straddle (one file per group the runner wraps as a unit), and give each file the envelope that is true of it.

Bounding the lock wait: --lock-timeout

A DDL statement that waits for a table lock holds its own place in the lock queue, and every later query on that table queues behind it: a migration that waits behind one long report query can stall the table for everyone. --lock-timeout=<duration> bounds that wait: the file carries PostgreSQL’s own transaction-scoped setting,

SET LOCAL lock_timeout = '2s';

so a statement that cannot get its lock within the bound gives up with ERROR: canceling statement due to lock timeout (SQLSTATE 55P03) instead of waiting, and its transaction rolls back. The flag names PostgreSQL’s lock_timeout setting; it is not a wait for any lock of retrofit’s own.

<duration> is a positive integer with no sign and no leading zero, followed with no space by ms, s, min or h (lowercase), at most 2147483647ms after unit conversion: 500ms, 2s, 1min. It is emitted exactly as you wrote it (2s and 2000ms are different bytes). 0, a bare integer (PostgreSQL would read it as milliseconds), 2 s, 1d, 2S and an empty value are refused before the verb runs: flag-invalid-value (exit 2) at the flag. off means unset.

[project]
lock_timeout = "2s"

Resolution is --lock-timeout → [project] lock_timeout → unset, the same ladder --envelope rides. An invalid [project] value is config-invalid (exit 1) at load, so it fails every verb that loads the config and no flag rescues it; the value is trimmed of surrounding whitespace, the flag’s is not. --lock-timeout=off clears either tier for one run. Unset, the emitted file is byte-identical to one emitted without the feature. The flag and the key are migrate’s alone: snapshot, whose --include-drop wrapper is a separate surface, reads neither.

Where the line goes, per envelope. The setting only ever takes the SET LOCAL form: it ends with its transaction, whether that commits or rolls back, so it cannot outlive the file on the runner’s session. A plain SET or a RESET is never emitted.

Under own the ON_ERROR_STOP guard is emitted whenever a lock timeout is resolved, --into-schema and --down included, so plain psql -f stops with exit 3 at a give-up. Under caller on a runner that sends the file as one simple Query with no transaction of its own (golang-migrate’s default), PostgreSQL runs the file in one implicit transaction, and the line bounds every statement of it.

The retry depends on your runner’s bookkeeping. A rolled-back file is clean to re-run once the runner allows it: golang-migrate marks the version dirty after a failed file, so migrate force to the prior version before retrying. migrate retries nothing itself.

Per acquisition, not per statement. The bound applies to each lock acquisition, row locks included: a statement that takes several locks in turn can wait up to the bound on each, and a span holding only data can give up on a row lock.

The reach under caller. SET LOCAL lasts until your transaction ends, so it also bounds the runner’s own bookkeeping statements inside that transaction (a Flyway history row, for one), and under Flyway group=true, where every pending migration shares one transaction, every later file of the group.

What the bound does not reach. A statement outside every span runs without it: the DETACH PARTITION … CONCURRENTLY node, and non-transactional statements admitted under --allow-unscreened-dml in a band before a leading node or after a trailing node. A window made only of such nodes carries no line at all. While a lock timeout is resolved, the segmentation warning names each such statement on its own Detail line, unbounded: DETACH PARTITION <partition> CONCURRENTLY or unbounded: seq=<n> src=<origin> statement <k>, in artifact order. Statements after a data body’s own transaction control are also outside the bound, under own and caller alike. So are statements after a data body’s \c or \connect line, which reaches the artifact only under --allow-unscreened-dml (a RunRawScript op body’s is outside this screen) and ends the transaction open before it; under own the window then reports migrate-segmented-non-atomic (see Data bodies that carry their own transaction control). A data body that sets the setting itself overrides the bound: SET LOCAL lock_timeout, RESET or set_config('lock_timeout', …, true) until its span ends, and a plain SET or set_config(…, false) also onto the session.

No transaction to scope into. Under independent, a resolved lock timeout refuses the run before any artifact byte: migrate-lock-timeout-unscoped, exit 1. SET LOCAL has no effect outside a transaction block, and a plain SET would outlive the file on a pooled connection. Set the bound on the runner’s connection instead (for example PGOPTIONS='-c lock_timeout=2s'), or pass --lock-timeout=off for the run. A project that sets both envelope = "independent" and lock_timeout loads, and every migrate run refuses until one of the two flags resolves it.

Segmented scripts, and which runners they are specified against

A window is emitted as N separate segments rather than as one all-or-nothing script when it holds an operator that needs the transaction closed and a new one opened. Ordinary DDL never segments, however much of it there is, and neither does screened data on its own. Data you admit past the screen with --allow-unscreened-dml is the exception: a body carrying its own transaction control can segment a window holding no such operator at all, which is why that attestation degrades the reported count to a lower bound. See Data bodies that carry their own transaction control.

What that boundary costs (and so how many segments the window reports) is a second question, decided by which positions on each side of it can contribute a statement. An operator is only one of the kinds that can.

Operators. Some cannot run inside the transaction retrofit would otherwise wrap the whole file in, and retrofit closes its transaction before such a statement and opens a new one after it. The two kinds differ in what that costs. ALTER TYPE … ADD VALUE needs only a commit boundary before a later statement can use the new value, so a trailing one (with nothing after it to read the effect) separates no work and costs no atomicity. DETACH PARTITION … CONCURRENTLY cannot run inside a transaction block at all, so it costs atomicity wherever it sits, last position included.

Data records. A .data.sql data record occupies a position too, and occupying one is what can make an operator’s boundary separate work. Seed records (migrate --label <l>) are emitted as a trailing block after all DDL, so they land past every operator; a record anchored on the oplog’s root boundary lands ahead of every operator; and a record anchored on an operator shares that operator’s position. A screened data record places no boundary of its own; that is the operator’s doing. What a trailing seed block does is put something after the last operator: a window whose last operator needs a commit boundary segments once the seed lands past it, because the boundary that would otherwise have separated nothing now separates the DDL from the data. The DDL commits, then the data applies as its own unit.

That costs atomicity. Under --envelope=own, where retrofit places the boundaries and the runner opens none, the window emits as segments and names the cost as a migrate-segmented-non-atomic report: each boundary, and what a failure in a segment leaves durable. The all-or-nothing it loses is the window’s, not the envelope’s (ALTER TYPE … ADD VALUE followed by its use cannot be one transaction under any envelope), so nothing is asked of you to emit it.

To keep the window atomic instead, re-run over a --from/--to range that isolates the responsible operators, so the rest of the window still applies as one atomic script. On a window whose occupancy comes from the trailing seed block, that remedy is inert: the block is emitted after every operator in any window, so no narrowing can separate it from the last DDL. Under caller, whose refusal hint offers narrowing first, the hint omits it on that shape and names the offending records instead, by seq and source file:line.

Which migrate-tx-incompatible you are looking at

migrate-tx-incompatible covers more than one situation, so the refusal carries a stable class discriminator you can branch on: class: on the line after the message in text mode, discriminator in the JSON error envelope. Both classes are GroupLexParse and exit 1. Read the class before choosing a remedy; no flag answers either.

• tx-incompatible-unattested: the window needs a boundary retrofit does not own. This is the class a caller-envelope window raises (see --envelope): a data body’s COMMIT;, or an admitted \c/\connect line, draws such a boundary as an operator does. Remedies: narrow --from/--to, re-run under --envelope=own, or split the file.
• tx-incompatible-unsatisfiable-edge: there is no legal segmentation at all. One position in the window demands the transaction closed while another demands that same transaction stay open, so no boundary placement satisfies both, and a different envelope or a flag cannot help. The only remedy is to split the window so the conflicting operators land in separate migrations: narrow --from/--to, or author them as two migrations.

This class is reached through the same-batch enum rule: a window that adds an enum label with ALTER TYPE … ADD VALUE and uses that label in a later statement’s default or expression. PostgreSQL makes the new label visible at parse time but invisible to the issuing backend until commit, so the use cannot be satisfied within the authoring range whatever the boundaries. migrate does not infer the split; author it as two windows. Which class such a window reports depends on the envelope, because the two classes are checked in order and the unattested check comes first:

• under --envelope=own and --envelope=independent, the unattested class never fires and the window reports tx-incompatible-unsatisfiable-edge directly;
• under --envelope=caller, the unattested class fires first, and tx-incompatible-unsatisfiable-edge shows once you re-run under own.

In none of these does anything but splitting make the window emittable.

The advisories a successful segmented emit carries. Segmentation is not always a refusal, and the emitting paths announce themselves on stderr rather than silently:

• migrate-segmented-non-atomic: the file is not all-or-nothing. Raised on every segmented --envelope=own emit, and on every --envelope=independent run whose window needs a boundary. Exit 0; the detail names what was spent. --allow-external-atomicity does not retire it under own or independent: the atomicity lost there is the window’s, not the apply context’s.

• wrapper-suppressed: no BEGIN;/COMMIT; wrapper was emitted for this file at all. Raised whenever something suppressed the wrapper and you have not answered why, which any non-own envelope does. The detail names every input that suppressed it, not just one: drop a single input where two apply and the wrapper still does not come back. The file relies on your apply context for atomicity. Under independent the detail also states, once per file, the two assumptions the obligation line states: the runner stops at the first error, and no other session writes the tables the file rewrites.

  Two things stop it reaching stderr, and they are not the same. --allow-external-atomicity attests that your apply context owns transaction atomicity, which answers the advisory: it is not raised at all, in either mode, and the terminal JSON envelope records the attestation under data.external_atomicity_attested. The attestation answers extract-reverse-guard-non-atomic the same way. That report is raised when a file with no wrapper places a guard (a --down guard, or a forward inline’s coverage check) after a statement that changes the database. It states that nothing frames the file in one transaction, which is the fact attested. Separately, -qq drops the whole advisory tier from the text surface; the advisory is still raised and --as=json still carries it.

Both are advisories on a successful run, not refusals: a CI job that treats a non-empty stderr as failure will trip on ordinary caller and independent invocations.

A CI job that greps stderr for wrapper-suppressed is reading a weaker signal than it looks. Its absence means the wrapper was emitted, or the fact was attested, or the tier was quieted, or the write was lost. See the output contract’s “Advisory absence is not a negative claim”. Read --as=json if you need the machine answer.

Data bodies that carry their own transaction control

migrate counts the segments it emits, and the number it reports must match the number the script actually applies as. A data body carrying its own COMMIT;, BEGIN; or ROLLBACK; (or a statement PostgreSQL bars from a transaction block, such as VACUUM or CREATE INDEX CONCURRENTLY) would open a unit retrofit never counted, so the reported number would understate the real one.

Every spliced body is therefore parsed and screened before it is emitted. A body carrying such content is refused (migrate-dml-unscreened), naming each offending record’s seq and source file:line. The screen reads parsed statements, never text: a COMMIT inside a string literal, a dollar-quoted $$ROLLBACK;$$ and a -- COMMIT; comment are all fine, and the body is emitted byte-for-byte as authored; screening never rewrites it. Ahead of the parse, one raw-line pre-pass reads the body as psql does and finds every line on which psql starts a psql meta-command (\connect, \i, \echo, pg_dump’s \restrict, …), outside every string, comment and closed COPY … FROM stdin data block.

A body carrying a psql meta-command line is refused on its own arm (migrate-dml-unscreened, discriminator dml-body-meta-command), one Detail line per line, form=psql meta-command <token> (body line <k>). psql runs such a line client-side, outside retrofit’s framing, so the remedy is to delete the psql meta-command lines: rows a \connect aimed at another database belong in that database’s project, and a file \i included belongs inlined as SQL; pg_dump’s \restrict/\unrestrict lines are not data. A body whose parse would read a different body than psql runs refuses on the unparseable arm (dml-body-unscreenable).

Pass --allow-unscreened-dml to emit anyway. It covers the whole invocation, and it changes what the segment count means: retrofit reports “at least N segments” rather than an exact number, because a body it could not decide may open more.

Two forms are admitted rather than refused, with an advisory (dml-body-partition-dependent): REINDEX INDEX/REINDEX TABLE and CLUSTER <table> are barred from a transaction block only when the target relation is partitioned. That is catalog state, which migrate does not read, and the parsed forms are identical either way, so the advisory names the record instead of refusing it. If the target is partitioned, the window applies as one more segment than reported.

A psql meta-command line admitted under --allow-unscreened-dml is never silent: the run emits one dml-body-meta-command-admitted advisory per line, beside the dml-body-partition-dependent account above, and the artifact carries one -- WARNING: psql meta-command line admitted under --allow-unscreened-dml: <token> (body line <k>, seq=<n> src=<origin>) comment per line above the body, which itself emits untouched. An admitted \c/\connect line ends the transaction open before it, as a body COMMIT; does: under own the window segments and reports migrate-segmented-non-atomic, and under caller it refuses migrate-tx-incompatible.

A segmented script, and any file bounded by a lock timeout (--lock-timeout), carries an ON_ERROR_STOP guard as the first line of its body: a failure or a lock-timeout give-up halts the run with exit 3 instead of running every remaining segment and exiting 0.

Supported runners

A segmented script is specified against runners that do not open a transaction of its own around the file:

Neither unsupported row errors out: atomicity is lost at exit 0. See psql --single-transaction is not supported.

Recovering from a partly-applied segmented script

Because the segments commit independently, a failure in segment k leaves segments 1 through k−1 committed and durable. Re-emitting produces a byte-identical script, so the migration is not re-runnable: the already-applied segments would fail on the retry. Recovery means inspecting which segments applied and hand-authoring the remainder.

Two modes: in-place vs --into-schema

In-place (default)

Replays the (from, to] op range as DDL diffs against the schema the cut already addresses. Use this when the live database is already at --from and you want to land it at --to.

migrate has no --include-drop flag. Its DROP emission is driven by the ops in the window, not by a flag: DropTable, DropColumn, and DropIndex always emit their DROP, and so does DropFunction (see What migrate sees).

A DROP in the script does not always mean an element was retired. Rebuild operators use one internally: ReplaceMaterializedView, for instance, emits DROP MATERIALIZED VIEW … CASCADE followed by the CREATE that reinstalls it.

To tear a schema down and rebuild it from scratch (a reflash rather than a delta), use snapshot, which owns that surface:

retrofit snapshot --at <opid> --include-drop=scoped   # per-element DROP IF EXISTS, then CREATE
retrofit snapshot --at <opid> --include-drop=schema   # DROP SCHEMA … CASCADE, then CREATE

The prologue is coherent there because snapshot re-creates everything it drops. A migrate script only carries the delta, so the same prologue would drop objects the script never re-creates.

--into-schema <schema> (copy script)

Emits a script that creates a fresh copy of the --to cut under a different schema name, beside the live schema. Use the copy as a sandbox for a window that moves no rows: a window that moves rows refuses, and the copy is not a full-fidelity clone. The list below names what it omits or refuses.

A copy that holds what the in-place run would leave is not available.

The source schema (the cut’s existing schema name) is selected via the resolver chain. Pre-replay multi-schema detection refuses with into-requires-schema if the oplog has more than one CreateSchema and the resolver could not pick one.

The copy first creates each user-defined type (enum or composite) live in the --to cut, so its columns, defaults, checks and composite fields name the copy’s type rather than the source schema’s. It then creates each table with its constraints and fills it from the source table with the same element identity, reading a value of a user-defined type through text. Each identity sequence is then aligned to the copied rows, so the next insert does not collide with them. Everything else is omitted or refused:

• Post-data facets are omitted. Secondary indexes, replica identity, RLS state, policies, and comments are absent. The --down --into-schema copy omits the same facets. The in-place flavor restores them.
• Functions and triggers refuse. A function or trigger live in the --to cut (one created before --from included), or a function or trigger operation in the window, refuses the whole migrate with migrate-into-schema-function-trigger-unsupported, rather than emit a copy that silently lacks it.
• Views and materialized views refuse. One live in the --to cut refuses the whole migrate with migrate-into-schema-unsupported-family, rather than emit a copy that silently lacks it.
• A partition tree with an attached partition refuses. The copy creates every table plain and reads a partitioned table together with its partitions’ rows. A partition that owns its columns (one bound by AttachPartition, or imported from a dump) under a partitioned table, as the copy’s source holds them, would have its rows copied twice: into the parent and into its own table. That refuses the whole migrate with migrate-into-schema-unsupported-family. A partition created by CreatePartition or PartitionTable takes its columns from its parent, and its rows are copied once, through the parent.
• Foreign keys to another schema refuse. An FK that references a table outside the source schema refuses with into-cross-schema-fk-unreachable.
• Types the source schema does not manage are not copied. A column or composite field of an extension type, or of a type in another schema, stays bound to that type, so the copy depends on the schema that holds it.
• Row motion refuses. A window holding a row-moving operation (CopyColumn, CopyTable, ExtractTable, JoinTable, MergeTable, SplitTable, a keyed MoveColumn) or a CastColumn with using= refuses the whole migrate with migrate-into-schema-row-motion-unsupported. Stop --to before that operation.
• Enum label renames on copied columns refuse. A RenameEnumValue in the window, on an enum that a copied column uses (directly, as an array’s element type, or through a composite’s fields), refuses with migrate-into-schema-row-motion-unsupported. The copy creates the enum with its renamed labels and reads each source value through text, so it cannot carry a row holding the old label. Stop --to before the rename.
• Detaching a born partition refuses. A DetachPartition in the window whose partition takes its columns from its parent (one created by CreatePartition or PartitionTable) refuses with migrate-into-schema-row-motion-unsupported. The detach gives the table its own columns under new identities, so the copy would create it empty while its rows land in the parent’s copy. A partition bound by AttachPartition keeps its columns, and its detach passes this refusal, but the copy’s source still holds it under its parent, so it refuses as an attached partition (above). Stop --to before the detach.
• The data corpus is not consulted. A reference, transition, cleanse or unclassified record in the window refuses with migrate-into-schema-row-motion-unsupported. A seed record in the window is not emitted, even when --label names its dataset.
• Raw script bodies are not rewritten. Each RunRawScript block follows the copy verbatim, under the copy’s search_path (see below), but a name qualified with the source schema still reaches the source, so a block can write live data.

To keep what the copy omits, or to get past a refusal, rehearse in place on a restored copy or branch of the database (for example pg_dump | pg_restore into a scratch database, then migrate in place there).

A RunRawScript in the window runs after the copy, in a trailing raw section led by SET LOCAL search_path = <schema>; (a session SET under --envelope=independent, which has no transaction to scope it to), so the block’s unqualified names resolve in the copy, not the source. Only the copy’s schema is on that path: pg_catalog is still searched, but an unqualified call into public fails rather than reaching back. Each block’s -- WARNING: lists the source-qualified relations the parser found in it, or says the body could not be parsed; function, type and dynamic-SQL (EXECUTE) references are not checked. A window with no RunRawScript sets no search_path.

Splitting an optional attribute into a sparse child table

A hand-written split often moves an optional attribute into its own table and keeps that table sparse: a child row only where the attribute is present. ExtractTable does not split that way. Its child that copies the source primary key takes a row for every source row, including rows whose moved columns are all NULL, so on a populated table a moved column can never take NOT NULL. For a sparse child, author the split as a keyed MoveColumn per column.

The worked example is the shape of Concourse’s separate_pins_from_resources migration. It takes a resource’s pin out of

CREATE TABLE resources (
  id integer PRIMARY KEY,
  name text NOT NULL,
  api_pinned_version jsonb,
  pin_comment text
);

into resource_pins (resource_id, version jsonb NOT NULL, comment_text text NOT NULL), keyed by resource_id and holding a row only for a pinned resource.

1. Create the child and its key. The key column, its NOT NULL and primary key, and a foreign key back to the source. Choose on_delete for the relationship; Concourse cascades.

retrofit op add CreateTable public resource_pins
retrofit op add AddColumn public.resource_pins resource_id integer
retrofit op add AddNotNull public.resource_pins.resource_id
retrofit op add AddPrimaryKey public.resource_pins resource_pins_pkey --column=resource_id
retrofit op add AddForeignKey public.resource_pins referenced_table=public.resources \
  on_delete=cascade name=resource_pins_resource_id_fkey --column=resource_id --referenced_column=id

2. Clear the values the domain considers invalid. A pin_comment on an unpinned resource has no pin to belong to, and Concourse discards it. The operators do not decide that, so author it as its own step, ahead of the moves, and say what it drops. Its reverse is empty: on --down the cleared comments do not come back.

retrofit op add RunRawScript \
  'DO $$ BEGIN UPDATE public.resources SET pin_comment = NULL WHERE api_pinned_version IS NULL AND pin_comment IS NOT NULL; END $$;' \
  'reverse_body=DO $$ BEGIN END $$;' \
  'reason=drop the pin_comment of every unpinned resource'

3. Move each column along the key.

retrofit op add MoveColumn public.resources.api_pinned_version public.resource_pins --key_pair="from=id,to=resource_id"
retrofit op add MoveColumn public.resources.pin_comment public.resource_pins --key_pair="from=id,to=resource_id"

The pair runs from the source primary key to the child key, so each move is the anchored one-to-one arm toward the dependent. A value with no partner row gets one: the move inserts a child row only where the moved value IS NOT NULL and the child holds no row for that key yet, and reports it as a padded N row(s) notice. It then carries each value, proves the carry, and drops the source column. A source row with no value gets no child row. migrate --down moves every value back.

4. Rename to the target shape, then add NOT NULL.

retrofit op add RenameColumn public.resource_pins.api_pinned_version version
retrofit op add RenameColumn public.resource_pins.pin_comment comment_text
retrofit op add AddNotNull public.resource_pins.version
retrofit op add AddNotNull public.resource_pins.comment_text "fill=''"

fill= is the hand-written COALESCE(pin_comment, ''): it rewrites the NULLs left to '' before SET NOT NULL. That is a declared certain (depends) forward loss (op add warns data-loss-on-apply), and the down leaves the '' in place.

Without step 2 the child is not sparse in version. The pin_comment move pads a child row for each unpinned resource holding a comment, and that row’s version is NULL. AddNotNull on version then fails at apply: PostgreSQL refuses with column "version" of relation "resource_pins" contains null values. Add that NOT NULL between the two moves instead and the pin_comment move refuses first, before it writes a row, with migrate-move-column-pad-blocked and the hint to clear the unpartnered values. Either way, step 2 is the way out. Nothing in retrofit drops those comments for you.

Rolling back. A --down across the whole window refuses with migrate-non-reversible, because the renames and NOT NULLs change facets of the moved columns that the moves read. Its hint names the two-step rollback at the last MoveColumn.

What migrate refuses, and why

migrate renders only the operators on its allow-list, where snapshot renders every replayable op. An op outside the list refuses with migrate-blocked-on-unsupported-smo, so a window never renders as an empty script.

The allow-list covers the in-scope set: schema / table / column / constraint / index lifecycle ops; attribute updates (SetDefault / DropDefault / replica identity / comments / identity generation / deferrable bits / DropGeneratedExpression); constraint renames and ValidateConstraint; type, extension, plain-view, materialized-view, policy/RLS, and declarative-partitioning ops; most of the function/trigger family (below); the data-motion ops (below); RunRawScript; and Seal (filtered to a no-op).

Functions and triggers

The function/trigger family is in scope and emits. Eight operators have renderers:

• Functions: CreateFunction, ReplaceFunction, RenameFunction.
• Triggers: CreateTrigger, DropTrigger, RenameTrigger, EnableTrigger, DisableTrigger.

Three shapes refuse:

• A ReplaceFunction that renames an input parameter refuses the whole migration with migrate-replace-function-parameter-rename: PostgreSQL will not change a named input parameter’s name (or drop it) through CREATE OR REPLACE FUNCTION (42P13). Author a DropFunction then a CreateFunction with the new names instead. Giving a formerly unnamed parameter a name is allowed and renders.
• A ReplaceFunction that changes the output row (an OUT, INOUT or TABLE column’s name, type, order or count) refuses the same way with migrate-replace-function-return-row-change: that row is the function’s return type, which PostgreSQL will not change in place (42P13). Renaming a function’s only output column is allowed.
• Under --into-schema, every member of the family refuses with migrate-into-schema-function-trigger-unsupported (see --into-schema).

Outside those three cases the family renders like any other in-scope operator.

Data-motion SMOs

The data-motion operators are emitted:

• Structural (table-level): CopyTable, ExtractTable, JoinTable, SplitTable, MergeTable.
• Column moves / copies: MoveColumn, CopyColumn.

These render destructive PG DDL (CREATE TABLE … LIKE / AS SELECT, INSERT … SELECT, and a DROP of the consumed source) with a -- destructive: (or -- destructive-conditional: for predicate-bearing joins) marker above the statement. migrate is a generator, not an executor: it emits the script; applying it is your pipeline’s job (principle #1).

Two MoveColumn shapes refuse. A constraint-bearing MoveColumn (the source column is the source of a live FK) refuses with migrate-move-column-fk-source-deferred: moving it is not available. A move of a column that is NOT NULL without a DEFAULT at the move refuses with migrate-move-column-needs-default, in place and under --down alike: the destination rows would have no value, so PostgreSQL would reject the SET NOT NULL at apply. Set a DEFAULT before the move, or drop NOT NULL and re-add it after seeding the destination. The simple unreferenced shape emits.

Preserved DML: the data corpus consumer

When import set custom DML aside into per-table data files (<schema>.<table>.<YYYYMMDDHHMM>….data.sql, see import.md), migrate reads the corpus back automatically, with no flag: every addressed *.data.<format> under the schema’s declared data_root is discovered (.data.sql; a legacy *.dml.sql is still read, and only an obsolete header refuses with data-file-malformed). It splices each record into the emitted script by its purpose:

• ref: production reference data from the base corpus; always applied (never label-filtered), spliced at its anchor.
• transition: spliced inline, right after its anchor-op’s DDL, before the next op.
• cleanse: spliced like a transition, but wrapped in a -- retrofit:dml run-once marker so a re-applied migration won’t repeat it.
• seed: opt-in, and the opt-in is the dataset’s name: purpose=seed records are emitted only when a --label <label> names their dataset, as one trailing block after all DDL, in ascending seq. --label is repeatable and ordered: each occurrence names one layer, and a later layer’s disposition=replace floor supersedes the layers below it for that table. Records no --label named are excluded and counted table-by-table in a stderr advisory, cause not-selected. --allow-unnamed-label <label> (repeatable, byte-exact) attests that the dataset carrying that label is left out on purpose, such as a test-only label on a long-lived target. The run-level warning then drops that label and fires only while another unnamed dataset remains. An attestation that answers nothing prints a note that it had no effect. snapshot.md has the details.
• unclassified: refused by default (dml-unclassified, exit 2). --allow-unclassified-dml downgrades the refusal to a warning and emits each unclassified body as a trailing unclassified-DML WARNING block.

An in-window disposition=replace block (the table’s full observed row set) is emitted with a synthesized prologue so it lands as the complete state; within one (table, label-set) chain, the latest replace at-or-before the --to cut supersedes earlier blocks. The prologue is a property of the table’s resolved floor, derived once from the layer fold. Its form depends on the table’s key, read at that script position:

• The candidates are the primary key, then the UNIQUE constraints, then the unique btree indexes with no WHERE clause and no expression column. Key columns may be nullable. A DEFERRABLE UNIQUE constraint is never a candidate: PostgreSQL cannot use it as an ON CONFLICT arbiter.
• The key is the first candidate whose every column the captured rows write. A candidate the rows do not spell in full does not count.

For example, take accounts (id serial PRIMARY KEY, email text UNIQUE, name text). Rows that write (id, email, name) are keyed on id; rows that write only (email, name) are keyed on email; rows that write only (name) cover no candidate.

• Staged block: creates a TEMP table LIKE the target (pg_temp."retrofit_replace_<schema>_<table>"), loads the captured rows into it, upserts them into the target on that key, deletes every current row whose key the staged state does not hold, re-seats any identity/serial sequence, and drops the temp. Rows already in place are updated only where they differ; the target is never emptied between statements, and a referencing table’s rows are never orphaned by an intermediate state. When a key column is nullable, the block first deletes the target’s rows with a NULL key and re-inserts them from the captured rows, because a NULL key never matches. When a DEFERRABLE UNIQUE constraint has exactly the key’s columns, the upsert names the key’s constraint (ON CONFLICT ON CONSTRAINT <name>) rather than listing its columns.
• Whole-table clear and staged reload: DELETE FROM <table>;, then the captured rows are loaded through the same TEMP table and inserted in one statement. The marker comment names the cause: no key, key present but not usable as an `ON CONFLICT` arbiter (every unique constraint the table has is DEFERRABLE), or key present but not covered by the body. A column the table had at the record’s anchor that the rows do not write is set to NULL, and a data-replace-whole-reload warning on stderr names those columns.
• Bare clear: a body=none floor, a reading body admitted under --allow-reading-replace-body, or a body that cannot be staged renders a whole-table DELETE FROM <table>; clear ahead of the rows, with the marker comment naming the reason.

Rows that render outside a staged block (beneath a bare clear, or from an append record or a ref record with no prologue) are followed, after the table’s last such row in the band, by the same identity/serial re-seat the staged block runs, so the next insert that omits the key does not collide with a captured row. Transition and cleanse bodies get no re-seat; their sequence effects are their author’s.

Both refusals exit 2:

• key-uncovered rides the stale-dml-reference envelope, one reason=key-uncovered line per table naming its candidates and the columns the rows lack (arbiters=… missing=…, or arbiters=none). A table whose rows carry no column list (a headerless COPY, a positional INSERT … SELECT), or a position whose schema cannot be read, refuses the same way. --allow-stale-dml does not downgrade this reason. The hint reads capture <schema>.<table> fresh at HEAD (retrofit drift <source>, or retrofit data capture --from-label <label> --schema <schema> --table <table>), then either , or add the missing columns (…) to the record's body or, when a missing column is generated, identity or serial, , or give <schema>.<table> a natural-key UNIQUE the body can carry.
• replace-no-upsert-form: table "<table>" has no ON CONFLICT arbiter: unique index "<index>" shares its columns with DEFERRABLE UNIQUE constraint "<constraint>"; PostgreSQL has no ON CONFLICT form that selects that index. The hint reads add a non-deferrable UNIQUE constraint on (<columns>) or drop DEFERRABLE from "<constraint>", then capture <table> at HEAD.

retrofit data witness previews the same rule for each replace record it witnesses at the record’s anchor (the position its floor renders at) and at HEAD: preview (anchor <opid>): <schema>.<table> and preview (HEAD): <schema>.<table>, each followed by keyed on (<columns>), uncovered, no-arbiter or coverage unknown. It refuses replace-no-upsert-form itself at either cut. The anchor’s answer is the one migrate acts on; the HEAD answer previews the floor a fresh capture would mint, and follows the record’s table ELEMENT rather than its name: a table renamed after the capture is previewed under its HEAD name, its body’s columns bound where the body named them (at the anchor), never to a later table that took the old name; a table that ended after the capture (dropped, joined, extracted) gets the anchor preview alone, whatever a later table under the same name looks like. An anchor refusal’s message reads at anchor <opid>, table "<table>" has no ON CONFLICT arbiter … and its hint adds that an unchanged capture still mints the new floor at HEAD. retrofit op seal runs the anchor check over every reference replace anchored in the range it is about to seal, and refuses the same way (its hint ends then retrofit op seal), because a sealed anchor’s catalog never changes; import --seal and drift --seal run the same gate. A floor a later floor of its chain supersedes (a fresh capture, or a data retire) never renders, so neither verb judges it, at its anchor or at HEAD.

Within one script position the prologues render clears children before parents (the FK topology at that cut), staged blocks parents-before-children on the upsert leg, and the clears/blocks ahead of every row of the same position. Tables in a foreign-key cycle are ordered as one group; when every member is staged, each step of the group renders as one WITH statement. When the cut cannot be read, the order degrades to a deterministic tie-break (anchor, then table id) and the position is led by a -- WARNING: comment; the script is still emitted.

Apply a script carrying a staged block with errors stopping the run; the applying role needs the TEMP privilege on the database. The transaction follows the envelope. Under own (the default), apply with psql -X -v ON_ERROR_STOP=1 -f <script>: the file’s wrapper is the transaction, so a failed phase rolls back the span it runs in. Do not add -1 under own (see psql --single-transaction is not supported). Under caller, apply inside the transaction you open, with psql -X -v ON_ERROR_STOP=1 -1 -f <script> or your own BEGIN; \i <script> COMMIT;. Under independent each of the block’s statements commits on its own, so a failed phase leaves the statements before it applied. Two cautions: a secondary UNIQUE constraint the arbiter does not cover can still reject an upsert whose new row collides with a row the delete leg has not yet removed (re-run the same script after clearing the collision: the block’s DROP TABLE IF EXISTS opener absorbs a stranded temp); and a surrogate key re-minted at capture (a serial the source regenerated) matches nothing in the target, so the block replaces every row rather than updating in place. A table referenced by another table’s ON DELETE CASCADE foreign key is reported on stderr (data-replace-cascade) before the clear reaches it.

A range that crosses a reference table’s declaration removal (the window holds the schema ref-tables remove that ended the span the floor is anchored in: a redeploy after a rollback, or a deploy from before the removal to past it) renders that table’s floor as a keyed delta against the fold at the range’s start, never its full observed state: after the removal the table’s rows are the application’s. The staged block upserts the floor’s keys as above, but its delete leg deletes only the keys the corpus itself dropped since the range’s start: DELETE … WHERE <key> IN (CAST(… AS <key type>), …) AND NOT EXISTS (…), each key stated as its INSERT stated it (a string quoted, a number or boolean bare) and cast to the key column’s type, so it converts as that INSERT did, never a key the staged state holds (compared by the database’s equality, so 1 and '1' are one key), and nothing when it dropped none. A nullable key’s NULL-key rows are not cleared. The marker reads -- retrofit:data replace: keyed delta of <table> at anchor <opid>; its reference declaration ends at op <opid> inside this range, and at apply a DO block raises NOTICE: retrofit: kept N row(s) of <table> the corpus state does not hold when the target holds rows the state does not. stderr carries data-replace-crossing in place of data-replace-prologue, naming the deleted keys. When a record of the chain does not fold offline (an UPDATE, DELETE, MERGE or INSERT … SELECT body, or one leaving a column DEFAULT), keys a column cast since, or states a removed key by an expression such as nextval(…), the removed keys cannot be computed: the block upserts only and the warning names the record. A floor with no usable key (a keyless table, body=none, a whole-table reload, an admitted or unstageable body) cannot render a delta: it renders no clear, its rows apply as the records state them (a target already holding them may refuse or duplicate them), and the warning says why. A window that ends before the removal renders the full state as usual, and a window past it holds no floor, so one window or two reach the same rows for every key the corpus names.

Replace under row security, row triggers, or DO INSTEAD rules. Every row-removing leg of the prologue is a plain DELETE run as the applying role: the whole-table clear, the NULL-key delete of a nullable key, the staged block’s delete leg, and a keyed delta’s delete. Apply the script as a role that is subject to the table’s row-level-security policies (FORCE ROW LEVEL SECURITY with an owner that is not a superuser and lacks BYPASSRLS, or a non-owner), or against a table carrying row triggers or ON DELETE … DO INSTEAD rules, and those DELETEs skip the rows the policy hides or the trigger or rule diverts. They still exit 0, so the apply succeeds and the table is not the replace state. A keyed delta’s kept N row(s) NOTICE then can undercount, because it is a separate SELECT count that sees only the rows the policy lets that SELECT read. What follows depends on the prologue’s form:

• Bare clear (keyless table, body=none, admitted or unstageable body): the rows the clear skipped stay, then the captured rows are inserted beside them, so the table holds silent duplicates.
• Whole-table clear and staged reload: the reload is a plain INSERT, so a surviving row that collides on a unique key fails the apply loudly; a table with no unique key duplicates silently, as above.
• Staged block (keyed): the upsert still runs, but the delete leg misses, so stale rows silently survive beside the replace state.

The recourse is at apply time and differs by cause. For row security, apply as a superuser or a BYPASSRLS role, or disable the policy for the load. A privileged role does not bypass row triggers or DO INSTEAD rules, so a trigger or rule that diverts the DELETE must be disabled for the load whichever role applies it. Restore whatever you disabled afterward. migrate emits no check for this.

Before emitting a record, migrate repairs its references in the emitted body: a table or column rename resolves to its name at the render cut, a write-only reference to a dropped column is removed, and records for a dropped table are excluded. Each repair is reported in the data-band stream; the data file on disk is unchanged. A reference whose meaning cannot be preserved still refuses (stale-dml-reference, exit 2); --allow-stale-dml downgrades that case to a warning and emits the body under a stale-reference WARNING comment. The key-uncovered reason above is not downgraded. See Stale data references and repair. Anchor edges: a record anchored behind the window is omitted with an advisory (dml-behind-window, naming the record’s purpose; repeats sharing one purpose and anchor collapse to a counted line, and --no-consolidate restores the full list); one anchored ahead of --to refuses (dml-anchor-unresolved) unless --allow-unreached-dml omits it under a dml-ahead-of-window warning; an anchor the oplog does not hold always refuses; re-pin the file with retrofit data repin. Window membership is seal cover: a record anchored on a Seal line sits just after it, in the range the next seal covers, so --from that seal holds it and --to that seal (the release) does not; last holds every record. A declared data_root that does not exist refuses with data-path-missing; an absent undeclared default or an empty corpus directory is the normal no-corpus case (nothing to splice).

View operators

In in-place mode, CreateView, ReplaceView, RenameView, and DropView emit one statement each: CREATE VIEW, CREATE OR REPLACE VIEW, ALTER VIEW … RENAME TO, and bare DROP VIEW, respectively.

PostgreSQL permits CREATE OR REPLACE VIEW only when the replacement keeps the existing columns with the same names, order, and data types; new columns may be appended at the end. migrate enforces the declared column-name prefix while replaying ReplaceView; PostgreSQL validates type compatibility when the generated migration is applied.

This is the in-place diff contract. --into-schema does not replay these view-operation statements; it follows a separate copy path that materializes tables, constraints, and data from the terminal cut. Because that copy has no view arm, --into-schema refuses with migrate-into-schema-unsupported-family whenever a view or materialized view is live in the --to cut, rather than emit a copy that silently omits it.

PG18-gated operators: pg18

SetConstraintEnforced and SetConstraintInherit need PostgreSQL 18: under a 17 target the cut fails with unsupported-on-engine-version. At 18, migrate renders neither. Forward, a window holding one refuses migrate-blocked-on-unsupported-smo, and so does a --down that would set a key’s enforceability back. A --down over a window holding SetConstraintInherit refuses migrate-non-reversible, because no down step inverts it. PostgreSQL versions gives every verb’s row at each major.

Failure modes

Grouped by stage:

Flag

• into-requires-schema: --into-schema was passed against an oplog with multiple schemas and no --schema flag.
• into-schema-conflict: --into-schema=<name> collides with a schema already registered in the to-cut.
• flag-conflict: --at was passed without --down (forward, --from is the database’s cut), or --to was passed under --down without an explicit --at or --from-dump; the Hint names --at B for a database at B and --at <tip> --to B for a slice.
• from-dump-mutex: --from-dump names the window slot the database occupies, and that slot cannot also be stated by hand: forward it conflicts with --from, under --down with --at.
• unknown-envelope: --envelope names a value outside the closed set (exit 2).
• label-empty: --label was passed empty or whitespace-only (exit 2).
• flag-retired: a retired flag was passed (--strict, --allow-non-atomic-segments or --allow-unsafe-drops), with or without --down (exit 2); drop the flag.

Engine

All three engine refusals are GroupUserEnv and exit 2.

• unknown-engine-name: --using engine doesn’t match the project engine, or the flag is malformed (no : separator, empty engine half).
• unknown-engine-version: --using version not in this build’s supported set, or the version half is empty.
• unsupported-on-engine-version: the resolved engine version cannot emit ops in the cut (e.g. PG18 ops with a PG17 pin).

Cut resolution

• opid-not-found: --from / --to prefix doesn’t resolve.
• from-not-found: --from resolved to an opid, but no op in the loaded oplog carries it. Distinct from opid-not-found, which fires when the prefix itself doesn’t resolve.
• opid-before-horizon: --from resolves below the sealed horizon.
• ambiguous-opid: --from / --to prefix matches more than one opid.
• range-inverted: --from is after --to.
• fork-in-range: an unreconciled fork lives inside (from, to]. Hint points at retrofit reconcile.
• cut-error: generic cut-resolution failure surfaced from the validator.

--from-dump resolves the cut the database is at by identifying a pg_dump file (the from-cut forward, the to-cut under --down), so migrate also reaches the identify refusals:

• kind-mismatch: the dump is not a .sql file path, -, or a stream: a URL scheme, a non-.sql extension, or an extension-less regular file (exit 2, Param source). The hint names each accepted form.
• source-empty: the dump is a stream that delivered zero bytes, the signature of a producer that failed upstream (exit 2, Param source).
• dump-read-error: the dump path could not be read. A conninfo-shaped argument is read as a file path, never as a live connection (exit 2).
• dump-parse-error: the dump does not parse as SQL, or could not be normalized against the active schema.
• from-dump-no-match: no cut matches the dump, no operation follows its nearest cut, and the divergence changes no element an unsealed operation created or changed: a change made outside retrofit to a database at last’s schema. The envelope names the nearest cut and renders the residual; the hint names drift --dry-run (exit 2). The nearest cut is chosen among the cuts the search compared, every Seal cut and every cut after the last Seal. When drift would plan nothing against the nearest cut (identify’s comparison counts a difference drift’s does not), the detail lists the facets that did not pair, one cut only: or dump only: line each, and the hint is a note: that names no drift and offers the nearest cut by hand: migrate --from (--at under --down) in place of --from-dump.
• from-dump-behind: no cut matches the dump, and operations, sealed or not, follow its nearest cut. The detail lists each one after the residual (sealed or unsealed, and destructive for a drop); JSON carries them as ops_after (opid, operator, target, sealed, destructive). The hint names no drift, which diffs against last and would author their reversal: a database that takes unsealed operations is rebuilt from the log with retrofit migrate --from root --to last into a fresh database, its rows saved first, and one that takes only sealed operations runs nothing (exit 2). When drift would plan nothing against the nearest cut, the detail lists the facets that did not pair, as for from-dump-no-match, and in place of those two branches the hint says the comparisons disagree and offers the nearest cut by hand.
• from-dump-working-copy: no cut matches the dump, nothing follows its nearest cut, and its divergence changes elements unsealed operations created or changed; the message names their opids. The hint is from-dump-behind’s, and names no drift (exit 2).
• from-dump-schema-absent: the dump holds neither the schema nor any of its objects, so the database is at root, which no dump is identified at. A non-public schema only: every database holds public. Replace --from-dump with --from root, which emits the CREATE SCHEMA; under --down there is nothing to reverse (exit 2).
• from-dump-ambiguous: the dump is structurally equal to more than one cut; replace --from-dump with that cut stated by hand: --from <opid> forward, --at <opid> under --down (exit 2). Its discriminator is from-dump-ambiguous-equal-cuts (two or more seal runs, or no seal), or from-dump-ambiguous-run-not-convergent when the dump matches one seal run but the window’s data replays differently at its seals, or the slot’s explicit operand falls inside the run.
• identify-internal-error: identification failed mid-scan.

An empty but well-formed window is not a refusal: it is a disclosed no-op, exit 0 (see What migrate sees).

Emission

• migrate-blocked-on-unsupported-smo: an op in the range is not on the renderer allow-list (for example, SetConstraintEnforced). Detail names the operator and opid; hint says to stop --to before that op, hand-write its DDL, then resume with --from at it.
• migrate-move-column-fk-source-deferred: a constraint- bearing MoveColumn lives in the range; moving it is not available.
• migrate-move-column-needs-default: a MoveColumn in the range moves a column that is NOT NULL without a DEFAULT at the move, so the destination’s existing rows would have no value. Fires in place and under --down; not under --into-schema, whose copy adds no column to a populated table. Detail names the opid, the column and the destination; hint names SetDefault before the move, or DropNotNull then AddNotNull after seeding.
• import-move-column-fold-mismatch: raised by import, not by a migrate run; see import.md.
• migrate-replace-function-parameter-rename: a ReplaceFunction in the range renames or un-names an existing named input parameter (in, inout or variadic, compared by position), which PostgreSQL refuses in place (42P13 cannot change name of input parameter). Whole-migrate refusal, in place only; the message names the signature, the opid, the position and both names. Hint: author DropFunction then CreateFunction (replay refuses the drop while a live element calls the function), or keep the name and change only the body.
• migrate-replace-function-return-row-change: a ReplaceFunction in the range changes the output row its out, inout or table parameters declare (a column’s name, type or mode, compared by position, or the column count), which PostgreSQL refuses in place (42P13 cannot change return type of existing function). Renaming the only output column is allowed. Whole-migrate refusal, in place only; checked after the input-parameter rename. The message names the signature, the opid, the column position and both columns. Hint: author DropFunction then CreateFunction (replay refuses the drop while a live element calls the function), or keep the output columns and change only the body.
• migrate-non-reversible: --down over a window the ghost cannot invert, or a --down --into-schema window whose copy would not match every row (exit 1, nothing rendered). The detail lists each block, naming the operation and the element; each block’s Hint names a way back, such as --from <op> to leave the operation outside the window, or a compensating forward. See --down.
• migrate-deferred-key-name-ambiguous: under --envelope own, a foreign key the bracket sets immediate shares its name with another constraint of its schema, so SET CONSTRAINTS cannot name it (exit 1, nothing rendered). The detail names the schema, the name and the tables that hold it; the Hint is to rename one of them. A partition’s clone of the key never counts. See Deferred foreign keys around a row-writing op.
• A guard raised when a down is applied is not a migrate refusal: the script renders, and PostgreSQL raises one of the operator’s apply-time guards (retrofit: <id>: …, SQLSTATE P0001) on an operation the down computed, such as the MergeTable that undoes a SplitTable. The guard prints the same Hint as on an operation you wrote. Its first way out (an operation authored before the operator, or a DisableTrigger/EnableTrigger bracket around it) works only on an operation you wrote: nothing can be authored ahead of a down’s operation or around it. The same operation authored now lands after the window’s: where it changes a table that operation read (a column, a default, a key), the next --down refuses migrate-non-reversible, and the two-step down that refusal’s Hint names undoes the new operation first and meets the same guard. So the Hint’s Under migrate --down clause names what works there: fix the rows it counted, or remove in SQL the state no operation records, then run the same down; or roll forward: author the named operation, then the operator after it, and apply them forward.
• emit-render-failed: a body hit one of three un-appliable render classes: a tombstoned reference (it names an element not live as of the requested cut), a sentinel/reference count mismatch, or a duplicate output label after a rename; the last is reachable with every referenced element still live (a column-list-less view whose projection labels collide). migrate aborts before flushing rather than emit DDL PostgreSQL would reject (GroupLexParse, exit 1). The envelope carries the relevant element id, or the colliding label for the duplicate-label class.
• migrate-tx-incompatible: the window needs a mid-file boundary under --envelope=caller, which owns none (tx-incompatible-unattested), or no boundary placement satisfies it at all (tx-incompatible-unsatisfiable-edge). The discriminator names which; the first is answered by --envelope=own, narrowing, or splitting the file, the second only by splitting the window. A data body’s COMMIT;, or a \c/\connect line admitted under --allow-unscreened-dml, is such a boundary. See Segmented scripts.
• migrate-lock-timeout-unscoped: a lock timeout is resolved (--lock-timeout or [project] lock_timeout) on an invocation whose file opens no transaction to scope it into: --envelope=independent. Refused before any artifact byte (exit 1, no class); set the bound on the runner’s connection, or pass --lock-timeout=off. See Bounding the lock wait.
• migrate-into-schema-function-trigger-unsupported: under --into-schema, the range carries a function or trigger op, or a function or trigger is live in the --to cut. Whole-migrate refusal; see --into-schema.
• migrate-into-schema-row-motion-unsupported: under --into-schema, the window holds row motion the copy cannot carry: a row-moving operation, a CastColumn with using=, a RenameEnumValue on an enum a copied column uses, a DetachPartition of a partition that takes its columns from its parent, or a non-seed data-corpus record anchored in the window. Whole-migrate refusal; stop --to before the named operation, or see --into-schema.
• migrate-into-schema-unsupported-family: under --into-schema, a view or materialized view is live in the --to cut, or a partition that owns its columns sits under a partitioned table in the copy’s source (the --from cut, or the tip under --down). Whole-migrate refusal; see --into-schema.
• migrate-blocked-on-partitioned-fk-not-valid: the range adds a NOT VALID foreign key on a partitioned table, which PostgreSQL 17 forbids, or a PartitionTable rebuild re-adds one: the rebuilt table carries an outbound foreign key still NOT VALID. Validate immediately (for the rebuild, ValidateConstraint the FK before the PartitionTable, or drop it before partitioning), re-parent the FK, or pin engine_version to 18.
• migrate-rebuild-function-arg-renamed: a PartitionTable or DepartitionTable rebuild drops and re-creates a BEGIN ATOMIC function that reads the table, and one of the function’s argument types (a table, view or type) was renamed after the function was created. The re-create would name the old type name, binding whatever holds it now or failing at apply (exit 2). Author DropFunction then CreateFunction with the current type names before the rebuild, or stop --to before it.
• dml-anchor-unresolved: a preserved-DML record’s anchor-op resolves to no op in (from, to] (exit 2). Two causes, told apart by the detail. The anchor is a real op ahead of --to: the hint names the --to that reaches it, and --allow-unreached-dml downgrades the refusal to a dml-ahead-of-window warning and omits the record (it is never emitted out of position). Or the anchor names no op the oplog holds: no flag downgrades that; re-pin the file with retrofit data repin <file> --anchor-op <opid>. A record anchored behind the window is the softer case: omitted with an advisory.
• dml-purpose-unrouted: a preserved-DML record carries a purpose this build does not route. A routing gap between the wire enum and the consumer, not a user error (exits 2).
• dml-unclassified: the data corpus holds an unclassified record and --allow-unclassified-dml was not passed (exit 2).
• migrate-dml-unscreened: set-aside DML carries content that would create a transaction segment retrofit cannot count (explicit transaction control, an unparseable body, or a psql meta-command line; the discriminator names which: dml-body-transaction-control, dml-body-unscreenable or dml-body-meta-command, whose remedy is to delete the psql meta-command lines). --allow-unscreened-dml attests it and degrades the window’s reported segment count to a lower bound (exit 2).
• stale-dml-reference: a preserved-DML body holds a reference render-time repair cannot resolve without changing its meaning, and --allow-stale-dml was not passed (exit 2). A replace table that another table’s foreign key references, and whose rows cover no key, refuses here too with reason key-uncovered; --allow-stale-dml does not downgrade it.
• replace-no-upsert-form: a replace table’s key is a unique index whose columns a DEFERRABLE UNIQUE constraint shares, so no ON CONFLICT form can select it (exit 2).
• data-path-missing: the schema’s declared data_root directory is absent.
• data-file-unreadable: a corpus data-file path cannot be stat’d or read (exit 2).
• data-file-malformed: a corpus data file fails the data-file grammar (exit 2). Failures caught by a framing guard carry that guard’s discriminator; the header and record-grammar failures raise the same code with no discriminator. One refusal names every malformed file the corpus scan finds; only an unfilled scaffold is skipped instead, with a data-file-unfilled warning.
• dml-data-conflict: either of two rival-claim shapes at one chain and anchor: two divergent versions of one row, or two replace blocks in different files each claiming the table’s full row set. The latter aborts even when their row keys are disjoint, since picking one would silently drop the other’s claim. Hard abort; the envelope names both files.
• into-cross-schema-fk-unreachable: under --into-schema, an FK references a table outside the source schema.
• migrate-internal: panic recovered during emission (exits 2).
• io-error: opening, writing, or closing the migrate output stream failed.

Many migrate refusals (migrate-blocked-on-*, migrate-replace-function-parameter-rename, migrate-replace-function-return-row-change, migrate-move-column-fk-source-deferred, migrate-move-column-needs-default, migrate-non-reversible, dump-parse-error, the cut-resolution refusals, and the flag-stage into-*, flag-conflict and from-dump-mutex) exit 1 via GroupLexParse.

The exit-2 GroupUserEnv refusals migrate itself raises are unknown-envelope, label-empty, flag-retired, the three engine codes (unknown-engine-name, unknown-engine-version, unsupported-on-engine-version), dump-read-error, from-dump-no-match, from-dump-behind, from-dump-working-copy, from-dump-schema-absent, from-dump-ambiguous, data-path-missing, dml-anchor-unresolved, dml-unclassified, stale-dml-reference, data-file-unreadable, data-file-malformed, dml-data-conflict, migrate-dml-unscreened, and migrate-rebuild-function-arg-renamed. migrate-internal and dml-purpose-unrouted are GroupInternal and also exit 2. io-error exits 2 from either group: GroupInternal when the output stream fails, GroupUserEnv when reading the data corpus does.

The lists above are migrate’s own refusals and are not the closed set of codes a migrate run can emit. Shared subsystems every verb goes through raise their own, under their own contracts: argument validation (unexpected-args), cut-reference anchoring (unknown-cut-ref, ref-out-of-range), and replay-stage validation: the catalog rules enforced at replay, never the append-only ones. That set spans both the rule-* family and custom rules carrying their own names (survivor-pre-drop, for one). Each keeps its own declared exit class; most render as GroupLexParse → exit 1. unverifiable is the replay-stage code that is uniformly GroupUserEnv → exit 2. Treat all of them as owned by their subsystems, not enumerated here.

Oplog parse failures are the exception that does not surface its subsystem’s code: an oplog that does not parse refuses io-error at exit 2, with the parse code (kind-mismatch, for one) only in the message text. Match on the message, not the code, for those. A conflict marker a merge left in the oplog is the one parse failure that keeps its own code: oplog-conflict-marker at exit 1.

A CI gate keyed on exit 1 alone therefore misses the environment- and corpus-shaped refusals; key on the envelope’s code instead. See cli-output-contract.md for the group → exit-code mapping.

Scope contract

migrate’s in-scope set is the allow-list in § What migrate refuses. The full per-operator inventory is in smo-catalog.md.

The out-of-scope categories on migrate are:

• pg18: PG18-gated ops.
• N/A:no-pg-primitive: ops with no DDL primitive at all (SetGeneratedExpression desugars; SetColumnPosition has no PG primitive).

Data-motion ops (structural + move/copy) are in scope and emit destructive DDL (see § Data-motion SMOs).

Related

• concepts.md: oplog, cut, replay, genealogy.
• snapshot.md: single-cut post-state emission.
• drift.md: reconcile an oplog with an out-of-band schema.
• smo-catalog.md: every operator with status.
• cli-output-contract.md: per-verb stdout / stderr / exit-code shape.