Emit a migration script
retrofit migrate prints the SQL that takes a database from one point
in the oplog, the log of operators (ops) your
schema is recorded in, to another: the ALTER, CREATE and DROP
statements, with the data rows recorded between the two points. It
reads the oplog and never changes it. Pipe the output to
psql, or keep the file for your deploy pipeline to apply.
retrofit migrate is the reference: every flag, the shape
of the output, and each refusal and exit code.
How a migration applies gives the model.
Emit what is new since the last release
retrofit migrate > migration.sql
psql -X -v ON_ERROR_STOP=1 -f migration.sql
By default the script starts at the latest Seal, the end of the history
your last release shipped (Sealed and unsealed ops), and ends at the last op. With no
Seal yet, it builds the whole schema from nothing. The file holds its
own BEGIN; and COMMIT;, so a failure part way applies nothing,
unless stderr says the file commits in parts. Read the warnings on
stderr before you apply it.
When nothing has changed since the Seal, the script is an empty
transaction and stderr says nothing to migrate. That is a success, not
an error.
Pass --schema <name> when the project registers more than one schema;
without it, migrate picks the schema the way every command does
(The resolver chain).
Choose the two points
Name the start with --from and the end with --to:
retrofit migrate --from 988edcb6 --to 7a6b1234 # between two ops
retrofit migrate --from root --to last # the whole history
retrofit migrate --from seal^ --to seal # the last sealed release
--from is the point the database is at now: the script is only correct
against a database in exactly that state. retrofit op show --include-sealed prints each op, the sealed ones included, with its id
first, as in AddColumn(988edcb612ab, prev=…, …); any unique prefix of
four or more characters names it. --tail, --filter and --touching
narrow the list (retrofit op show --help). root,
last and seal name the start, the last op and the latest Seal, and
^ steps back one. The window lists
every form.
Start from a Seal where you can. An unsealed op can move as the log
grows, and migrate prints a note when --from names one.
Start from where the database is
When you do not know which point a database is at, dump its schema and let migrate find it:
pg_dump --schema-only -d <database> > db.sql
retrofit migrate --from-dump db.sql > migration.sql
The script then starts where the dump places the database and ends at
the last op, so it also applies every unsealed op. Add --to seal for a
database that takes only sealed releases.
To check the point without emitting anything, as a gate in a deploy
job, run identify:
retrofit identify db.sql --expect seal
It exits 0 only when the dump is at the point you name. When the dump
holds objects the project does not own, such as a runner’s history
table, list them under [[exclude]] in retrofit.toml so they are not
read as differences (Exclude objects the project does not
own shows the entry). Starting from a dump
covers a dump that matches no point.
Read the script before you apply it
Every script opens with a header:
-- engine: postgres 17
-- assumes database is at 0f3c1a9b2d4e
-- envelope: own; apply this file outside any open transaction (psql only: it carries \set ON_ERROR_STOP; use --envelope caller for a driver)
The assumes line names the point the script starts from, and the
envelope line says how the file must be applied. A statement that
discards data has a -- destructive: comment above it. stderr lists
each warning: a lock that blocks writers while it is held, rows a data
record left out, and the like. Output
describes every line.
Choose how the file is applied
The default file opens its own transaction and runs only under psql.
If your runner opens a transaction around the file, sends the file as
one query, or sends each statement on its own, emit for that instead:
retrofit migrate --envelope=caller > migration.sql # the runner opens one, or sends one query
retrofit migrate --envelope=independent > migration.sql # the runner sends each statement alone
Choose who opens the transaction covers each case. To make a statement give up rather than wait behind a long query for a lock, see Bound how long a migration waits for a lock. A file that stopped part way is covered in Recover after a failed apply or a merge.
Include seed rows
Reference rows recorded in the data corpus are always in the script, at the point they were recorded. Seed datasets, such as test fixtures, are included only when you name them:
retrofit migrate --label env:dev # include the env:dev dataset
retrofit migrate --label base --label env:dev # layer env:dev over base
Seed rows follow all of the schema changes. A dataset you did not name
is listed in a warning on stderr; pass --allow-unnamed-label <label>
to say a dataset is left out on purpose. Preserved
DML says where each kind of record
goes, and retrofit data describes the corpus.
Roll a deploy back
retrofit migrate --down --from seal^ --at seal # undo the last sealed release
retrofit migrate --down --from-dump db.sql # from where the database is, back to the latest Seal
--down prints the reverse of the window: the statements that take a
database at the end of the window back to its start. --at names the
point the database is at now (default: the last op), and --from the
point to return to (default: the latest Seal). With --from-dump, the
dump names where the database is.
A down drops what the window created, so a column the window added loses the rows written since, and a table the window dropped comes back empty. The script’s header lists each such loss, every rule it puts back, and what it cannot restore. Read it before you apply the script; where the loss is not acceptable, write a forward change that undoes the release instead. Back to the latest Seal means back to the Seal’s schema: a change you adopted into the log after the Seal is undone too.
migrate never writes the log, so a down nobody records leaves the log
ahead of the database. That suits a system you rebuild from the log.
When the log is shared, or describes a database that has to stay at a
cut, record the down with
retrofit op revert over the same
range:
retrofit migrate --down --from seal^ # print the reverse
retrofit op revert --from seal^ # append it to the log
op revert prints the op a database the down took back now names as its
cut.
A down prints a hint that names how to catch the log up, and the choice
follows from whether the window holds a Seal. Unsealed ops can still be
taken out or changed, so the hint names op remove or op revise, then
drift for what remains. A Seal freezes the ops before it, so over a
window that holds one the hint names op revert, which appends the
reversal. op revert runs over either kind of window, and the ops it
reverses stay in the log. Do not use drift for a step that moved rows:
it records the schema a dump shows, so it logs that step as drops and
adds, and a database migrated over them loses the rows the reverse kept.
When a window cannot be reversed, migrate prints no script and names a
way back, usually a --from that leaves the blocking op outside the
window. A raw script op reverses only if it declares how: add its
reversal with retrofit op add DeclareReversal target=<opid> and one of
reverse_body=, reverse_noop= or reverse_blocked=. The reverse
migration covers each case,
and How a reverse script is
built the
model.
To inspect the earlier shape before you roll back, --down --into-schema <schema> writes a copy of it beside the live schema,
filled from the live rows.
Rehearse in a copy of the schema
retrofit migrate --into-schema staging > copy.sql
The script creates a copy of the end point under the schema staging,
beside the live one, and fills its tables from the live rows. The copy
leaves out secondary indexes, policies and comments, and migrate prints
no script for a window that moves rows between tables, or for a schema
with functions, triggers or views. For a full rehearsal, restore a dump
of the database into a scratch database and apply the in-place script
there. The copy script lists
what the copy holds.
Rebuild a schema from scratch
A migration only holds the changes between two points. To drop a schema
and build it again at one point, use snapshot, which re-creates
everything it drops:
retrofit snapshot --at <opid> --include-drop=scoped # drop each object, then create
retrofit snapshot --at <opid> --include-drop=schema # drop the schema, then create
Emit the schema as SQL covers it.
Emit for another PostgreSQL major
retrofit migrate --using=postgres:18
The output is valid at the major the project pins (engine_version).
--using emits for another supported major for one run and writes
nothing to retrofit.toml. PostgreSQL versions
says what each major supports.
Splitting an optional attribute into a sparse child table
A hand-written split often moves an optional attribute into its own
table and keeps that table sparse: a child row only where the
attribute is present. ExtractTable does not split that way. Its child
that copies the source primary key takes a row for every source row,
including rows whose moved columns are all NULL, so on a populated
table a moved column can never take NOT NULL. For a sparse child,
author the split as a keyed MoveColumn per column.
The worked example is the shape of Concourse’s
separate_pins_from_resources migration. It takes a resource’s pin out
of
CREATE TABLE resources (
id integer PRIMARY KEY,
name text NOT NULL,
api_pinned_version jsonb,
pin_comment text
);
into resource_pins (resource_id, version jsonb NOT NULL, comment_text text NOT NULL), keyed by resource_id and holding a row only for a
pinned resource.
1. Create the child and its key. The key column, its NOT NULL and
primary key, and a foreign key back to the source. Choose on_delete
for the relationship; Concourse cascades.
retrofit op add CreateTable public resource_pins
retrofit op add AddColumn public.resource_pins resource_id integer
retrofit op add AddNotNull public.resource_pins.resource_id
retrofit op add AddPrimaryKey public.resource_pins resource_pins_pkey --column=resource_id
retrofit op add AddForeignKey public.resource_pins referenced_table=public.resources \
on_delete=cascade name=resource_pins_resource_id_fkey --column=resource_id --referenced_column=id
2. Clear the values the domain considers invalid. A pin_comment
on an unpinned resource has no pin to belong to, and Concourse discards
it. The operators do not decide that, so author it as its own step,
ahead of the moves, and say what it drops. Its reverse is empty: on
--down the cleared comments do not come back.
retrofit op add RunRawScript \
'DO $$ BEGIN UPDATE public.resources SET pin_comment = NULL WHERE api_pinned_version IS NULL AND pin_comment IS NOT NULL; END $$;' \
'reverse_body=DO $$ BEGIN END $$;' \
'reason=drop the pin_comment of every unpinned resource'
3. Move each column along the key.
retrofit op add MoveColumn public.resources.api_pinned_version public.resource_pins --key_pair="from=id,to=resource_id"
retrofit op add MoveColumn public.resources.pin_comment public.resource_pins --key_pair="from=id,to=resource_id"
The pair runs from the source primary key to the child key. Where a
moved value IS NOT NULL and the child has no row for that key yet, the
move inserts one, and reports it as a padded N row(s) notice. It then
copies each value, checks the copy, and drops the source column. A
source row with no value gets no child row. migrate --down moves every
value back.
4. Rename to the target shape, then add NOT NULL.
retrofit op add RenameColumn public.resource_pins.api_pinned_version version
retrofit op add RenameColumn public.resource_pins.pin_comment comment_text
retrofit op add AddNotNull public.resource_pins.version
retrofit op add AddNotNull public.resource_pins.comment_text "fill=''"
fill= is the hand-written COALESCE(pin_comment, ''): it rewrites the
NULLs left to '' before SET NOT NULL. op add warns that this loses
data (each NULL becomes ''), and the down leaves the '' in place.
Without step 2 the child is not sparse in version. The
pin_comment move pads a child row for each unpinned resource holding
a comment, and that row’s version is NULL. AddNotNull on version
then fails at apply: PostgreSQL reports column "version" of relation "resource_pins" contains null values. Add that NOT NULL between the
two moves instead and the pin_comment move stops at apply, before it
writes a row, with a hint to clear the unpartnered values. Either way, step 2 is
the way out. Nothing in retrofit drops those comments for you.
Rolling back. A --down across the whole window prints no script,
because the renames and NOT NULLs change the moved columns the moves
read. Its hint names a rollback in two steps, split at the last
MoveColumn.
Related
retrofit migrate: every flag, the output, and each refusal.- How a migration applies: the model.
- Choose who opens the transaction, Bound how long a migration waits for a lock, Recover after a failed apply or a merge: applying the script.
- Emit the schema as SQL: the schema at one point.
- drift: record a change made outside the log.
- Operators: every operator.