retrofit
0.15.0-wip.89
retrofit.sh

These docs are for 0.15.0-wip.89, a WIP, and may describe what no release does yet. The newest release is 0.14.0: this page for 0.14.0.

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.

FlagThe cutDefault
--fromThe cut the database is assumed to be at.The latest Seal, or root when the oplog has no Seal.
--toThe 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 names drift --dry-run.
  • from-dump-behind: operations follow the nearest cut, each listed as sealed or unsealed (JSON ops_after). The hint names no drift, 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 is from-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 or SET-only dump), so the database is at root; only a schema other than public, which every database holds. --from root writes the CREATE 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, and a ReplaceView that rebuilds writes DROP VIEW with no CASCADE, after the objects that read the view, then the CREATE (View operators). 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. The tables UnjoinTable creates, and the table a PartitionTable or DepartitionTable rebuild creates, get the same: after each UnjoinTable child’s INSERT, and after the rebuild’s row copy, before the old table is dropped. 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. The alignment does not deduplicate: where a single-column key enforces the identity, overlapping source ranges fail a merge at apply.

Two renders outside the data-motion operators set an identity sequence the same way. An in-place AddIdentity gives the column a new sequence that starts at its START WITH under rows that already hold values, so after the ADD GENERATED … AS IDENTITY migrate sets that sequence past them with the same setval; an empty table keeps its START WITH. An --into-schema copy sets each copied table’s identity sequences after that table’s INSERT (The copy script).

InlineTable and ExtractTable move no identity column. Each sets a moved serial column’s new sequence instead: InlineTable on the table it folds into, from the column’s max, before it draws values for the rows that had no row to fold in; ExtractTable on the child, from the source’s max or its sequence’s position past it, before the source loses the column. A rebuild sets no serial sequence: the rebuilt table keeps the column’s live one.

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).

A lock held across a later step

Under own and caller, a lock that blocks a table’s writes is held until the transaction commits, so a later step in the same transaction whose time retrofit cannot bound makes those writers wait for it too. Such a step rewrites or rescans a table, runs a validating scan or an index build, copies rows, or runs a raw script or a data body. migrate prints one apply-risk-lock-held-across-step warning per held table, naming the op that took the lock and the first such step after it. Its hint names where to split the window into parts that each commit, and, when no --lock-timeout is set, the --envelope=independent form of the run. A table created earlier in the same transaction holds nothing, and independent raises none. The warning does not change the exit, and validate --fail-on-apply-risk does not count it; Advisory codes gives its text.

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 as reverse-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: ExtractTable as InlineTable, JoinTable as UnjoinTable, SplitTable as MergeTable and back, MoveColumn as the move back.
  • A RunRawScript declares its reversal as one of three arms: reverse_body=<DO block>, written verbatim and reported as reverse-opaque (nothing checks that it undoes the forward); reverse_noop=<reason>, which writes nothing; or reverse_blocked=<reason>, which marks it forward only. A script with no arm, or a blocked one, refuses the window. So does a reverse_body whose DO block commits or rolls back, under every envelope: a down is never written as segments, and PostgreSQL fails such a block inside a transaction.

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 reverse_body that commits or rolls back is appended too, and a forward migrate places it as it places any script that commits (Segmented scripts).

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_valid returns the rule NOT VALID, checking no row present; the report has the query that shows the rows left unchecked. A DropNotNull comes back as CHECK (<column> IS NOT NULL) NOT VALID, named by the relaxation’s name=.
  • reverse=coerce rewrites the rows that break the rule, then re-adds it: fill=<value> on DropNotNull and DropForeignKey, --fill column=<table.column>,value=<value> on DropCheck, and --keep <table.column> on DropUnique and DropIndex, which keeps the first of each set of duplicates and sets the key’s nullable columns NULL on 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 CHECK 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
ItemWhat it names
reverse-unrecordedThe oplog does not record the reverse; the hint names how the log catches up.
data-loss-on-apply-render-chosenEach step that destroys rows written since the forward, graded certain or potential, with the compensating forward that keeps them.
data-loss-on-applyEach element the down re-creates empty.
reverse-guardEach rule the down re-imposes. A rule returned NOT VALID has the query that shows the rows left unchecked.
reverse-irreversibleWhat the down does not bring back as --from held it.
reverse-noopWhat writes no statement.
reverse-opaqueA RunRawScript reversed by its unchecked reverse_body.
reverse-declared-noopA RunRawScript declared to need no down action, with the reason.
reverse-sliceFor a slice, the range it rolls back and the operations it keeps.
reverse-recordedThe 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.

Valuemigrate writes its own wrapperAn outer transaction is open at apply
own (default)yesno
callernoyes: the one you opened
independentnono: 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 caller the run refuses with migrate-tx-incompatible. The hint names --envelope=own with a runner that opens no transaction, narrowing --from/--to, or splitting the migration into files.
  • Under independent each statement already runs on its own, so the file is written, exits 0, and prints migrate-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.

