pgsm postgres_schema_management v0.9.2

A guided tour for someone new to pgsm: what problem it solves, the one idea you need to hold in your head, the day-to-day loop, and the two things that make it more than a diff tool — the change-script macro engine and the build-and-test path. For the exhaustive flag-by-flag reference, see the CLI reference; this guide is the "why" and the "how it fits together." For whether pgsm is the right tool at all (versus Flyway, Liquibase, Rails, Atlas, …), read why-pgsm first.

The problem it solves

Most schema tooling tracks the steps, not the destination. With a migration framework you author an ordered pile of ALTER TABLE … deltas; the schema is "whatever running migrations 1..N produced," and nothing ever checks that the database actually equals what you meant. Two branches both grab migration 20240601 and you're renumbering by hand. A teammate hot-fixes production at 2am and the migration history has no idea it drifted.

pgsm inverts this. You keep a repository of object definitions — one file per table, view, function, type — that is the schema you want, reviewed like any other code. pgsm reads the live database, computes the difference, generates dependency-ordered DDL to close it, applies that in a transaction, and then re-diffs to prove the database now matches. The same diff, run on a schedule, is your drift detector.

Crucially, the diff never destroys data on its own. A DROP TABLE, a DROP COLUMN, a type-narrowing — anything that loses data — is refused at plan time and routed to an explicit, reviewed, run-once change script. You can't accidentally delete a table by deleting a file.

The one idea: desired state minus actual state, proven to zero

Hold two trees in your head:

• The desired state — your schema/ repository. One file per object, hand-authored (or bootstrapped once from an existing database, then maintained by hand). This is the authoritative source.
• The actual state — a live snapshot pgsm reads internally on every run, by exporting the database with pgstd and formatting it with pgsf so it compares byte-for-byte against your repo.

Every operation is a function of those two:

That last line is the whole philosophy: an apply isn't "done" because the SQL ran — it's done because re-diffing finds nothing left to do. verify is diff with a non-zero exit when the report isn't empty; it's your CI gate.

Getting started

