retrofit
0.13.0
retrofit.sh

retrofit snapshot

retrofit snapshot writes the schema at one point in the oplog to stdout as SQL: its DDL, the rows of its reference tables, and the seed datasets --label names. It reads the oplog, the configuration and the data files under the schema’s data_root, and writes no file except a --skip-manifest. Emit the schema as SQL is the task guide.

Synopsis

From retrofit snapshot --help:

Usage:
  retrofit snapshot [flags]

Description

snapshot replays the oplog up to one point, the cut, and writes what the replay builds: the live tables, columns, constraints, indexes and views, with their names and types at the cut, in the order valid DDL needs. The schema is the one --schema names, or the one the resolver chain selects. The same oplog, cut, corpus and flags give byte-identical output.

The cut

--at names the cut, and the default is last. It takes an opid (an op’s 12-hex id), a prefix that matches one opid, or an anchor:

--atThe cut
lastAfter the last op (the default).
sealAfter the last Seal (Sealed and unsealed ops).
rootBefore the first op. It has no elements to render and refuses with opid-not-found.
last^, last~NOne op, or N ops, before the last op.
seal^, seal~NOne Seal, or N Seals, before the last Seal: a step goes to a Seal, not to the op before it.
<opid>After that op.

root takes no step. A step past the oldest op or Seal, seal on an oplog with no Seal, and a step from root refuse with ref-out-of-range. A prefix that matches more than one opid refuses with ambiguous-opid.

An unreconciled fork between the first op and the cut refuses with fork-in-range, as migrate does for the same fork, and the hint names retrofit reconcile with the fork’s shared prev. A cut at or before that shared prev renders normally. The check runs before the data band is assembled, so a labeled run refuses the same way and writes no DDL and no data.

Output

A DDL document on stdout, in this order:

  1. Banner, one comment line (unless --no-header):

    -- retrofit snapshot v1 opid=<12-hex> engine=postgres engine_version=<major> include-drop=<mode>
    

    Under --ddl-only, and only then, the line ends in one more token, data=omitted, whether or not the band held a record. The banner names neither the schema nor the cut: its opid is a hash of the DDL body, the data band excluded, and not an oplog opid, so --at does not take it.

  2. DROP prologue, per --include-drop (see The drop prologue).

  3. CREATE EXTENSION statements first (some create schemas).

  4. CREATE TABLE statements in dependency order, and any function or view a table needs.

  5. The data band’s state records: the rows that state a table’s whole content, under a -- retrofit:snapshot-data v1 header (see A full snapshot and a data-only band).

  6. ALTER TABLE statements for constraints, comments, replica identity and defaults: the Set* family pg_dump round-trips.

  7. CREATE FUNCTION / VIEW / MATERIALIZED VIEW statements in dependency order. A materialized view is created WITH NO DATA.

  8. CREATE INDEX statements after the tables they belong to.

  9. CREATE TRIGGER / POLICY statements and RLS toggles.

  10. COMMENT ON statements (unless --no-comments).

  11. The data band’s other records, under a header of their own, and inside the transaction when a drop prologue opens one.

  12. Refresh notes, after any COMMIT;: one -- post-deploy: comment line, then two for each materialized view, the second a REFRESH MATERIALIZED VIEW to run after the apply. They are comments, so applying the document runs none.

Either part of the band is omitted when it holds no record (see The data band).

Under --as=json the document is the ddl member of a {"type":"snapshot"} record and holds the DDL only; the band, in one part, is a snapshot-data record of its own. snapshot records lists every record and their order.

SELECT * views

A view defined with a star projection is emitted with the star replaced by an explicit column list, in the source table’s ordinal order, as pg_dump prints it:

-- authored
CREATE VIEW app.vv AS SELECT * FROM app.t;

-- snapshotted
CREATE VIEW app.vv AS SELECT id, a, b FROM app.t;

A qualified t.* expands the same way, and a star over a join expands source by source in FROM order. The list is frozen when the view is defined: adding app.t.c after the view exists leaves app.vv projecting id, a, b in every later snapshot. A star that cannot be expanded is listed under What the output leaves out.

Type references

Inside a rendered body (a view query, a function body, a CHECK expression, a column default), a reference to a type the oplog manages is emitted qualified and quoted, as ::"schema"."typename". Built-in and extension types (text, int4, jsonb) stay bare. A snapshot therefore applies to a fresh database with no search_path set.

