Emit the schema as SQL
retrofit snapshot prints your schema as SQL: its DDL, the rows of its
reference tables, and any seed datasets you name. It reads the
oplog, the log of operators (ops) your schema
is recorded in, and never changes it. Pipe the output to psql to build a
database, or keep the file as a schema dump.
retrofit snapshot is the reference: every flag, the
order of the output, and each refusal and exit code.
Emit the current schema
retrofit snapshot > schema.sql
psql -v ON_ERROR_STOP=1 -d <database> -f schema.sql
The file holds every live table, column, constraint, index and view, in an order that applies to an empty database, and the rows of the tables you declared as reference data. Read the warnings on stderr before you apply it (see Check what was skipped).
Pass --schema <name> when the project registers more than one schema;
without it, snapshot picks the schema the way every command does
(The resolver chain).
Choose the point in the log
By default the output is the schema after the last op. To emit the
schema as it stood earlier, name a point with --at:
retrofit snapshot --at seal # as of the last Seal
retrofit snapshot --at last^ # one op before the last
retrofit snapshot --at 988edcb612ab # after this op
retrofit op show --include-sealed lists each op with its id, the
sealed ones included. A Seal marks the end of
the history a migration shipped (Sealed and unsealed ops). The cut
lists every form --at takes.
Reconcile a merge first
After a merge in which both branches appended ops, the oplog can hold
two histories side by side. snapshot then prints an error and no SQL;
its hint names the retrofit reconcile command for that fork. Run it to
see the order it would write (it writes nothing), then write the order
Reconcile after a
merge gives, and emit
again.
A point before the fork, named with --at, still emits.
Emit only some tables or schemas
retrofit snapshot --table users --table orders # only these tables
retrofit snapshot --exclude-table audit_log # every table but this one
retrofit snapshot --exclude-schema staging # leave a schema out
These match pg_dump’s -t, -T and -N, with exact names and no
patterns. A table left out loses its rows too. When a table you keep has
a foreign key to a table you leave out, keep both.
Filters has the rest.
Prepare the target database
By default the output only creates objects, which suits an empty
database. To apply it over a database that already holds the schema,
add a drop step with --include-drop:
retrofit snapshot --include-drop=scoped # drop each emitted table first
retrofit snapshot --include-drop=schema # drop each whole schema first
scoped drops the tables, indexes, foreign keys, views and
materialized views the output recreates, and leaves everything else in
place. Another table’s foreign key into those tables, or an object left
in place that depends on a table or view it drops (a view the output
does not write, a function, a policy), stops the drop when you apply;
use schema then.
schema drops each schema with CASCADE, which removes every object in
it, so use it only on a schema you own outright. Both run the script in
one transaction, so a failed apply under psql -v ON_ERROR_STOP=1
leaves the database as it was.
The drop prologue has the
details.
Include reference and seed rows
Rows come from the data files kept beside the oplog (the data corpus). The rows of tables declared as reference data are always in the output. A seed dataset, such as test fixtures, is in the output only when you name its label:
retrofit snapshot --label env:dev # add the env:dev dataset
retrofit snapshot --label base --label env:dev # layer two datasets, in that order
To see what a label selects without emitting SQL, run
retrofit data list --label env:dev.
When a dataset is left out, a warning on stderr names its label. To
leave one out on purpose and quiet the warning, name it with
--allow-unnamed-label <label>. The data
band has the rules for layers,
exclusions and ordering.
Emit the schema without rows
retrofit snapshot --ddl-only > schema.sql
--ddl-only leaves every row out, which is the shape of
pg_dump --schema-only; pg_dump parity compares
the two option by option. The data files are still read, so a problem in
them stops this run too. The first line of the file ends in
data=omitted.
Load rows into an existing database
retrofit snapshot --data-only --label env:dev > rows.sql
psql -v ON_ERROR_STOP=1 -1 -f rows.sql
--data-only emits the rows alone, for a database whose tables already
exist. A table captured as a complete set of rows is brought to exactly
that set: rows with a matching key are updated, missing rows are added
and the rest are removed. Apply it in one transaction (-1) under a
role that may create temporary tables. Applying the same file again
changes nothing once the database matches it.
To load only the tables the top layer touches, add --over with the
last label:
retrofit snapshot --data-only --label base --label env:dev --over env:dev
A full snapshot and a data-only band describes what each table’s block does.
Check what was skipped
A run that leaves something out still prints the rest of the schema and reports each omission on stderr. Read those lines before you apply.
- A view is missing. Its body could not be rebuilt at that point in
the log, often a
SELECT *over a subquery, another view or aNATURAL JOIN, or a*left in a CTE, a subquery or aUNIONarm. Name its columns in the body, in every CTE, subquery andUNIONarm too, or recreate it after you apply. An explicit column list (CREATE VIEW v (a, b, c) AS …) helps only a star at the top level. - A function or trigger is missing. Its body names an element that no longer exists at that point. At an earlier point that is expected; at the last op, the body needs revising.
- A sequence is not created. A column default draws from a sequence the oplog does not hold. Create the sequence before you apply; the warning prints the statement.
- A raw script is not in the output. A
DOblock recorded in the oplog is left out, and stderr gives the count;retrofit migrateemits it.
To keep a list of the skipped views and functions in a file:
retrofit snapshot --skip-manifest skips.jsonl > schema.sql
What the output leaves out and Render failures list every case.
Emit for another PostgreSQL major
retrofit snapshot --using=postgres:16
--using changes the target for one run and leaves retrofit.toml as
it is. To change it for good, run retrofit schema engine <version>.
PostgreSQL versions lists the supported
majors.
Related
retrofit snapshot: the reference for every flag, the output and each refusal.- The oplog: the oplog, replay and identity.
- pg_dump parity: snapshot’s flags beside
pg_dump --schema-only’s. - migrate: emit the script between two points in the log.
- drift: the other direction, recording a schema changed outside the oplog.
retrofit data: the data files and their labels.