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:
--at | The cut |
|---|---|
last | After the last op (the default). |
seal | After the last Seal (Sealed and unsealed ops). |
root | Before the first op. It has no elements to render and refuses with opid-not-found. |
last^, last~N | One op, or N ops, before the last op. |
seal^, seal~N | One 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:
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: itsopidis a hash of the DDL body, the data band excluded, and not an oplog opid, so--atdoes not take it.DROP prologue, per
--include-drop(see The drop prologue).CREATE EXTENSION statements first (some create schemas).
CREATE TABLE statements in dependency order, and any function or view a table needs.
The data band’s state records: the rows that state a table’s whole content, under a
-- retrofit:snapshot-data v1header (see A full snapshot and a data-only band).ALTER TABLE statements for constraints, comments, replica identity and defaults: the
Set*familypg_dumpround-trips.CREATE FUNCTION / VIEW / MATERIALIZED VIEW statements in dependency order. A materialized view is created
WITH NO DATA.CREATE INDEX statements after the tables they belong to.
CREATE TRIGGER / POLICY statements and RLS toggles.
COMMENT ON statements (unless
--no-comments).The data band’s other records, under a header of their own, and inside the transaction when a drop prologue opens one.
Refresh notes, after any
COMMIT;: one-- post-deploy:comment line, then two for each materialized view, the second aREFRESH MATERIALIZED VIEWto 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): noDROPstatements and no transaction.scoped:DROP … IF EXISTSfor the foreign keys, indexes and tables in the emission set, and for every view and materialized view the document writes, then theCREATEstatements. 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--tableor--exclude-table, only the included tables’ foreign keys, indexes and tables are dropped, and every view the document writes still is.schema: oneDROP SCHEMA IF EXISTS <name> CASCADEper schema, then theCREATEstatements. The cascade drops every dependent outside the emission set too. With--tableor--exclude-tableit refuses withflag-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.
refrows apply from the base corpus. Arefrecord (production reference data) is embedded with no flag and is never label-filtered.seedrows apply when--labelnames their dataset. Each--labelnames 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
UNIQUEconstraint or a unique index the captured rows write in full; aDEFERRABLEconstraint never qualifies) gets a staged block that upserts through aTEMPtable 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(reasonkey-uncovered) when another table’s foreign key references it; - a unique index that shares its columns with a
DEFERRABLEUNIQUEconstraint refuses asreplace-no-upsert-form; - a
replacebody that reads a relation refuses asdata-replace-body-reads-relation, unless--allow-reading-replace-bodydowngrades it to a warning: the body is written verbatim under aWARNINGcomment and the table’s prologue is a whole-table clear; - a
body=nonefloor renders a bareDELETE 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
FROMis a derived relation (a subquery or a CTE) with no fixed base-column list; - the
FROMnames 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, orJOIN … 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.