pg_dump --schema-only parity
snapshot emits DDL shaped like pg_dump --schema-only. This page audits
that claim option by option: for each pg_dump option that affects a
schema-only dump, what snapshot does, whether the name is discoverable
from pg_dump, and whether import accepts the option’s effect on output.
Options are from the PostgreSQL 18 pg_dump reference:
https://www.postgresql.org/docs/18/app-pgdump.html.
Status legend
- covered: retrofit has an explicit flag or a fixed behavior
matching
pg_dump’s contract. - implicit: retrofit never emits the relevant DDL, so the
pg_dumpflag’s “do not emit X” semantics hold trivially. - gap:
pg_dumphas it andsnapshothas no counterpart. - out-of-scope: the option does not fit an oplog-in, one-stream-out emitter.
Snapshot side (retrofit snapshot vs pg_dump --schema-only)
pg_dump | snapshot today | status | round-trip via import |
|---|---|---|---|
-s, --schema-only | --ddl-only | covered | n/a (input shape) |
-c, --clean | --include-drop=scoped|schema | covered (no alias) | DROP statements skipped (gap below) |
--if-exists | folded into --include-drop=scoped|schema | covered (contract) | n/a (DROPs skipped) |
-C, --create | none (schema-scoped, never CREATE DATABASE) | out-of-scope | n/a |
-O, --no-owner | implicit (no owner concept in vocab) | implicit | n/a (OWNER TO is skipped) |
-x, --no-privileges / --no-acl | implicit (no GRANT/REVOKE) | implicit | n/a (GRANT/REVOKE is skipped) |
--no-comments | --no-comments suppresses COMMENT ON | covered | tolerant (in vocab) |
--no-tablespaces | implicit (no TABLESPACE …) | implicit | n/a |
--no-table-access-method | implicit (no USING <method> on CREATE TABLE) | implicit | n/a |
--quote-all-identifiers | always quotes every identifier | covered (contract) | tolerant (parser is quote-agnostic) |
-n, --schema=<pattern> | --schema <registered-name> (single, not pattern) | covered (different shape) | n/a |
-N, --exclude-schema | --exclude-schema=<name> (repeatable, exact-match) | covered (exact-match) | n/a |
-t, --table / -T, --exclude-table | --table=<name> / --exclude-table=<name> (repeatable, exact-match) | covered (exact-match) | tolerant (output is a strict subset) |
--section=pre-data|post-data | none (single-stream emit) | gap | tolerant either way |
--no-statistics | implicit (no ANALYZE or stats emission) | implicit | n/a |
-f, --file=<path> | none (stdout only) | out-of-scope (pipe-only) | n/a |
--no-policies | none (CREATE POLICY is emitted; no suppression flag) | gap | tolerant (in vocab) |
--no-publications | implicit (no CREATE PUBLICATION) | implicit | n/a |
--no-subscriptions | implicit (no CREATE SUBSCRIPTION) | implicit | n/a |
--no-security-labels | implicit (no SECURITY LABEL) | implicit | n/a |
--use-set-session-authorization | implicit (no owner concept) | implicit | n/a |
-e, --extension / --exclude-extension | none (CREATE EXTENSION is emitted; no include/exclude filter) | gap | extensions in vocab on import |
--strict-names | n/a (single-name lookup, no pattern matching) | out-of-scope | n/a |
--filter=<file>, --table-and-children, --exclude-table-and-children | none (filtering machinery) | out-of-scope | n/a |
-F, --format, -Z, --compress, --no-sync, --sync-method | plain stream only | out-of-scope | n/a |
--binary-upgrade, --snapshot, --serializable-deferrable, --lock-wait-timeout | live-DB-only knobs | out-of-scope (retrofit emits from the oplog, not a live DB) | n/a |
Notes on individual rows
-c, --clean (no alias). The --include-drop=none|scoped|schema flag
encodes a blast-radius choice that pg_dump’s single --clean switch
does not. Aliasing --clean to one of the three modes (most naturally
scoped) would let a user paste muscle memory from pg_dump and silently
pick the wrong radius. --include-drop=scoped is the nearest match for
pg_dump --clean --if-exists; there is no --clean alias.
--if-exists. Always-on under scoped/schema. The “drop without
IF EXISTS” combination has no useful application (a fresh-DB apply
should use --include-drop=none; a reload-into-existing should be
idempotent). Not exposed as a separate flag.
-C, --create. pg_dump --create adds CREATE DATABASE; retrofit
operates one schema at a time and never emits database-level DDL. Schema
emission itself is unconditional per schema in the cut, with
public/pg_catalog/information_schema skipped to avoid a
“schema already exists” failure. Under
--include-drop=scoped|schema, schema creation uses
CREATE SCHEMA IF NOT EXISTS; bare CREATE SCHEMA under none.
--no-comments. snapshot emits COMMENT ON TABLE / COMMENT ON COLUMN / COMMENT ON CONSTRAINT / COMMENT ON FUNCTION when the
vertex carries a comment; snapshot --no-comments suppresses all four,
mirroring pg_dump --no-comments.
--quote-all-identifiers. The emitter always quotes every
identifier in ASCII double quotes, equivalent to
pg_dump --quote-all-identifiers always-on.
Quoting every identifier avoids reserved-word collisions across
PostgreSQL versions. It is fixed behavior, with no flag.
-n, --schema. Here --schema is a per-command flag
naming a single registered schema (it is not root-persistent: the
root command rejects it). The registry lookup is
exact-match, not glob. Pattern-matching and
multi-schema emit in one call would require merging cuts across oplogs
and stable cross-oplog opid resolution, and neither is available.
-N, --exclude-schema / -t, --table / -T, --exclude-table.
All three are exact-match against the cut’s
registered/canonical identifier; pg_dump’s --strict-names is the
default. Each flag is repeatable. snapshot refuses a name that does
not resolve with schema-not-in-cut or table-not-in-cut (silent
no-op would hide a typo). With both --table and --exclude-table,
exclude wins on conflict (matches pg_dump). FK reachability is
checked against the filtered set: snapshot refuses an FK from an
included table to an excluded one with fk-cross-schema-unreachable.
--include-drop=schema is incompatible with
--table (flag-conflict), because schema-level DROP CASCADE would blast
siblings the --table filter spares; use --include-drop=scoped for
per-element DROPs. A cut holds one schema, so
--exclude-schema=<cut-name> empties the emission; the banner still
prints. Glob/pattern matching, --strict-names (meaningful only with
patterns), --filter=<file> and --table-and-children are not
available.
--section=pre-data|post-data (gap). pg_dump
splits its DDL into pre-data (CREATE TABLE, sequences) and post-data
(indexes, FKs, comments) so reload tooling can interleave data loads.
snapshot emits a single stream ordered CREATE → ALTER ADD CONSTRAINT →
CREATE INDEX → COMMENT. With a data band selected, text mode places
each record that states a table’s whole content (a replace section, or
a reference restate) after the CREATE TABLE statements and before the
constraints, the slot pg_dump’s data section takes between pre-data
and post-data. Every other record runs after the whole schema (A full
snapshot and a data-only
band).
-f, --file (out-of-scope, pipe-only). Shell redirection
(retrofit snapshot > schema.sql) writes the file; under --as=json the
DDL rides the {"type":"snapshot","ddl":"…"} record.
Import side (retrofit import vs pg_dump output)
import is the inverse: walk a .sql file into retrofit’s vocabulary
and mint an oplog. Out-of-vocab statements route to the stdout
skip-manifest; they do not fail the import.
pg_dump artifact | import behavior | status |
|---|---|---|
CREATE SCHEMA <name> (top-level) | silently dropped | covered |
CREATE SCHEMA IF NOT EXISTS <name> | silently dropped | covered |
DROP TABLE IF EXISTS … (from --clean --if-exists) | per-target liveness gate: mint DropTable for a tracked-live target; suppress an ordinary absent target | covered |
DROP INDEX IF EXISTS … (from --clean --if-exists) | per-target liveness gate: mint DropIndex for a tracked-live target; suppress an absent target | covered |
DROP TYPE IF EXISTS … | per-target liveness gate: mint DropType for a tracked-live target; suppress an absent target | covered |
DROP SCHEMA IF EXISTS … CASCADE | always suppressed; no DropSchema operator exists | covered |
DROP TABLE <name>; (bare, no IF EXISTS) | minted as DropTable op (hand-authored intent) | covered |
DROP INDEX <name>; (bare, no IF EXISTS) | minted as DropIndex op (hand-authored intent) | covered |
ALTER TABLE … OWNER TO … | skipped (AlterTableOwnerTo) | covered |
ALTER FUNCTION/TYPE/DOMAIN/SCHEMA … OWNER TO … | skipped (AlterOwnerStmt), same decision class as the table form | covered |
GRANT / REVOKE | skipped | covered |
TABLESPACE clause on CREATE TABLE | not modeled; parser tolerates, retrofit ignores | covered |
USING <method> clause on CREATE TABLE (table access method) | not modeled; ignored | covered |
COMMENT ON TABLE/COLUMN/CONSTRAINT/FUNCTION | in vocab, minted as SetTableComment / SetColumnComment / SetConstraintComment / SetFunctionComment ops | covered |
CREATE POLICY | in vocab, minted | covered |
SECURITY LABEL, CREATE PUBLICATION, … | skipped (out of vocab) | covered |
Quoted identifiers (pg_dump --quote-all-identifiers) | parser-agnostic; tolerated | covered |
SET … prelude, backslash psql meta-commands | silently dropped | covered |
CREATE EXTENSION | in vocab, minted | covered |
CREATE FUNCTION written ahead of the table, view or function its body binds (a string-literal LANGUAGE sql body, a %TYPE or %ROWTYPE declaration) | CreateFunction appended just after the statement creating what it binds, with the note schema-import-function-moved-after-bound-name | covered |
unresolved CREATE TRIGGER callback | import mints a synthetic CreateFunction stub; snapshot suppresses the function DDL but keeps the trigger | gap (conditional apply) |
Notes
DROP of IF EXISTS lines. A pg_dump --clean --if-exists file
starts with DROP TABLE … IF EXISTS per object (and, under --clean
without -n, a DROP SCHEMA … IF EXISTS … CASCADE and DROP INDEX … IF EXISTS for any standalone indexes). import does not classify
all of those guarded statements as noise. For TABLE, INDEX, and TYPE,
import evaluates every target independently: a target tracked-live at that
target’s source position mints the matching DropTable, DropIndex, or
DropType; an ordinary absent target is suppressed without an op or
skip-manifest entry. A mixed list can therefore mint its live targets while
suppressing its absent targets.
Against a fresh import target the prologue’s targets are absent, so the
prologue mints nothing. A bare DROP (no IF EXISTS) is never suppressed:
it mints for a known target. An unknown table or index refuses with
validation-error; an unknown type goes to the skip manifest as
drop-type-unresolved. DROP SCHEMA IF EXISTS is always suppressed: there
is no DropSchema operator. A drop of a still-active deferred-data-motion
table target produces the cross-skipped-data-motion-target disposition.
Unresolved trigger callbacks (gap, conditional apply). If the input
contains a trigger whose callback is defined by neither the input nor the
current cut, import mints a language=internal CreateFunction stub so
the trigger has an oplog identity, and reports function-synthesized-stub.
snapshot never emits the stub as function DDL but does emit the trigger,
so the SQL applies only when the target database already provides the
callback (built in, or from an installed extension); otherwise PostgreSQL
rejects the CREATE TRIGGER. A callback qualified with another schema is
resolved by its bare name: it binds to a same-named function in the
resolved schema, or gets the stub, and snapshot writes the call without
that schema. A trigger callback in another schema is not available.
Knobs specific to retrofit
These have no pg_dump analog and exist because retrofit’s input is
an oplog, not a live database:
--at <opid>: emit at a historical cut, not just head.--using=<engine>:<major>: one-shot emit-target override (read-only).--no-header: suppress the canonical banner line.--include-drop=none|scoped|schema: the drop-radius triplet documented above.--label <label>: embed the namedpurpose=seeddataset from the preserved data corpus as a seed band; text mode places it in two parts (A full snapshot and a data-only band), and--as=jsonemits it as a separatesnapshot-datarecord. snapshot embedsrefrows by default;--ddl-onlyis the option that leaves rows out (the-srow above).--labelis the closest analog topg_dump --section=data; the rows come from the data files under the schema’sdata_root, not from a database. Repeat--labelto layer datasets in argv order. See The data band.
Identity sequences in migrate
PostgreSQL’s dump/restore treats sequence values as part of the data
section. For CopyTable, SplitTable and MergeTable, the SQL migrate
emits calls pg_get_serial_sequence after each target’s row copy and
setval to align the new sequence with the copied identity values (the
column’s max, or its min for a descending sequence). pg_dump reads
each sequence’s name and value from the source database; the emitted SQL
reads the target’s rows when it runs instead, so empty targets keep their
START WITH.
UnjoinTable runs the same alignment on each child after its INSERT.
PartitionTable and DepartitionTable run it on the rebuilt table after
its row copy, before the old table is dropped; a serial column keeps its
live sequence across the rebuild, so that sequence is not set.
InlineTable and ExtractTable never move an identity column. Each
aligns a moved serial column’s new sequence instead: InlineTable on the
table it folds into, from the column’s max, before it draws values for
the rows that had no row to fold in; ExtractTable on the child, from
the source’s max or its sequence’s position past it, before the source
loses the column.
An in-place AddIdentity runs the same alignment on its table after the
ADD GENERATED … AS IDENTITY: PostgreSQL starts the new sequence at its
START WITH under rows that already hold values, and the alignment sets
it past them.
Although sequence-value changes are generally non-transactional, the
supported envelopes do not expose a sequence-only rollback residue here:
own and caller roll the freshly created target and sequence back
together, while independent has already committed the target, row copy,
and setval before any later statement fails.