The drop prologue

--include-drop selects what precedes the CREATE statements, and a bare --include-drop is scoped:

  • none (default): no DROP statements and no transaction.
  • scoped: DROP … IF EXISTS for the foreign keys, indexes and tables in the emission set, and for every view and materialized view the document writes, then the CREATE statements. Nothing else in the schema is dropped. A table outside the set with a foreign key into the set, or an object left in place (a view outside the set, a function, a policy) that depends on a dropped table or view, makes the apply fail. With --table or --exclude-table, only the included tables’ foreign keys, indexes and tables are dropped, and every view the document writes still is.
  • schema: one DROP SCHEMA IF EXISTS <name> CASCADE per schema, then the CREATE statements. The cascade drops every dependent outside the emission set too. With --table or --exclude-table it refuses with flag-conflict.

scoped and schema wrap the document in BEGIN; and COMMIT;, so a failed apply under psql -v ON_ERROR_STOP=1 rolls back.

Filters

--table, --exclude-table and --exclude-schema are pg_dump’s -t, -T and -N. A name matches the registered name exactly, as <name> or <schema>.<name>, with no glob (pg_dump parity), and retrofit snapshot --help-all gives how the filters combine with each other and with --schema. A name the cut does not hold refuses with table-not-in-cut or schema-not-in-cut, and a foreign key from an included table to an excluded one refuses with fk-cross-schema-unreachable.

--table and --exclude-table select rows as well as DDL: a table they leave out has no rows in the data band, ref rows included. Each corpus record left out is counted under the cause table-filtered, per purpose and table: a data-excluded record under --as=json, and an apply-risk-data-exclusions-table line at -v in text. A record is matched to its table by identity, so one captured under a table’s earlier name follows the rename.

Without the data band

--ddl-only emits the document snapshot emits with the data band left out: the schema-only shape (pg_dump --schema-only). Removing the band from a snapshot and the data=omitted token from a snapshot --ddl-only gives the same bytes. Under --as=json the snapshot record is emitted and no snapshot-data record. It exits 0.

The band is still resolved, so every refusal it raises (data-unavailable-at, data-layer-ambiguous, a witness mismatch, an unresolved anchor) refuses this run too. Every record the band would have applied is then counted under the cause ddl-only, per purpose and table, one count per corpus record: a data-excluded record under --as=json, and an apply-risk-data-exclusions-table line at -v in text. A table with no rows to omit is silent, a record excluded under an earlier cause keeps that cause, and the run-level not-selected summary is not printed. Under --no-header only the run report records the omission.

--ddl-only with --data-only or --label is a flag-conflict, and with --over it is refused as --over is without --data-only. --allow-stale-dml and --allow-reading-replace-body are accepted and have no effect.

The data band

snapshot embeds rows from the data corpus: every addressed data file under the schema’s declared data_root, found with no file argument. The rows form the data band.

  • ref rows apply from the base corpus. A ref record (production reference data) is embedded with no flag and is never label-filtered.
  • seed rows apply when --label names their dataset. Each --label names one dataset by its exact label, and a record with no labels matches none.

Exclusions

Records with any other purpose are excluded, and every exclusion is counted (data-excluded, exit stays 0); a silent run means the corpus held nothing to drop. This covers unclassified records, seed rows no --label named (not-selected), rows a higher layer’s replace floor superseded (superseded), and transition and cleanse records. The history-bound purposes are counted and never applied: retrofit migrate applies them at their anchor op.

Both are experimental. Unless retrofit.toml opts into the purpose ([experimental] purposes), a corpus holding a transition or cleanse record refuses with experimental-not-enabled before any DDL is written, --ddl-only included. With the opt-in, the run counts the records as above and warns experimental-purpose once per such file (Experimental purposes).

--allow-unnamed-label <label> attests that the dataset with that exact label is left out on purpose. It is repeatable, and no form covers every dataset, so a dataset added later still warns. The run-level apply-risk-data-exclusions warning drops the attested labels and fires while any other unnamed dataset remains. Under --as=json the data-excluded record lists them as unnamed_attested. Every other cause still reports, and -v still lists the attested tables. An attestation that answers nothing (the corpus has no dataset with that label, or the run also names it with --label) prints a note that it had no effect.