pgsm is a client-side CLI that connects to an existing database (it doesn't create one — except test, which owns a throwaway). It talks to PostgreSQL through an in-process driver; there's no psql dependency. It shells out to two sibling tools only to read the live database's current state:

Both must be on PATH (test also needs the pgt runner there). Build pgsm and the siblings each with just build, then:

just install            # copies target/release/pgsm to /usr/local/bin (sudo if needed)
just install ~/.local/bin   # …or a PATH dir you own

You need a schema repository to point it at. To create one — author it by hand for a greenfield database, or bootstrap it from an existing one — see adoption. pgsm_demo is a complete public example of the layout.

Connecting. Every command takes the standard connection flags, which mirror psql and fall through to the PG* environment variables when omitted:

pgsm diff -d mydb -U postgres --host /tmp -p 5432 path/to/schema
pgsm diff path/to/schema     # …or rely on PGDATABASE / PGUSER / PGHOST / PGPORT

-h is host (like psql), so help is --help, not -h. TLS is honored via --sslmode (default prefer) and the --ssl* cert paths; see connection.md.

The repository layout

You point a command at the schema tree (the first positional argument); everything else resolves by convention as siblings of it:

your-repo/
  schema/            # the desired state — one file per object (the positional arg)
  changes/           # change scripts (the macro language)              (--changes)
  seeds/             # reference-data CSVs, seeds/<schema>/<table>.csv   (--seeds)
  config/
    exclusions.toml  # runtime-managed objects to skip on both sides    (--exclusions)
  tests/             # test suites, fabricators, fixtures (pgsm test discovers these)

Only schema/ is required for diff/verify/plan. apply also reads changes/ and seeds/; seed reads seeds/; test discovers everything under tests/ and config/. The full contract is the repository-layout reference.

Always pass the same --exclusions the repo tree was exported with. It's forwarded to pgstd when acquiring the actual state; if the two sides exclude different things, runtime-managed objects (cycling-partition children, etc.) appear on only one side and the diff reports spurious differences.

The everyday loop

Start read-only. diff never touches the database — it prints what would change to converge the database to your repository:

pgsm diff -d mydb -U postgres --exclusions config/exclusions.toml schema

Each line reads <kind> <severity> <object> — kind is create/drop/alter, severity is additive/destructive. An empty report means the database already matches.

Review the plan — the exact dependency-ordered DDL pgsm would run:

pgsm plan -d mydb -U postgres --exclusions config/exclusions.toml schema

This is the artifact you read before applying. If a drop would orphan a dependent (pgsm never emits CASCADE) or a change would destroy data without a covering change script, plan fails here rather than emitting unsafe SQL.

Apply — the full convergence loop in one command:

pgsm apply -d mydb -U postgres --exclusions config/exclusions.toml schema

It auto-creates pgsm's bookkeeping if absent, runs a pre-apply drift check, runs pending change scripts, executes the plan in a single transaction, then re-diffs to prove convergence and records a verified deployment. If the post-apply verify isn't empty, apply exits non-zero and the deployment is left recorded-but-unverified — an honest "did not finish" that never advances the version. A failed pure-DDL apply changes nothing, because of the transaction.

Verify — the same comparison as diff, but it exits non-zero unless the report is empty. This is the CI gate, and the same diff run nightly against production is your drift alarm:

pgsm verify -d mydb -U postgres --exclusions config/exclusions.toml schema

Check the recorded version — the latest verified deployment (schema fingerprint, the commit if applied from a clean git tree, and when):

pgsm version -d mydb -U postgres

pgsm version (the subcommand) reports the database's version. pgsm --version / -V is a separate root-level flag that prints the binary's own version and connects to nothing. Don't conflate them.

Drift safety: the pre-apply check

Before any change script or the plan runs, apply acquires the live state and compares its schema fingerprint against the recorded version's. If they differ — the database changed out-of-band since pgsm last recorded it — apply aborts, records nothing, touches nothing, and points you at pgsm diff to inspect the drift.

pgsm diff -d mydb -U postgres --exclusions config/exclusions.toml schema   # inspect it
pgsm apply --force ...   # …then deliberately overwrite the out-of-band change

--force skips the check and converges anyway — the explicit acknowledgment that the out-of-band change is to be overwritten. A database with no recorded version yet (never applied) has nothing to drift from, so the first apply records the baseline.

The change-script lane: data work a diff can't infer

A diff describes structure, and structure is most of what changes — but not all. Backfills, renames of data-bearing objects, enum-value removal, reference-data updates, anything where DDL and data must interleave in a specific order: these live in change scripts under changes/. A change script is not SQL. It's a short sequence of one-line macro commands that patch a deployed database, leaning on your schema files for every definition.

# changes/2024-add-tier.change   (colon-separated fields, one command per line, no comments)
enum_add_value:billing.plan_tier:enterprise
column_add:billing.accounts:tier
align:billing.accounts
seed:billing.plan_features

Two macro families:

• Convergence macros (align, seed, call) lean optimistically on the authoritative files — align:<object> converges one object to its file, seed:<schema>.<table> converges a reference table to its seed CSV.
• Assertive macros (table_*, column_*, enum_*, constraint_*, trigger_*, index_*, sequence_*) each state exactly one operation and fail loudly when the deployed state isn't what you expected — deliberately no if exists / if not exists, because a mismatch is drift you must see.

The destructive operations — table_drop, column_drop, column_retype, enum_remove_values, a deleting execute_sql — live only here. The committed, reviewed, applied-once line is the acknowledgment that data may be lost. execute_sql is the escape hatch: one DML statement only, heavily discouraged (it rejects DDL verbs and multiple statements).

Key execution rules to internalize:

• Change scripts run before the global diff in every apply, then the diff converges whatever structure remains, then verify proves they agree.
• Each script runs exactly once per database, recorded by name + content checksum in pgsm's bookkeeping. apply runs every script in changes/ not already recorded, in order.
• One line = one transaction. A failed line rolls back cleanly and aborts the apply, but lines that already committed are not reversed — recovery is forward-resume, never rollback. Per-line journaling records how far a script got, so a rerun resumes from exactly the line that failed. Make each line a coherent step; a line boundary is a real commit boundary an operator can park on.

The full vocabulary, the enum_remove_values choreography, and the resume model are in the macro-language design.

Building and testing from source

apply converges an existing database. Building a schema into an empty one is a different problem — the object reference graph has unbreakable cycles (FKs, function-calling defaults, function bodies referencing tables that reference back), so no single dependency order works. pgsm solves it pg_dump-style, with --phased: a preamble (set check_function_bodies = false) then ordered phases that defer the cycle-breakers (indexes, FKs, function-calling defaults) to the end.

You rarely invoke --phased directly — pgsm test uses it automatically:

pgsm test -d mydb_test -U postgres schema            # build (or reuse) + run the suite
pgsm test -r -d mydb_test -U postgres schema         # drop and rebuild first
pgsm test --verify -d mydb_test -U postgres schema   # add an empty-diff check as a CI tail
pgsm test --json -d mydb_test -U postgres schema     # machine-readable results on stdout

test builds a fresh database from your declarative tree, seeds it, and runs its suite via the pgt runner (or pgTAP via pg_prove, selected by an optional pgsm.toml). Build progress goes to stderr, results to stdout, so --json or a pipe sees only results. Unlike apply, test owns its target database — it creates, reuses, or (-r) recreates it. The point: your schema isn't merely diffable, it's provably constructible, seedable, and green.

To load reference data into a freshly built database outside the change-script path, seed discovers every seeds/<schema>/<table>.csv, orders them by foreign-key dependencies, and loads them in one transaction:

pgsm seed -d mydb -U postgres schema

Taking over an existing database

The first time you point apply at a database a prior framework built, the scripts under changes/ describe history already reflected in the live schema. Without intervention the first apply would replay every one of them. --adopt makes that first apply safe: it records every existing change script as already-applied without running it, so the takeover is a no-op for that history, while a genuinely-new script added later still runs:

pgsm apply --adopt -d mydb -U postgres --exclusions config/exclusions.toml schema

It's a one-time gate — --adopt only bites on the first apply, and is a no-op (with a notice) once the database is managed. The full takeover walkthrough is the adoption guide.

Bookkeeping: zero-setup, self-contained

pgsm records what each deployment converged to — an append-only deployments log, a deployment_verifications row (whose presence means the post-apply verify passed), the commit SHA on a clean tree, the applied_changes set, and a transient per-line resume journal. This is pgsm's own infrastructure, not part of your schema. It lives in a dedicated pgsm schema that apply creates automatically (from definitions embedded in the binary) and that is excluded from every diff.

So there is no init step, nothing to vendor, and a comparison never reports the pgsm schema. The database reports its own version. The schema is named pgsm specifically to avoid colliding with one your database may already own. Full design: bookkeeping.

Where to go next

• guide/why-pgsm.md — the honest comparison against migration frameworks and other diff tools, and where pgsm isn't the fit.
• guide/getting-started.md — install, connect, and run your first diff / apply / test, step by step.
• guide/adoption.md — lay out a schema repository and bring an existing database under management (the takeover).
• guide/troubleshooting.md — what the common refusals and failures mean, and how to resolve them.
• reference/cli.md — every subcommand, argument, and flag.
• reference/repository-layout.md — the database-repo contract, command by command.
• design/ — how the engine works inside: declarative-model, macro-language, bookkeeping, seeds, test-runner, phased-apply, and the rest, each authored from the code.