Record a schema changed outside the oplog
When a database’s schema changes outside the
oplog (a hotfix applied by hand, a change made
in a console), retrofit drift reads a dump of that database, works out
what changed since the oplog’s last op, and appends the ops (operators)
that record it. Where a change has more than one reading, such as a
rename or a drop and an add, it asks you which one.
retrofit drift is the reference: every flag, what drift
recognizes and skips, and each refusal and exit code.
Preview what drift would record
Dump the schema, then run drift with --dry-run:
pg_dump --schema-only --schema app <database> > dump.sql
retrofit drift --dry-run dump.sql
drift plan: 1 addition, 1 removal, 0 questions
+ 809b DropColumn app.orders.total
+ (preview) AddColumn app.customers.phone
would drift 2 ops; 0 questions; skipped 0 statements
A dry run writes nothing. Each line is an op drift would append; a line
opening with ? is a question it would ask. Pass --schema <name> when
the project registers more than one schema; without it, drift picks the
schema the way every command does (The resolver
chain).
The schema must already be in the oplog. To start an oplog from a dump, import it instead.
Record the changes
Run the same command without --dry-run:
retrofit drift dump.sql
The ops are appended to the oplog’s unsealed tail, where you can still
revise or remove them (Sealed and unsealed ops). Read the warnings on stderr: a
rename or a new NOT NULL, for one, prints what it risks when a
migration applies it. To see the SQL a database would
run, emit the migration (Emit a migration script).
A source that is not a schema-only dump works too: any DDL that states the schema’s current shape.
Answer drift’s questions
When a change has more than one reading, drift opens your editor
($EDITOR or $VISUAL) on one line per question, the default answer
already written:
# ---- column-rename @ dump.sql:1 ----
# from: text NULL
# to: text NULL
rename 382b905807a9 contact
Keep the line to accept the default, edit it to give another answer, then
save and quit. The comment lines at the top of the buffer list the
answers: a rename with a new name, a drop and an add, and a cast. For a
table that was copied, extracted, joined or merged, the answer line
already holds accept when drift recognized the operator, and
accepting records it.
Answering with a drop and an add records what changed, but the moved
columns lose their identity (their ElementID).
Save the buffer with no answer line to cancel; nothing is written.
A database deployed out of order can skip a logged drop or rename yet
hold an op logged after it. Where drift would put the element back, it
asks a reversal question, and the header also lists keep and
accept. keep, the default, leaves the log as it is; accept
records the restore (Questions).
With no editor set, a run that has questions stops and writes nothing. To record everything else and leave the questions for a later run:
retrofit drift --on-question=skip dump.sql
Each unanswered question is then listed with the skips. Questions has the answer grammar and every policy.
Keep drops out of the oplog
By default drift records a drop for anything the dump no longer has. A drop in the oplog changes no database; a migration that applies it does. To record the additions now and review the drops apart, withhold them:
retrofit drift --ignore-drops dump.sql # no drops at all
retrofit drift --ignore-drops=table,column dump.sql # no table or column drops
Each withheld drop is still listed, marked (deferred), so a CI job that
runs retrofit drift --ignore-drops --dry-run against production
reports both the additions to record and the drops to look at. An answer
that would need a withheld drop stops the run; answer another way, or
withhold fewer kinds. Drops lists the kinds.
Keep reference rows in step
The rows of the tables you declared as reference data (currency codes,
country lists) are part of the schema, so drift records them in the same
run. Dump with the rows as INSERT statements:
pg_dump --inserts --schema app <database> > dump.sql
retrofit drift dump.sql
A table whose rows changed is written to a new data file beside the
oplog, and the report names it with the rows added and removed. To
declare a table first, run retrofit schema ref-tables add <table>, or
pass --ref <table> in the run that creates it. Seed rows, such as test
fixtures, are not schema; capture them with retrofit data capture --label <label> <source>. Reference
data has every case drift reports
instead of capturing.
Record a table split or merge yourself
Some changes move columns or rows between tables. drift asks about a copy, an extract, a join or a merge, but when a table keeps some columns and an added table takes the rest, it can only offer a drop and an add, which loses the moved columns’ identity. Its warning prints the command that keeps it:
retrofit op add ExtractTable app people --into 'addresses=city,street'
retrofit drift --dry-run dump.sql
Name only the columns that moved; the new table’s key back to the source is added for you, under the names the warning gives. Once the op is in the oplog, and the dump declares that key under those names, the next drift run plans nothing for that change. The same goes for a split of a table’s rows, which drift never infers, since a dump does not show the predicate that routed each row:
retrofit op add SplitTable app orders "orders_open,orders_closed" --conditions="predicate=closed_at IS NULL" --conditions="predicate=closed_at IS NOT NULL"
retrofit op add <Operator> --help prints each operator’s arguments,
and Operators lists them. Data-motion
operators gives what drift
recognizes for each.
Mark the result stable
A Seal marks the end of the history a migration ships (Sealed and unsealed ops). To seal drift’s new ops in the same run:
retrofit drift --seal dump.sql
The Seal’s reason is drifted to dump.sql; --seal="<reason>" gives
your own. A run that records nothing writes no Seal. When the range
holds reference rows captured earlier and never witnessed, the run
stops before writing; run retrofit data witness, then seal.
Seals has the rest.
Check what was skipped
drift lists each statement it records nothing for, with its kind and
class: grants, an object in another schema, a constraint shape no
operator holds. Most are expected. A skip that loses content no
operator holds stops the run with a non-zero exit, after the rest is
recorded, and the hint names each kind. Model the change by hand,
narrow the run with --schema, or accept the loss with
--allow-capability-gaps. A pg_dump --clean dump usually needs that
flag. Skips and the exit
status lists every
class.
A dump from a managed database also holds objects your project does not own; Exclude objects the project does not own keeps them out.
Related
retrofit drift: the reference for every flag, the report and each refusal.- The oplog: the oplog, the cut and element identity.
- Import existing SQL: start an oplog from a dump.
- Emit a migration script: the SQL that applies what drift recorded.
- Work with row data: the data files and their labels.