retrofit migrate
retrofit migrate writes to stdout the SQL that takes a database from
one cut of the oplog to another: forward by
default, or the reverse with --down. It reads the oplog, the
configuration, the data files under the schema’s data_root and, with
--from-dump, a schema-only dump, and writes no file.
Emit a migration script is the task guide, and
How a migration applies gives the model
behind the envelope, segments and the lock wait.
Synopsis
From retrofit migrate --help:
Usage:
retrofit migrate [flags]
Description
migrate replays the oplog to two cuts, --from and --to, and writes
the ALTER, DROP and CREATE statements that turn a database at the
first into one at the second, with the data records the window holds
spliced in. The schema is the one --schema names, or the one the
resolver chain
selects. The same oplog, cuts, corpus and flags give byte-identical
output.
The window
The window is (from, to]: the operations after --from, up to and
including --to.
| Flag | The cut | Default |
|---|---|---|
--from | The cut the database is assumed to be at. | The latest Seal, or root when the oplog has no Seal. |
--to | The cut the script ends at. | last |
Together the defaults are the unpublished delta. With no Seal that is
the whole oplog as a forward CREATE script, the same output as
--from root.
Both flags take an opid prefix that matches one opid, or an anchor,
root, last or seal, each with an optional ^ or ~N decrement
toward older operations: seal^ is the Seal before the latest, last~2
two operations before the last. A --from anchor that steps past the
oldest operation is clamped to root, and so is --from seal with no
Seal. A --to anchor that steps past it, --to seal with no Seal, and
any decrement of root refuse.
An unsealed operation’s position moves as the log grows, and a
reconcile rebases it, so a --from that names one prints a note
naming the recourse: start from a Seal, or place the database with
--from-dump.
A window that holds no operation (right after a seal, --from seal --to seal, an empty oplog) exits 0 with a valid document: the header, the
marker line -- no changes in the requested window: nothing to migrate,
and an empty wrapper. stderr has nothing to migrate: the requested window is empty, and under --as=json the ok record has no ddl
object. --no-header drops the marker line only.
Starting from a dump
--from-dump <file> names the cut the database is at by identifying a
pg_dump --schema-only capture of it, as retrofit identify does.
Forward, the dump fills --from, and --to keeps its default, last,
so the script also applies every unsealed operation; --to seal stops
at the latest Seal. Under --down the database is at the window’s tip,
so the dump fills --at, and --from names the cut to return to (by
default the latest Seal, so every operation after it that the database
holds is reversed, and each column the down drops takes its rows).
--from-dump excludes the flag that names the same slot: --from
forward, --at under --down. The argument is a .sql path, - or a
stream; a connection string is read as a file path, never as a
connection. When the dump matches a run of Seals that only operations a
schema dump cannot observe separate, the run’s oldest Seal fills
--from and its newest --at, and the from-dump-seal-run note names
the run.
A dump at no cut
When no cut matches the dump, the refusal names the nearest cut (among every Seal and every cut after the last Seal) and how the database differs from it:
from-dump-no-match: the database was at the last op, then changed outside the log. The hint namesdrift --dry-run.from-dump-behind: operations follow the nearest cut, each listed as sealed or unsealed (JSONops_after). The hint names nodrift, which would record their reversal: a database that takes unsealed operations is rebuilt from the log into a fresh database, its rows saved first.from-dump-working-copy: the difference is in elements unsealed operations created or changed; the hint isfrom-dump-behind’s.from-dump-ambiguous: the dump matches more than one cut, or a run of Seals whose data replays differently at each. The hint names the cut stated by hand:--from <opid>, or--at <opid>under--down.from-dump-schema-absent: the dump holds no object other than an extension (an empty orSET-only dump), so the database is atroot; only a schema other thanpublic, which every database holds.--from rootwrites theCREATE SCHEMA.
Sealing and deploying explains each answer.
Objects the project does not own
--from-dump removes what the project’s [[exclude]] entries own from
the dump before identifying it, so those objects never read as a
difference, and reports each entry that removed a statement. A kept
statement that depends on an excluded object refuses. The script renders
from the oplog unchanged. Exclude objects the project does not
own covers the entries.
Output
The script is one document on stdout. Under the default envelope it is
BEGIN;, the statements, then COMMIT;, with an open-transaction check
(The envelope). Every document opens with a header:
-- engine: postgres 17
-- assumes database is at 0f3c1a9b2d4e
The assumes line names the cut the script starts from: --from
forward, the window’s tip under --down. Under --into-schema a third
line, -- assumes <schema> does not exist in target database, names the
copy’s target. Every document also has one obligation line naming its
envelope:
-- envelope: own; apply this file outside any open transaction (psql only: it carries \set ON_ERROR_STOP; use --envelope caller for a driver)
-- 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
No runner parses the line, and --no-header keeps it. An own file is
a psql script: its \set ON_ERROR_STOP on line is a psql command, so
a driver that sends the file as one query fails at the backslash before
any statement applies. Under --as=json the document is the ddl field
of a {"type":"migrate","ddl":"<document>"} record.
There is no --include-drop: a DROP is written where an operation in
the window drops something. A DropFunction writes a bare
DROP FUNCTION <signature>;, never CASCADE. A rebuild writes a DROP
that retires nothing: ReplaceMaterializedView writes
DROP MATERIALIZED VIEW … CASCADE, then the CREATE. A drop and
rebuild of a whole schema is snapshot --include-drop’s (The drop
prologue).
The data designation line
Every run of data rows spliced in 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"."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).
Identity sequences after a row copy
An identity column on a table CopyTable, SplitTable or MergeTable
creates gets a new backing sequence. After the row copy, migrate sets
each such sequence to the column’s max with setval: after
CopyTable’s INSERT, after each SplitTable child’s INSERT, and
once after MergeTable’s last source INSERT. Values all below the
sequence’s minimum set it to that minimum; a descending sequence is set
to the column’s min; an empty target keeps its START WITH.
ExtractTable and the other data-motion operators write no alignment.
The alignment does not deduplicate: where a single-column key enforces
the identity, overlapping source ranges fail a merge at apply.
The row-level security read guard
A render that reads rows and then moves, copies, rewrites or drops them
reads as the applying role, and where row-level security applies to that
role PostgreSQL returns only the rows the policies show, with no error.
So each such render guards every table it reads, after its LOCKs and
before its first read: CopyTable, CopyColumn, ExtractTable,
JoinTable, SplitTable, MergeTable, InlineTable, the
PartitionTable and DepartitionTable rebuilds, each table an --into-schema copy
reads, and the reverses that read rows back. The guard raises
row-level security filters <the rows> of <table> when
row_security_active(<table>) is true:
- never for a superuser or a role with
BYPASSRLS; - for the table’s owner only when the table is
FORCE ROW LEVEL SECURITY; - for any other role whenever row-level security is enabled on the table, even when the policies would hide no row.
The guard is written whatever the replayed schema shows, since row
security enabled outside the log or by a RunRawScript is not in it.
The lock a split or merge holds
Under own and caller, a SplitTable locks its source, and a
MergeTable its into table and then each source, with
LOCK TABLE <table> IN EXCLUSIVE MODE;, held until commit. While it is
held, writes to the table wait, and so do writes to any table whose
foreign key references it. Under independent no lock is taken, and a
note states the premise that no session writes those tables. migrate
prints one apply-risk-exclusive-lock warning per locked table that may
hold rows; it does not change the exit, and validate --fail-on-apply-risk does not count it. A cast that rewrites its table
has a warning of its own (Column casts).
The reverse migration
--down writes the statements that take a database at the window’s tip
back to --from. For each element the window touched, it computes the
net change from the tip back to --from as ordinary operations, and the
forward renderer writes them, with the same locks, wrapper and
-- destructive: markers:
- An element live at both cuts is matched by identity: a rename is
renamed back, a cast cast back, a default or nullability change
reverted. An operation that changes no structure, such as
ValidateConstraint, writes nothing and is reported asreverse-noop. - An element the window created is dropped, and the report names the rows the drop destroys.
- An element the window dropped is re-created from its definition at
--from, without the rows the drop discarded. - A structural operation inverts as its paired operator, which keeps the
rows:
ExtractTableasInlineTable,JoinTableasUnjoinTable,SplitTableasMergeTableand back,MoveColumnas the move back. - A
RunRawScriptdeclares its reversal as one of three arms:reverse_body=<DO block>, written verbatim and reported asreverse-opaque(nothing checks that it undoes the forward);reverse_noop=<reason>, which writes nothing; orreverse_blocked=<reason>, which marks it forward only. A script with no arm, or a blocked one, refuses the window.
A sealed script’s arm can be declared later with
retrofit op add DeclareReversal target=<opid> <arm>, which leaves the
sealed line untouched; the newest declaration governs.
A window the down cannot invert refuses with migrate-non-reversible
and writes nothing. The detail lists each block, and each block’s hint
names a way back: usually --from <op> to leave the blocking operation
outside the window, or a compensating forward.
The database’s cut is the window’s tip: --at <cut>, the dump under
--from-dump, or last. A --to before the tip makes the window a
slice that keeps the operations after --to; a slice needs an explicit
--at or --from-dump.
--down leaves the oplog as it was, and
retrofit op revert appends the same
reversal to it as new operations.
A down whose range opens with operations and the reversal op revert
already appended for them does not render that run again: it renders
what remains after the recorded reversal, and its report names the
recorded run as reverse-recorded. A down that takes last as its tip
(no --at, no --from-dump) says in the hint that a database that has
not applied the recorded reversal is not at that tip.
Guards when a down is applied
A down’s statements include the operators’ apply-time guards
(retrofit: <id>: …, SQLSTATE P0001). A guard raised on an operation
the down computed, such as the MergeTable that undoes a SplitTable,
prints the same hint as on one you wrote, but its first way out (an
operation authored before it) cannot apply to a computed operation. The
hint’s Under migrate --down clause names what works: fix the rows it
counted, or remove in SQL the state no operation records, and run the
same down; or roll forward instead.
Reverse policy for a relaxation
A relaxation that removes a rule (DropNotNull, DropCheck,
DropForeignKey, DropUnique, DropIndex, SetConstraintEnforced,
CastColumn) takes an optional reverse payload naming what the down
does to rows written after the relaxation:
reverse=refuse, the default wherever the rule can be refused, returns the rule as a guard: PostgreSQL refuses a row that breaks it.reverse=not_validreturns the ruleNOT VALID, checking no row present; the report has the query that shows the rows left unchecked. ADropNotNullcomes back asCHECK (<column> IS NOT NULL) NOT VALID, named by the relaxation’sname=.reverse=coercerewrites the rows that break the rule, then re-adds it:fill=<value>onDropNotNullandDropForeignKey,--fill column=<table.column>,value=<value>onDropCheck, and--keep <table.column>onDropUniqueandDropIndex, which keeps the first of each set of duplicates and sets the key’s nullable columnsNULLon the rest.
The reverse operators (AddNotNull, AddCheck, AddForeignKey,
AddUnique, CreateIndex) take the same payload, so the same rewrite
can roll the rule forward. A window that relaxes one rule under two
policies, or with two payloads, refuses; the hint names op revise of
an unsealed relaxation, or op add of the reverse operator. op add
and --down report each operation whose down meets rows written after
it as one after-traffic-outcome line. No window refuses for loss; the
report names every loss.
On a column whose type is a domain over a composite type, IS NULL is
row-wise: a fill= on DropNotNull or AddNotNull overwrites a value
whose fields are all NULL, and the not_valid carrier rejects one
holding any NULL field.
The down report
A down’s header has a report of what it does, as -- lines after the
-- envelope: line, under --no-header too. The same items are printed
on stderr and, under --as=json, are in the migrate record’s notes:
-- 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
| Item | What it names |
|---|---|
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, graded certain or potential, with the compensating forward that keeps them. |
data-loss-on-apply | Each element the down re-creates empty. |
reverse-guard | Each rule the down re-imposes. A rule returned NOT VALID has the query that shows the rows left unchecked. |
reverse-irreversible | What the down does not bring back as --from held it. |
reverse-noop | What writes no statement. |
reverse-opaque | A RunRawScript reversed by its unchecked reverse_body. |
reverse-declared-noop | A RunRawScript declared to need no down action, with the reason. |
reverse-slice | For a slice, the range it rolls back and the operations it keeps. |
reverse-recorded | The range op revert already recorded, the operations that record it, and the cut what remains reaches. Advisory codes has its hints. |
A side copy of the earlier cut
--down --into-schema <schema> writes the --from cut as a copy in a
side schema, its rows copied from the live tables by element identity,
and leaves the live schema and its rows as they are. 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 no
RunRawScript reverses by a body. Any other window refuses with
migrate-non-reversible, naming the first column, rule or script that
fails.
The envelope
--envelope states the transactional context the file is applied in.
migrate cannot observe it, and writes the file the value describes.
| Value | migrate writes its own wrapper | An outer transaction is open at apply |
|---|---|---|
own (default) | yes | no |
caller | no | yes: the one you opened |
independent | no | no: each statement applies on its own |
own wraps on every path, --down and --into-schema included. The
value resolves from --envelope, then [project] envelope in
retrofit.toml, then own; the key has no [[schema]] tier, since one
file can span schemas. snapshot has no --envelope.
An own file refuses to run inside a transaction that is already open:
it raises own-envelope-transaction-open (SQLSTATE 25001) before any
DDL, so nothing applies, and checks again inside its BEGIN; in case a
session’s ON_ERROR_ROLLBACK let the first check pass. The check is a
DO block, so the applying role needs USAGE on plpgsql. caller and
independent files have no check.
Mid-file boundaries
Some statements need the enclosing transaction closed around them
(Segmented scripts). Under own the file writes
that boundary, a COMMIT; and a new BEGIN;. Under the other two it
cannot, since a bare mid-file COMMIT; would end the applier’s
transaction:
- Under
callerthe run refuses withmigrate-tx-incompatible. The hint names--envelope=ownwith a runner that opens no transaction, narrowing--from/--to, or splitting the migration into files. - Under
independenteach statement already runs on its own, so the file is written, exits 0, and printsmigrate-segmented-non-atomic.
A boundary that costs no atomicity (a commit with nothing after it to
read the effect) neither refuses nor segments: under own the file
writes it, and under the other two a boundary-requirement-commit-boundary
advisory leaves it to the apply context.
Deferred foreign keys around a row-writing op
A foreign key declared DEFERRABLE INITIALLY DEFERRED queues its check
to COMMIT, so a row-writing operation (a fold’s UPDATE or DELETE, a
split’s INSERT) leaves checks pending, and a later ALTER TABLE of the
table fails with 55006 (pending trigger events).
Under own, migrate brackets each such operation with
SET CONSTRAINTS <key>, … IMMEDIATE; before its writes and
… DEFERRED; after, for each key its writes can queue a check under,
and prints one deferred-keys-bracketed note. A write an earlier
operation left pending under a bracketed key fails with 23503 at the
opening line, naming the key. Under caller it writes
no bracket and prints a deferred-keys-owed warning whose hint composes
the lines to place around the script. Under independent nothing is
queued past a statement.
An authored data record is not bracketed, since migrate does not rewrite
its body. Where its writes leave a check pending at a later operation
that alters, truncates, drops or indexes the table, the script has a
-- retrofit: comment above that operation and prints a
deferred-keys-pending warning. A record that ends with
SET CONSTRAINTS <schema>.<key> IMMEDIATE; (naming the key, not ALL)
runs its checks first. A key whose name another constraint of the schema
shares cannot be named, and under own refuses with
migrate-deferred-key-name-ambiguous.
The lock timeout
--lock-timeout=<duration> writes PostgreSQL’s transaction-scoped
setting at the head of every transaction the file opens:
SET LOCAL lock_timeout = '2s';
A statement that cannot get a lock within the bound fails with
canceling statement due to lock timeout (SQLSTATE 55P03), and its
transaction rolls back. The bound applies to each lock the statement
takes, row locks included. migrate retries nothing.
<duration> is a positive integer, no sign or leading zero, followed
with no space by ms, s, min or h, at most 2147483647ms: 500ms,
2s, 1min. It is written as given (2s and 2000ms are different
bytes). off means unset. The value resolves from --lock-timeout,
then [project] lock_timeout, then unset; --lock-timeout=off clears
either for one run. Unset, the file is byte-identical to one written
without the setting. snapshot reads neither.
| Envelope | Placement | A give-up rolls back | Re-running the file |
|---|---|---|---|
own, one span | After BEGIN; and the ON_ERROR_STOP guard. | The whole file. | Applies it whole. |
own, segmented | At the head of every span. | That span; earlier spans stay committed. | Fails at the first statement already applied (Recover after a failed apply or a merge). |
caller | Once, as the file’s first statement. | The applier’s transaction: the whole file. | Applies it whole. |
independent | None: the run refuses with migrate-lock-timeout-unscoped, since SET LOCAL has no effect outside a transaction. | No file is written. |
Under caller, SET LOCAL lasts until the applier’s transaction ends,
so it also bounds the runner’s own statements in that transaction, and
every later file applied in it. A statement outside every span runs
without the bound (a DETACH PARTITION … CONCURRENTLY, a
non-transactional statement admitted under --allow-unscreened-dml),
and the segmentation warning names each one as unbounded:. A data body
that sets lock_timeout itself overrides the bound.
Segmented scripts
A window is written as several segments, each its own transaction, 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. Two operators need a boundary:
ALTER TYPE … ADD VALUEneeds a commit before a later statement can use the new value. A trailing one, with nothing after it to read the effect, costs no atomicity.DETACH PARTITION … CONCURRENTLYcannot run inside a transaction block, and costs atomicity wherever it is.
A data record holds a position too: seed records (--label) are one
block after all DDL, a record anchored on root comes before every
operator, and a record anchored on an operator shares its position. So a
window whose last operator needs a commit segments once a seed block
follows it, and narrowing the window cannot help, since the seed block
follows every operator in any window.
Under own such a window is written as segments and prints
migrate-segmented-non-atomic, naming each boundary and what a failure
in a segment leaves committed. Narrowing --from/--to so the
responsible operators fall outside keeps the rest atomic. Every script
wrapped under own opens its body with an ON_ERROR_STOP guard, so a
failure halts the run with exit 3 rather than running later segments.
migrate-tx-incompatible has a class you can branch on (class: in
text, discriminator in JSON). No flag answers either:
| Class | Raised when | The way out |
|---|---|---|
tx-incompatible-unattested | The window needs a boundary under caller, which owns none. | Narrow --from/--to; --envelope=own with a runner that opens no transaction; or split the file. |
tx-incompatible-unsatisfiable-edge | No placement is legal, as when an ALTER TYPE … ADD VALUE adds a label a later statement’s default or expression uses: PostgreSQL hides the new label until commit. | Split the window into two migrations. |
Under caller the unattested class is reported first.
A file with no wrapper prints atomicity-wrapper-suppressed, naming
every input that suppressed it, so stderr is not empty on an ordinary
caller or independent run. --allow-external-atomicity attests that
the apply context owns atomicity and silences it; it does not answer
migrate-segmented-non-atomic, whose lost atomicity is the window’s.
Data bodies with their own transaction control
Every spliced body is parsed and screened first, so the segment count
reported is the count the script applies as. A body with its own
COMMIT;, BEGIN; or ROLLBACK;, a statement PostgreSQL bars from a
transaction block (VACUUM, CREATE INDEX CONCURRENTLY), a line on
which psql starts a meta-command (\connect, \i, a dump’s
\restrict), or a body that cannot be screened refuses with
migrate-dml-unscreened, naming each record. The screen reads parsed
statements, so a COMMIT in a string, a dollar-quoted body or a comment
passes.
--allow-unscreened-dml writes such bodies anyway, and the report then
reads “at least N segments”. An admitted meta-command line gets a
-- WARNING: comment in the script, and an admitted \c or \connect
ends the open transaction as a COMMIT; does. REINDEX and CLUSTER on
a partitioned target are barred from a transaction block too, which
migrate cannot see; they are admitted with a
dml-body-partition-dependent advisory.
Applying with psql
Apply an own or independent file with:
psql -X -v ON_ERROR_STOP=1 -f migration.sql
-X skips the startup file (~/.psqlrc), and ON_ERROR_STOP=1 stops at
the first error with exit 3
(psql). Without -X,
a startup file that sets ON_ERROR_ROLLBACK on lets a failing statement
in a transaction pass: an own file’s guard still halts it, but a
caller file inside BEGIN; \i file.sql COMMIT; commits the rest and
exits 0. A caller file applies as one transaction under
psql -X -v ON_ERROR_STOP=1 -1.
| Apply context | Supported | What happens |
|---|---|---|
psql -X -v ON_ERROR_STOP=1 -f file.sql | yes | The file’s own boundaries are the transactions; it halts at the first failure. |
psql --single-transaction -f file.sql | no | An own file refuses by name (own-envelope-transaction-open) before any DDL. A window that does not segment applies as one transaction emitted under caller; a segmented one has no single-transaction form. |
| A runner that opens its own transaction around the file | no | An own file refuses by name before any DDL. |
snapshot’s --include-drop wrapper has no such check: under -1 its
COMMIT; ends psql’s transaction early.
The copy script
--into-schema <schema> writes a script that creates a copy of the
--to cut under another schema name, beside the live schema. It creates
each user-defined type, then each table with its constraints, fills it
from the source table with the same element identity, and sets each
identity sequence past the copied rows. A schema the --to cut already
registers refuses. Everything else is omitted or refused:
- Omitted: secondary indexes, replica identity, row-level security, policies and comments.
- Refused: a function or trigger live in the cut or operated on in the window; a view or materialized view live in the cut; a partition that owns its columns, whose rows would be copied twice; a foreign key to a table outside the source schema.
- Refused, since the copy cannot hold the rows they move: a row-moving
operation in the window (
CopyColumn,CopyTable,ExtractTable,JoinTable,MergeTable,SplitTable, a keyedMoveColumn), aCastColumnwithusing=, a reverse-policy payload, aRenameEnumValueon an enum a copied column uses, aDetachPartitionof a partition that takes its columns from its parent, and any reference, transition, cleanse or unclassified data record. A seed record is not written, even when--labelnames it. - A column of an extension type, or of a type in another schema, stays bound to that type.
- A
RunRawScriptblock is written verbatim after the copy, underSET LOCAL search_path = <schema>;, so its unqualified names resolve in the copy. A name qualified with the source schema still reaches the source, and each block’s-- WARNING:lists the source-qualified relations found in it.
What migrate renders
migrate renders the operators on its allow-list; an operation outside
it refuses with migrate-blocked-on-unsupported-smo, naming the operator
and opid, and the hint names stopping --to before it, writing its DDL
by hand, then resuming with --from at it. The list covers the schema,
table, column, constraint and index lifecycles, attribute updates,
types, extensions, views, materialized views, policies, row-level
security, declarative partitioning, most of the function and trigger
family, the data-motion operators, RunRawScript, and Seal, which
writes nothing. Operators gives each operator’s row.
Functions and triggers
CreateFunction, ReplaceFunction, RenameFunction, CreateTrigger,
DropTrigger, RenameTrigger, EnableTrigger and DisableTrigger
render. A ReplaceFunction that PostgreSQL would refuse with 42P13
refuses here first: one that renames or un-names an input parameter,
changes the output row, or removes an input parameter’s DEFAULT. The
hint names DropFunction then CreateFunction, or a replacement that
changes only the body.
Data-motion operators
CopyTable, ExtractTable, JoinTable, SplitTable, MergeTable,
MoveColumn and CopyColumn render CREATE TABLE … LIKE or
AS SELECT, INSERT … SELECT, and a DROP of the consumed source,
under a -- destructive: marker (-- destructive-conditional: for a
join with a predicate).
A MoveColumn of a column that is NOT NULL with no DEFAULT refuses
with migrate-move-column-needs-default, since the destination’s
existing rows would have no value; the hint names SetDefault before
the move, or DropNotNull and then AddNotNull once the destination is
filled. A move of a live foreign key’s source column is refused at
replay. A keyed MoveColumn along the one-to-one arm inserts a child row
where the moved value is not NULL and the child has none for that key,
reported as padded N row(s); where a NOT NULL on the child blocks
that row, the move’s guard raises migrate-move-column-pad-blocked at
apply, before it writes a row.
Splitting an optional attribute into a sparse child table
walks that shape.
Column casts
CastColumn renders ALTER TABLE … ALTER COLUMN … SET DATA TYPE … USING … under a -- destructive-conditional: cast … line; with no
using= it renders USING <column>::<type>. Adjacent casts on one
table render as one ALTER TABLE, so PostgreSQL rewrites the table once:
-- destructive-conditional: cast public.loans.lent_on to timestamptz
-- destructive-conditional: cast public.loans.returned_on to timestamptz
ALTER TABLE public.loans
ALTER COLUMN lent_on SET DATA TYPE timestamptz USING lent_on::timestamp AT TIME ZONE 'America/Los_Angeles',
ALTER COLUMN returned_on SET DATA TYPE timestamptz USING returned_on::timestamp AT TIME ZONE 'America/Los_Angeles';
A cast gets its own statement when another operation, a data body or a
transaction boundary comes between it and the previous cast; when the
run already casts the same column; when its using= reads a column an
earlier cast converts, or the whole row; when a using= calls a function
that reads or writes session state (nextval, set_config) or names
anything but the table and its columns; or when a deferred key’s check
is pending. --down renders each reverse cast on its own.
PostgreSQL refuses to change the type of a column a view reads, so the
cast drops those views (and the views that read them) first and
re-creates them after, under a cast-restores-dependents note that
names what the re-create does not restore, grants among them. A policy
or BEGIN ATOMIC function that reads a dropped view, and a trigger on
one, is re-created with it; a materialized view comes back WITH NO DATA, and the script ends with a -- post-deploy: REFRESH for it. A
policy, trigger WHEN or generated column that reads the column is not
dropped, and PostgreSQL refuses the cast over it. --down refuses the
cast back on a column a view reads.
A cast with no using= from date, timestamp or time to
timestamptz or timetz, or from timestamptz to date, timestamp,
time or timetz, converts each value in the applying session’s
TimeZone, so two sessions store
different values. A using= that names the zone does not depend on the
session.
A cast’s ALTER TABLE holds an ACCESS EXCLUSIVE lock, which blocks
reads and writes, while it rewrites or scans the table. migrate prints
one apply-risk-table-rewrite warning per cast statement that rewrites
the table, that rewrites it unless the applying session’s TimeZone is
UTC, or that rewrites nothing but scans the table to rebuild or re-check
an index or constraint. A table created earlier in the window gets none.
The warning does not change the exit; Advisory
codes gives its text.
View operators
CreateView, ReplaceView, RenameView and DropView write one
statement each. PostgreSQL permits CREATE OR REPLACE VIEW only when
the replacement keeps the existing columns with the same names, order
and types, and adds any new column after them; migrate checks the names
at replay, and PostgreSQL checks the types at apply.
PostgreSQL 18 syntax
migrate writes PostgreSQL 17 SQL at every emit target, 18 included. A
log holding PostgreSQL 18 syntax (SetConstraintEnforced,
SetConstraintInherit, a VIRTUAL generated column, NOT ENFORCED,
WITHOUT OVERLAPS or PERIOD) refuses with syntax-not-available,
forward and --down. An emit target older or newer than the pin refuses
an operation the pin accepts and the target does not, with
unsupported-on-engine-version; PostgreSQL
versions gives each case. --using=<engine>:<major>
overrides the emit target for one run (Emit
target).
Preserved DML
migrate reads the data corpus on every run, with no flag: every data
file under the schema’s data_root (a *.dml.sql file is read too).
--data-root reads another root, and --seed-path and
--extra-seed-path replace or add seed_paths roots (The data
file). Each record is written by its
purpose:
| Purpose | Where it is written |
|---|---|
ref | At its anchor, always; never filtered by label. |
transition | Right after its anchor operation’s DDL. |
cleanse | As a transition, inside a -- retrofit:dml run-once marker, so a re-applied migration does not repeat it. |
seed | Only when a --label names its dataset, as one block after all DDL. |
unclassified | Nowhere: the run refuses with dml-unclassified, unless --allow-unclassified-dml writes it under a WARNING block. |
transition and cleanse are experimental: unless retrofit.toml opts
into the purpose, a corpus holding one refuses with
experimental-not-enabled before any record is read (Experimental
purposes).
--label is repeatable and ordered, lowest layer first: a later layer’s
replace floor supersedes the layers below it for that table. A seed
dataset no --label names is left out and counted in a stderr advisory;
--allow-unnamed-label <label> says one is left out on purpose
(Exclusions).
The window and a record’s anchor
A record is written when its anchor is in the window. A record anchored
on a Seal belongs to the range after it, so --from that Seal includes
it and --to that Seal does not. A record anchored behind the window is
omitted with a dml-behind-window advisory, one anchored after --to
is omitted silently, and one whose anchor the oplog does not hold
refuses with dml-anchor-unresolved; the hint names retrofit data repin. A declared data_root that does not exist refuses; an absent
default or an empty directory is no corpus.
Repairs
Before writing a record, migrate brings its body up to date: a table or
column renamed since is written under its name at the render cut, a
write-only reference to a dropped column is removed, and a dropped
table’s records are left out. The file on disk is unchanged. A reference
whose meaning cannot be kept refuses with stale-dml-reference;
--allow-stale-dml writes the body under a WARNING comment instead,
except for the key-uncovered reason and a chain-collision (Stale
data references and repair).
Two cases refuse in a forward window because the script would apply wrongly:
dml-superseded-in-window: a reference record that a laterreplaceorrestateof its table supersedes, with a step between them that reads the table or checks its rows. The hint names splitting the window at the record’s operation, or versioning the table there withretrofit data scaffold --disposition restate --anchor-op <op>.dml-not-null-unfilled: a column added to a reference table that holds rows and setNOT NULLwith no default and nothing that fills it, which would fail with23502. The hint names versioning the table at the column’s birth, orretrofit op revise <op> "fill=<expr>".
The replace prologue
A replace record (a table’s full row set) is written with a prologue
so that it applies as the complete state. Within one table and label
set, the latest replace at or before --to supersedes earlier ones.
The prologue depends on the table’s key at that position: the first of
the primary key, the UNIQUE constraints, and the unique btree indexes
with no WHERE or expression column, whose every column the rows write.
A DEFERRABLE UNIQUE constraint is never the key, since PostgreSQL
cannot use it as an ON CONFLICT arbiter.
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) on email; rows that write only (name) have no
key.
| The table at that position | The prologue |
|---|---|
| has a key | a staged block, keyed on it |
| has no key, and no foreign key references it | a whole-table clear, then a staged reload |
| has no key, and a foreign key references it | refuses with stale-dml-reference, reason key-uncovered |
its key is a unique index a DEFERRABLE UNIQUE constraint shares | refuses with replace-no-upsert-form |
- Staged block. The rows load into a
TEMPtableLIKEthe target, are upserted into the target on the key, and every current row whose key the rows do not hold is deleted; then identity and serial sequences are re-seated. The target is never emptied between statements, and a referencing table’s rows never lose their parent. A column added after the capture that no row writes is left to the target, so it takes its default. - Whole-table clear and staged reload.
DELETE FROM <table>;, then the rows load in one statement. A column the rows do not write is set toNULL, and adata-replace-whole-reloadwarning names it. - Bare clear. A
body=nonefloor, or a body that cannot be staged, is aDELETE FROM <table>;ahead of the rows.
Rows written outside a staged block are followed by the same sequence
re-seat. Within one position, clears run children before parents, staged
blocks parents before children, and both ahead of every row. A staged
block needs the TEMP privilege, and its transaction is the envelope’s.
A table an ON DELETE CASCADE foreign key references is reported
(data-replace-cascade) before a clear reaches it. A surrogate key
generated afresh at capture (a serial the source regenerated) matches
nothing in the target, so the block replaces every row rather than
updating in place. retrofit data witness previews which prologue a
replace record gets, and retrofit op seal refuses a range holding a
reference replace with no ON CONFLICT form at its anchor
(replace-no-upsert-form).
A window across a reference declaration’s removal
A window that holds the schema ref-tables remove ending a table’s
reference span (a redeploy after a rollback, say) writes that table’s
floor as a delta, never its full state, because after the removal the
rows are the application’s. At apply it raises a kept N row(s) notice
when the table holds rows the corpus does not, and stderr has
data-replace-crossing. Where a key identifies the rows, it upserts the
floor’s keys and deletes only the keys the corpus itself dropped. Where
the dropped keys cannot be computed offline, it upserts only, and the
warning names the record.
Where no key identifies a row (a table with no key, or a row whose
nullable key holds a NULL), the whole row is the key and its copies are
counted, over the columns the floor writes. The window deletes every copy
of each row the corpus dropped since its start, then inserts the copies
of the floor’s rows the table lacks. A row equal to none the corpus held
is never touched, and a re-apply or a redeploy changes nothing. A
NULLS NOT DISTINCT key names its NULL as a key, so its rows take the
keyed delta. A column of json, xml, a geometric type, a domain or a
composite type compares by its text, so two of its values whose text
differs are two rows. Where the dropped rows cannot be computed offline,
it inserts only, and the warning says why.
Rows whose nullable key holds a NULL stay in the upsert, and may duplicate, where another unique key, unique index or exclusion constraint on the table could match them; the warning names it. A floor whose rows cannot be staged or compared applies them as stated, with no clear, and the warning says a target already holding them may refuse or duplicate them.
Row security, row triggers and rules
Every row-removing leg of a prologue is a plain DELETE run as the
applying role. Under row-level security, or on a table with row triggers
or ON DELETE … DO INSTEAD rules, those DELETEs skip the rows the
policy hides or the trigger or rule diverts, and still succeed:
| Prologue | Result |
|---|---|
| Bare clear | The skipped rows stay beside the inserted ones: duplicates, with no error. |
| Whole-table clear and staged reload | A surviving row that collides on a unique key fails the apply; otherwise, duplicates with no error. |
| Staged block | Stale rows stay beside the replace state, with no error. |
A superuser or BYPASSRLS role is not subject to row security, but no
role bypasses a row trigger or a DO INSTEAD rule. migrate writes no
check for this.
Conflicts in the corpus
Two files under one corpus root at one key (table, band, label set,
anchor) refuse with data-fork. Two records that state one row
differently, or two replace blocks under two roots that each claim the
table, refuse with dml-data-conflict. A file that breaks the data-file
grammar refuses with data-file-malformed, naming every such file,
except an unfilled scaffold, which is skipped with a warning.
Options
From retrofit migrate --help:
Flags:
--allow-external-atomicity [apply context] attest that the apply context owns transaction atomicity: the no-wrapper advisory is not raised where a non-own envelope suppressed the wrapper
--allow-reading-replace-body downgrade the replace-body-reads-relation refusal to a warning; emit the body verbatim under a WARNING comment, and render its table's prologue as a whole-table clear
--allow-stale-dml downgrade every stale-DML-reference refusal to a warning; emit the body under a stale-reference WARNING comment. It does NOT cover chain-collision, which is not attestable: two live chains for one element is a fork to resolve by retiring one, and exit 2 stands
--allow-unclassified-dml downgrade every unclassified-DML refusal to a warning; emit each unclassified body as a trailing unclassified-DML WARNING block
--allow-unnamed-label stringArray attest that the dataset carrying this exact label is deliberately left unnamed in this run (repeatable; byte-exact, never trimmed); every other unnamed dataset still warns
--allow-unscreened-dml attest set-aside DML whose body carries transaction control or psql meta-command lines (or which retrofit cannot parse): emit it anyway and report the window's segment count as a lower bound rather than an exact number
--at string requires --down: the cut the database is at, an opid prefix or anchor (default: last); --to beside it rolls back the slice (--from, --to] and keeps the ops after it
--data-root data_root root that overrides the schema's data_root
--down emit the reverse of the migration window: each touched element's net transition back to --from, rendered by the forward renderer
--envelope own transactional context the emitted file is applied in: own (retrofit wraps it in one transaction and refuses a transaction already open, as under psql --single-transaction; apply outside any open transaction) | caller (you open the transaction; retrofit emits no wrapper) | independent (each statement applies in its own implicit transaction); default: own
--extra-seed-path seed_paths root that appends to the declared seed_paths
--from root from-opid prefix or anchor (root/`last`/`seal`, optional `^`/`~N`); default: latest Seal, or `root` when no Seal exists
--from-dump string identify the cut the database is at from a pg_dump --schema-only file; it fills --from (forward) or --at (under --down)
-h, --help help for migrate
--help-all show extended help (vocabulary, semantics, caveats)
--into-schema string emit a copy script into <schema>; absence = in-place
--label stringArray apply the dataset carrying this exact label (repeatable; argv order is layer order; byte-exact, never trimmed; never cuts ref data)
--lock-timeout string bound each lock wait (per lock acquisition, row locks included): emit SET LOCAL lock_timeout = '<duration>' at the head of every transaction the file opens (a positive integer with ms, s, min or h, e.g. 2s; off = unset); overrides [project] lock_timeout; refused where the file has no transaction to scope it into
--no-header drop the no-op marker; the engine, starting-cut and envelope lines stay
--schema <name> registered schema <name> (resolves via the chain when omitted)
--seed-path seed_paths root that replaces the declared seed_paths
--to root to-opid prefix or anchor (root/`last`/`seal`, optional `^`/`~N`); default: last
--using string one-shot emit-target override (format: <engine>:<major>)
The global flags are in the Output contract.
Diagnostics
A refusal writes no script, and the error’s code names it (Output
contract). Refusal codes lists
every code migrate prints, with its exit status. Two refusals name their
way out: --allow-reading-replace-body admits a replace record whose
body reads a relation (data-replace-body-reads-relation), and
retrofit reconcile resolves an unreconciled fork inside the window
(fork-in-range). The operators’ apply-time guards (SQLSTATE P0001)
and own-envelope-transaction-open (SQLSTATE 25001) are raised by
PostgreSQL when the script runs, not by migrate.
Advisory codes lists the warnings and notes migrate prints, with every other advisory.
See also
Emit a migration script,
How a migration applies,
Choose who opens the transaction,
Bound how long a migration waits for a lock,
Recover after a failed apply or a merge,
retrofit snapshot,
retrofit data,
Operators,
Output contract.