Import existing SQL
retrofit import turns .sql files you already have (a schema file, a
pg_dump --schema-only, a set of migration scripts) into an
oplog, the log of operators (ops) your schema
is recorded in. Each statement it understands becomes an op, in the
order the files state them (a drop a function body reads can move; see
Drops read by function
bodies); it
reads the files and never changes them, and never touches a database.
retrofit import is the reference: every flag, every
statement import recognizes, and each refusal and exit code.
Import a schema file
retrofit import --schema sales schema.sql
The output lists each op as it is appended, then a summary line. When
sales is not registered yet, the run registers it and writes
sales.oplog, so nothing has to be set up first beyond retrofit init.
Without --schema, import picks the schema the way every command does
(The resolver chain).
- reads one source from standard input:
retrofit import --schema sales - < schema.sql
Import several files
retrofit import --schema sales 01_tables.sql 02_views.sql
The files are imported as one batch, in the order you pass them: if any file fails to parse or validate, nothing is written. A batch that skips content retrofit cannot represent still writes the ops it recognized before it fails (Skipped statements). A migration tree whose order matters is covered in Import a migration tree in order.
Preview with --dry-run
retrofit import --schema sales schema.sql --dry-run
--dry-run runs the whole import, prints the ops it would append and
stops on the refusals the real run raises, and writes no ops. The two
guards on --force print a preview instead of refusing. A
schema not registered yet stays unregistered.
Dry run has the details.
Keep the rows your SQL inserts
INSERT, UPDATE, DELETE and MERGE statements are not ops. import
writes each one, as written, to a data file beside the oplog, one file
per table (The data file), which snapshot
and migrate read when they emit SQL. Say what the rows are when you import them:
retrofit import --schema sales lookups.sql --ref currency # reference rows, in every emit
retrofit import --schema sales seed.sql --label env:dev # a dataset you name at emit time
retrofit import --schema sales backfill.sql --purpose transition # a one-time data change
A one-time data change (--purpose transition or --purpose cleanse)
is experimental: it works only in a project whose retrofit.toml opts
into it (Experimental purposes). A
backfill written as a DO block needs no opt-in.
A statement you give no such answer for is listed in the output and
written nowhere. To import the DDL alone, add --ddl-only.
Preserved DML has the
classification rules.
Check what was skipped
Statements outside retrofit’s vocabulary, such as standalone sequences
and GRANT, are listed in the output and not recorded. The summary line
counts them by kind:
imported 12 ops; skipped 2 statements (1 sequences, 1 GrantStmt)
To keep the list in a file:
retrofit import --schema sales schema.sql --skip-manifest skips.jsonl
When a skip drops something no op can hold, the run still writes the other ops and ends with an error naming the kinds. Read the skipped statements, then decide whether to accept the loss. Skipped statements lists every case.
Mark the baseline stable
retrofit import --schema sales schema.sql --seal
--seal adds a Seal after the imported ops, which marks them as a
shipped baseline later work builds on (Sealed and unsealed ops). Its reason names the files in
the order you imported them. To seal each file of a migration history
on its own, see Seal each
migration.
Extend or start over
Importing into a schema that already has ops extends it: the new ops follow the existing ones.
retrofit import --schema sales 03_indexes.sql
This gives the same ops as importing all the files in one run, apart
from their ids. To start over from the files instead, add --force,
which discards the oplog’s existing ops:
retrofit import --schema sales schema.sql --force
A sealed history, or data files tied to the old ops, stop a --force
run until you name them; Extending and
replacing has the flags.
A branch that kept adding to the old history cannot merge with the new
one: add its ops again on the new history.
Import a dump of a schema you already track
A pg_dump --schema-only of a database describes where the schema is
now, not the steps that got it there. Offered to a schema that already
has ops, import prints an error and writes nothing, since appending it
would record every object twice. The error’s hint names the command
for what you meant:
- The database changed outside the oplog and the oplog should catch
up: run
retrofit drift <source>, which records the difference as new ops. This is the usual answer. - You want to start over from the dump: run
retrofit import --force <source>. The existing history is gone. - You wanted the rows, not the schema: run
retrofit data capture <source>, which writes the dump’s rows to data files and leaves the oplog as it is. A dump whose rows areCOPYblocks needspg_dump --insertsor--column-insertsfirst.
Authoring ops: import vs
drift explains the
difference between the two commands.
Related
retrofit import: every flag, statement and refusal.- Import a migration tree in order: pass a tree’s files in order and seal each one.
- The oplog: the oplog, the cut and element identity.
- drift: record a schema changed outside the oplog.
retrofit data: the data files and their labels.- Operators: every operator and which commands handle it.