EnvelopePlacementA give-up rolls backRe-running the file
own, one spanAfter BEGIN; and the ON_ERROR_STOP guard.The whole file.Applies it whole.
own, segmentedAt 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).
callerOnce, as the file’s first statement.The applier’s transaction: the whole file.Applies it whole.
independentNone: 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 CREATE or DROP INDEX … CONCURRENTLY, a RunRawScript whose DO block commits or rolls back, 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. These operators need a boundary:

  • ALTER TYPE … ADD VALUE needs 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 … CONCURRENTLY, CREATE INDEX … CONCURRENTLY and DROP INDEX … CONCURRENTLY cannot run inside a transaction block, and each costs atomicity wherever it is.
  • A RunRawScript whose PL/pgSQL DO block commits or rolls back, at any depth (the batched backfill), costs atomicity wherever it is. PostgreSQL runs a DO block called inside a transaction block as one unit, and its COMMIT there fails with SQLSTATE 2D000. The control is read from the parsed block, so a COMMIT in a string, a comment or an EXECUTEd string, or a body in another language, keeps the script in its transaction.

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:

ClassRaised whenThe way out
tx-incompatible-unattestedThe 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-edgeNo 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 PL/pgSQL DO block that commits or rolls back at any depth, 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 comment, a function body or an EXECUTEd string passes, as does a DO block in another language.

--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 contextSupportedWhat happens
psql -X -v ON_ERROR_STOP=1 -f file.sqlyesThe file’s own boundaries are the transactions; it halts at the first failure.
psql --single-transaction -f file.sqlnoAn 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 filenoAn 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 keyed MoveColumn), a CastColumn with using=, a reverse-policy payload, a RenameEnumValue on an enum a copied column uses, a DetachPartition of 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 --label names it.
  • A column of an extension type, or of a type in another schema, stays bound to that type.
  • A RunRawScript block is written verbatim after the copy, under SET 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.
  • The path is a session SET search_path = <schema>; where the section has no one transaction to scope it to: under independent, and under own when a script’s DO block commits or rolls back. That script runs outside the copy’s transaction, which closes before it and opens again after it when a script follows. Under caller such a script refuses the copy as it refuses the window (Segmented scripts).

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. The note names what the re-create does not restore: rules, security labels and options, and comments on all but a re-created function. Grants and ownership are restored at apply, and a retrofit: acl- WARNING names each privilege or owner that is not. 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. Under retrofit validate --fail-on-apply-risk, such a cast counts as apply-risk-window-table-rewrite, and fails the run, when retrofit vouches for its recovery; one whose recovery it does not vouch for stays advisory (Advisory note codes).

View operators

CreateView, 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. A ReplaceView writes one CREATE OR REPLACE VIEW only when the view’s column names are a prefix of the new list and each of those columns is proven to keep its type. Replay accepts any new column list, a narrowing or a reorder included.

Any other ReplaceView is a rebuild. It opens with a view-rebuild-restores-dependents note naming what it drops and re-creates, and the ACCESS EXCLUSIVE lock it takes on the view and on each object that reads it. It drops those objects, then the view, with no CASCADE; creates the view with the new definition; restores its grants and ownership; and re-creates its triggers and the objects that read it, in dependency order. A -- WARNING: line names each object that may fail at apply or at its next fire, or that reads the view through a star and re-expands to the new list. A -- post-deploy: REFRESH line follows for each materialized view re-created empty. retrofit op add warns apply-risk-view-rebuild for a ReplaceView that rebuilds (Advisory codes).

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:

PurposeWhere it is written
refAt its anchor, always; never filtered by label.
transitionRight after its anchor operation’s DDL.
cleanseAs a transition, inside a -- retrofit:dml run-once marker, so a re-applied migration does not repeat it.
seedOnly when a --label names its dataset, as one block after all DDL.
unclassifiedNowhere: 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 later replace or restate of 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 with retrofit data scaffold --disposition restate --anchor-op <op>.
  • dml-not-null-unfilled: a column added to a reference table that holds rows and set NOT NULL with no default and nothing that fills it, which would fail with 23502. The hint names versioning the table at the column’s birth, or retrofit 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 positionThe prologue
has a keya staged block, keyed on it
has no key, and no foreign key references ita whole-table clear, then a staged reload
has no key, and a foreign key references itrefuses with stale-dml-reference, reason key-uncovered
its key is a unique index a DEFERRABLE UNIQUE constraint sharesrefuses with replace-no-upsert-form
  • Staged block. The rows load into a TEMP table LIKE the 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 to NULL, and a data-replace-whole-reload warning names it.
  • Bare clear. A body=none floor, or a body that cannot be staged, is a DELETE 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:

PrologueResult
Bare clearThe skipped rows stay beside the inserted ones: duplicates, with no error.
Whole-table clear and staged reloadA surviving row that collides on a unique key fails the apply; otherwise, duplicates with no error.
Staged blockStale 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 <path>                    root <path> 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|caller|independent>   transactional context <own|caller|independent> 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 <path>              root <path> that appends to the declared `seed_paths`
      --from <cut>                          start <cut>: 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 <path>                    root <path> that replaces the declared `seed_paths`
      --to <cut>                            end <cut>: 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.

Before 1.0, any release may change behavior; the release notes list what changed.

Published .