In text each stderr line names the tables and per-table counts for one cause of exclusion, so one purpose can produce more than one line: a dataset no --label named and a dataset a higher layer superseded are separate lines. Under --as=json the counts are data-excluded records on stdout, after the snapshot record; snapshot records gives their members and order.

Band layout

Each part of the band opens with the -- retrofit:snapshot-data v1 header. When the target schema is not public and nothing above the band creates it, the first part follows its header with:

CREATE SCHEMA IF NOT EXISTS "<schema>";

The DDL above usually creates the schema, so a text document drops the line. The snapshot-data JSON record, which applies on its own, and --data-only, which emits no DDL, keep it.

Every run of rows in the band opens under one designation line, -- Data: <target> (<designation>):

-- retrofit:snapshot-data v1
-- 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 target is the table the rows load, quoted schema and name. The designation is reference, seed, label <label>, transition, cleanse or unclassified. One line opens each maximal run of consecutive groups for one table, purpose and layer; a table interleaved across layers gets one line per run. The line is an ordinary comment: --no-comments and --no-header leave it, and under --as=json it is in the snapshot-data record’s band text.

Every bare relation reference in a data body is emitted naming the relation’s schema at the cut, so the band sets no search_path and does not depend on the applying session’s. The data file on disk keeps its bare identifiers.

Versions, repair and layers

Row blocks are versioned by anchor position: within one (table, label set) chain, the latest disposition=replace block at or before the cut supersedes earlier state, so snapshot --at renders the row set as it stood at that cut.

Before a selected record is emitted, its references are repaired as migrate repairs them, in the emitted body only; the data file on disk is unchanged. A reference that cannot be repaired without changing its meaning refuses as stale-dml-reference unless --allow-stale-dml is given (Stale data references and repair).

Datasets layer: each --label occurrence names one layer, in the order given, lowest first, and a later layer’s disposition=replace floor supersedes the layers below it for that table. A file with several named labels folds in each of those layers and is emitted once, at the highest of them.

A full snapshot and a data-only band

A text snapshot places the band in two parts. A record that states its table’s whole content (a replace section, or a reference restate) loads after the tables and before the constraints, indexes and triggers, where pg_dump places its data, so it fires no trigger: a snapshot-state-under-trigger note names each such table with a trigger retrofit migrate would fire on those rows. Every other record (an append, a delta section, a seed restate) runs after the whole schema, with its constraints, indexes and triggers live, as a migration from the root runs it. Under --as=json, and under --data-only, the band is one part.

A full snapshot renders the band as plain row bodies: the DDL above it has just created every table empty, so no floor has a prologue. After a table’s last row in each part, the band re-seats each of its identity and serial sequences with an annotated SELECT … setval(pg_get_serial_sequence(…), …) guarded on max(<column>) IS NOT NULL, the alignment pg_dump’s data section performs, so the first insert that omits the key takes the next free value. Each re-seat is a query, so applying the script prints one single-row result per sequence under the column name setval; psql -q does not suppress query results, and psql -o /dev/null discards them.

snapshot --data-only emits the band for an existing database: a -- data band: <n> tables, prologue=<none or delete> line (unless --no-header), then the band, and no DDL. A table whose resolved floor is a disposition=replace block has a prologue, so the rows apply as the table’s complete state, rendered as it is on migrate:

  • a keyed table (its primary key, a UNIQUE constraint or a unique index the captured rows write in full; a DEFERRABLE constraint never qualifies) gets a staged block that upserts through a TEMP table and never empties the target;
  • a table with no such key gets a whole-table clear and a staged reload, or refuses as stale-dml-reference (reason key-uncovered) when another table’s foreign key references it;
  • a unique index that shares its columns with a DEFERRABLE UNIQUE constraint refuses as replace-no-upsert-form;
  • a replace body that reads a relation refuses as data-replace-body-reads-relation, unless --allow-reading-replace-body downgrades it to a warning: the body is written verbatim under a WARNING comment and the table’s prologue is a whole-table clear;
  • a body=none floor renders a bare DELETE FROM <table>; clear.

Clears render children before parents; an unreadable cut degrades the order to a deterministic tie-break behind a -- WARNING: comment. Every row body is followed by the same sequence re-seat, inside the staged block when there is one. The replace prologue gives the key order and the marker causes. The staged block needs the TEMP privilege. A secondary UNIQUE the arbiter does not cover, or a surrogate key generated afresh at capture, can make an upsert collide or replace rather than update. Re-applying the same band to a converged target changes no row, and the staged block’s DROP TABLE IF EXISTS opener absorbs a temp table a failed run left behind.

pg_dump --section=data assumes an empty target; a --data-only band with a prologue states the captured rows over whatever the target holds. The band is not atomic across tables unless applied inside one transaction, and a stderr advisory says so. The snapshot-data record’s prologue member reads delete only when a prologue was emitted, so a full snapshot’s band reports none.

Order and duplicates

Within each part of the band, rows are emitted in a deterministic total order: anchor-op position first, then layer rank (the --label order), then file path, then position within the file. Records identical in anchor op, canonical body and label set are coalesced to one, so the same row in both the base corpus and a second layer is emitted once. Two records that state the same row differently at one anchor abort with dml-data-conflict.

The order is corpus order, not a foreign-key topological sort, and a row out of order fails at apply time, not at snapshot time. In a full snapshot the foreign keys are added after the state records and before the others: a state child row ahead of its parent still loads, and one whose parent no state record supplies fails at the later ADD CONSTRAINT, while the other records meet the keys live, so a child row ahead of its parent fails at its INSERT. snapshot warns data-statement-order-fk when a table’s state rows reach a parent only the other records supply, or a child’s other records run before its parent’s. A --data-only band applies to a database whose foreign keys already exist, so there a child row ahead of its parent fails at its INSERT against an immediate foreign key. For seed data with foreign-key dependencies, the anchor positions set the order.

What the output leaves out

The cut is the input, and snapshot renders what the replay builds from it (Replay and genealogy). What the cut does not reach is not emitted.

Statements outside the oplog. A statement import skipped, or one the catalog has no operator for, is not in the cut, and snapshot emits nothing for it. Functions, triggers, policies, extensions and materialized views are in the catalog and are emitted. Standalone sequences, privileges and objects in another schema are not: import lists them on its skip manifest (Skipped statements defines cross-schema).

A column whose DEFAULT nextval('…') draws from such a sequence (a tuned serial’s, for one) keeps that default, so a fresh database rejects its CREATE TABLE until the sequence exists. Each one is named before apply: a -- retrofit: line before the table and a sequence-not-created-by-render warning whose hint gives the statements to run. The sequence’s options were skipped with it, so the hint names the sequence only. A default whose sequence name cannot be read warns sequence-reference-unparsed instead. The run exits 0.

PostgreSQL 18 syntax. snapshot 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, and nothing is written. PostgreSQL versions gives every verb’s row at each major.

A SELECT * view that cannot be expanded. When a top-level star cannot be expanded soundly, no CREATE VIEW is emitted for the view. The run warns (see Render failures) and exits 0. Expansion fails when:

  • the star’s FROM is a derived relation (a subquery or a CTE) with no fixed base-column list;
  • the FROM names a relation that is not a live table in the cut, another view included: expansion reads columns only from base tables;
  • the star sits over a coalescing join (NATURAL JOIN, or JOIN … USING), where the output columns are merged rather than concatenated;
  • a source column was renamed somewhere in the cut’s history, so a re-expanded star would bind to the new name where the original definition froze the old one.

A view with an explicit output column list (CREATE VIEW v (a, b, c) AS …), or a body that names its columns, is not skipped.

A raw script. A RunRawScript op (an anonymous DO block) runs once, when the migration that holds it applies, and has no current-state DDL. snapshot writes no statement for it, so a change the script made to the schema is not in the document. When the cut holds one or more, snapshot prints one note on stderr giving their count and exits 0; retrofit migrate emits the script.

Render failures

A state the replay reaches can still fail to render, at two levels.

An element: skipped, exit 0. When one element’s body cannot be reconstructed from the cut, it is omitted, the document is finished and the run exits 0. Each skip prints one render-skip-element-not-rendered warning, whose skip_cause member is view-body for a view or materialized view and element-body for a function or trigger; the skips of one cause share one text line unless --no-consolidate is given. A view takes view-body for every render refusal, a reference that is not live at the cut included. A function or trigger is skipped when its body references an element that is not live at the cut; at an interior cut that is often the correct historical answer, and at --at last it is a real gap.

The cut: aborted. A failure to materialize the cut at all emits no DDL and refuses with render-blocked-on-unknowns, render-blocked-on-partial-forest or render-blocked-on-replay-error.

Skip manifest

--skip-manifest <path> writes the element skips to <path> as JSONL, in import’s record shape. Records have at least kind, name, schema, raw_stmt_kind, line and raw_text; line and raw_text are import-time fields, so on a snapshot skip they are always 0 and "" rather than absent.

The kind values are view-render and element-render, not the stderr warning’s code, so a filter written against the warning code matches nothing in the JSONL.

An element_id naming the tombstoned element is on element-render records, and is omitted whenever it is unset: always on view-render records, and on any element-render record whose skip supplied no id. src_file is omitted entirely, since an emit-time skip has no source file. The field set is open. The flag does not change the exit status.

The file is written whenever the run reaches the manifest step, including with zero skips (an empty file), so its existence is not evidence that anything was skipped, and its absence is not evidence that nothing was. The write follows the DDL, so a run that returns before it leaves no file: an empty or header-only oplog, a bad flag combination, an unresolvable cut, or a render-blocked-* abort.

A path naming the active oplog or the resolved configuration file (a custom path from --config or RETROFIT_CONFIG included) refuses with skip-manifest-clobbers-managed-file, on any run that reaches the manifest step. The check runs at write time, after the DDL document is on stdout: the refusal protects the managed file, not the run’s output.

Emit target

--using=<engine>:<major> overrides the emit target for one run and writes nothing to retrofit.toml. retrofit snapshot --help-all gives the target a run takes without it, and retrofit schema engine <version> sets the pin. PostgreSQL versions lists the supported majors.

Options

From retrofit snapshot --help:

Flags:
      --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-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
      --at root                           emit DDL for the cut at <opid> prefix or anchor (root/`last`/`seal`, optional `^`/`~N`) (default: last)
      --data-only                         emit the data band only: the banner (unless --no-header), then the band as snapshot emits it; no DDL
      --data-root data_root               root that overrides the schema's data_root
      --ddl-only                          emit the snapshot without its data band (schema only); the banner ends in data=omitted and the omitted rows are counted on the run report
      --exclude-schema strings            drop schemas matching <name> from emission (repeatable; pg_dump -N)
      --exclude-table strings             drop tables matching <name> or <schema>.<name> from emission (repeatable; pg_dump -T)
      --extra-seed-path seed_paths        root that appends to the declared seed_paths
  -h, --help                              help for snapshot
      --help-all                          show extended help (vocabulary, semantics, caveats)
      --include-drop string[="scoped"]    DROP prologue before the CREATEs: none | scoped | schema (default "none")
      --label stringArray                 apply the dataset carrying this exact label (repeatable; argv order is layer order; byte-exact, never trimmed; never cuts ref data)
      --no-comments                       suppress every COMMENT ON statement (pg_dump --no-comments)
      --no-header                         suppress the canonical banner line
      --over string                       with --data-only: emit only the tables the LAST-named --label's layer contributed to (a projection, never a diff)
      --schema <name>                     registered schema <name> (resolves via the chain when omitted)
      --seed-path seed_paths              root that replaces the declared seed_paths
      --skip-manifest string              write skipped view-render records (JSONL) to <path>
      --table strings                     restrict emission to tables matching <name> or <schema>.<name> (repeatable; pg_dump -t)
      --using string                      one-shot emit target override (format: <engine>:<major>; read-only)

retrofit data describes data_root and the seed_paths roots. The global flags (--as, -C, --config, -f, -q, -v, --no-consolidate) are in the output contract.

Diagnostics

A refusal writes no artifact, except skip-manifest-clobbers-managed-file, which is raised after the DDL is on stdout (see Skip manifest). Refusal codes lists every code snapshot prints, with its exit status.

Advisory codes lists the warnings and notes snapshot prints, with every other advisory and its class.

See also

Emit the schema as SQL, The oplog, pg_dump parity, retrofit migrate, retrofit data, Operators, Output contract.

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

Published .