pgstd Postgres Schema-to-Directory v0.11.2

A guided tour for someone new to pgstd (postgres_schema_to_directory): what problem it solves, the one idea to hold in your head, the day-to-day loop, and how it sits between a schema and the two sibling tools that consume and beautify its output. For the exhaustive flag-by-flag reference, see the README; this guide is the "why" and the "how it fits together."

The problem it solves

A database's schema is the most important thing it owns, and the hardest to see. It lives as catalog rows inside a running server — not as files you can read, diff, or put in a pull request. pg_dump gives you a file, but it's one monolithic script: every object in dependency order, in pg_dump's own style, with no stable home for any single object. Change one function and the dump's diff is a churn of moved lines; you can't point at "the accounts.charge function" as a thing that lives somewhere and has a history.

What you actually want for version-controlling a schema is the opposite: one small file per object, in a predictable place, named after the object, the same on every extraction — so a change to one trigger touches one file, code review reads like code review, and git log tells you the history of each object individually.

pgstd turns a PostgreSQL schema into exactly that tree — a clean, diffable directory of one-file-per-object plain SQL, grouped by schema and object kind, deterministic run to run. It is the "capture the schema into the repo" step; what you do with that repo afterward (diff it, format it, apply it) is the job of the sibling tools below.

It reads the schema from either of two places, and the tree is byte-identical whichever you use:

• a live database, the usual case; or
• a pg_dump file (--from-dump), with no server in the path — so the same monolithic script you couldn't review becomes the tree you can. That is what lets you capture a schema from a backup, from a dump a colleague sent, from a database you aren't permitted to connect to, or in CI with nothing to provision.

The one idea: a deterministic one-file-per-object tree

Everything pgstd does serves a single output contract. Under the output directory, one directory per schema, and inside it one subdirectory per object kind, and inside that one .sql file per object:

<output_dir>/
  globals/                       # cluster-global roles, memberships, defaults (see below)
  <schema>/
    schema.sql                   # the schema's own create + owner + comment + grants
    tables/<table>.sql           # create table + comments + ownership + privileges + RLS enable
    indexes/<table>.sql          # all non-constraint indexes on that table
    foreign_keys/<table>.sql     # all FKs from that table
    triggers/<table>.sql         # all triggers on that table
    functions/<name>.sql         # all overloads of that name, one file
    procedures/  types/  domains/  sequences/
    views/  materialized_views/
    aggregates/  operators/  casts/  collations/
    policies/  rules/  statistics/
    text_search_dicts/  text_search_configs/

Three properties make this tree trustworthy, and they're the reason to reach for pgstd over a raw dump:

1. Filename equals object name — lowercase, exactly as the catalog has it, no prefixes, no sequence numbers. Application order is the consumer's concern (its dependency sort), not the filesystem's. Index, FK, and trigger files are named after the table they attach to, grouping a table's objects of that kind into one file.
2. Deterministic and consistent. The whole extraction runs inside one read-only REPEATABLE READ snapshot, so concurrent DDL during the run is invisible — the tree is always one coherent point in time, the same guarantee pg_dump gives. Overloads are byte-ordered, output is sorted; the same database yields the same bytes on every machine.
3. Plain SQL, not pretty SQL. pgstd emits valid, complete, faithful DDL — the only normalization it applies is stripping OR REPLACE and terminating each statement. It does not define a style. House-formatting is a separate pass (see How it composes below). This separation is deliberate: extraction and formatting are independent concerns, and keeping them apart means either can change without disturbing the other.

The tree is pgstd-owned. A consuming repo may add sibling directories around it (change scripts, seed data, tests, tool config); pgstd neither produces nor reads those. But the object tree itself is its territory — which matters for --prune, below.

Getting started

Build and install the binary (build compiles, install only copies — so you compile unprivileged and sudo only the copy):

just build                     # compile target/release/pgstd
just install ~/.local/bin      # …into a PATH dir you own — no sudo, no rebuild

Then point it at a database and an output directory. Connection options follow psql/libpq conventions — the same flags, the same PG* environment variables:

# With the standard libpq env vars (PGDATABASE, PGUSER, PGHOST, …) already set:
pgstd /path/to/output

# Or spell the connection out explicitly:
pgstd -d mydb -U owner_mydb /path/to/output

Or point it at a pg_dump file instead, which needs no connection options and no PG* environment at all — - reads the dump from standard input:

pgstd --from-dump backup.sql /path/to/output
pg_dump -s mydb | pgstd --from-dump - /path/to/output

The password is never taken on the command line (that would leak it via ps and shell history) — it comes from PGPASSWORD or ~/.pgpass (the file named by PGPASSFILE, wildcards and 0600 check included), exactly like psql. Force an interactive echo-off prompt with -W; disable prompting entirely with -w. Note pgstd follows psql's quirk: there is no -h for help (-h is --host); use --help.

pgstd targets PostgreSQL 16 and newer. Older servers get a stderr warning, not a hard stop — a schema that uses none of the version-gated catalog columns extracts fine, and the warning just turns a possible deep failure into an up-front heads-up.

The everyday loop

Capture a whole database — every schema the connecting role can see, minus the always-excluded system schemas (pg_catalog, information_schema, pg_*, and pgsm):

pgstd -d mydb -U owner_mydb /path/to/output

Narrow what you capture. --schema is an allow-list (repeatable); the exclude flags then remove from whatever remains. They compose as include narrows, exclude removes:

pgstd --schema accounts --schema billing /path/to/output   # only these two schemas
pgstd -x "temp_schema,test_schema" /path/to/output          # exclude extra schemas

Speed up a large schema with a small connection pool. Extraction is ~22 independent passes (one per object kind); --jobs N spreads them across an N-connection pool that all share one exported snapshot, so the result is still a single coherent point in time:

pgstd -j 4 /path/to/output      # 4 connections, one shared snapshot
pgstd -j 0 /path/to/output      # auto: one connection per CPU

--jobs 1 (the default) is serial and byte-for-byte identical to having no pool at all — every existing invocation is unchanged.

The pool buys wall-clock time in proportion to the network latency, not the schema size: each pass fetches its whole object kind in a few bulk queries, so a big schema costs barely more round-trips than a small one. Over a local socket -j gains little. Over a 10 ms link a 2400-object extraction measures 2.3 s at -j 1 and 0.98 s at -j 4, with no further gain past -j 4.

Re-extract idempotently. A plain run is create-or-overwrite: it never deletes a file, so an object dropped from the database leaves its .sql behind as a ghost, and the tree drifts. Two ways to keep it honest — wipe first, or --prune:

pgstd --prune /path/to/output   # after extracting, delete the .sql files of objects that no longer exist

--prune is carefully bounded: it only ever touches .sql files inside the output directory, never ascending out of it or touching seed data, config, or a README. (Because the tree is pgstd-owned, a hand-authored .sql placed inside it counts as stale and gets pruned — keep consumer SQL in a sibling directory.)

Stay fail-fast. By default the first failing pass aborts the run with nothing partial written. That strictness is deliberate: the tree feeds a diff engine (pgsm, below), and a silently missing object reads as "drop this object" — so a half-written tree is more dangerous than no tree. Only reach for --continue-on-error to salvage an export from a genuinely broken object; it writes what it can, names every failure, and exits non-zero so CI still fails.

What lands in the tree (and what never does)

Each object's file is self-contained: the CREATE, then its ownership (ALTER … OWNER TO), then its non-default privileges (GRANT/REVOKE), then its COMMENT ON — the pg_dump ordering, so the file applies standalone. Comments and grants live in the same file as the object, so a comment change touches one file and stays local in review. Suppress the extras when you don't want them:

Owners and grantees are emitted as quoted identifiers and may name a role the tree itself doesn't define — an externally-provisioned identity the apply environment supplies.

Cluster globals. By default the extract also folds in a root globals/ directory — the cluster-wide permission structure relevant to this database. The cluster may host databases you aren't managing, so globals are never blind-dumped: only group roles (NOLOGIN) that own or are granted on a managed object, closed under group→group membership. Login roles are operational/external and excluded; a password is never emitted (read from pg_roles, never pg_authid). It ships globals/roles/<role>.sql, role_memberships.sql, default_privileges.sql, and settings.sql. The same engine is a standalone subcommand — pgstd … globals OUTPUT_DIR — the pg_dumpall --globals-only parallel, writing only the globals tree.

pg_dump describes a single database and carries no roles, memberships, or per-role settings, so a dump-sourced run needs a second file for them — --from-globals-dump <file>, from pg_dumpall --globals-only:

pg_dump -s mydb > schema.sql && pg_dumpall --globals-only > globals.sql
pgstd -d mydb --from-dump schema.sql --from-globals-dump globals.sql /path/to/output

It is optional. Without it the run writes no globals/ and says so — which is what a repository vendoring only a schema dump wants, since many have no concept of cluster globals at all.

What the tree never contains, because these aren't declared schema:

• Extension-owned objects — everything CREATE EXTENSION brings with it (pg_depend.deptype='e'); the root extensions.sql manifest records the extensions themselves instead. --filter-extension-schemas additionally drops a dedicated extension schema (one holding nothing but extension objects), treating it as provisioning-owned infrastructure. (public is never filtered.)
• A range type's auto-generated dependents — the multirange type, constructor functions, and range→multirange cast that create type … as range materializes; re-running their DDL would error.
• The contents of a shell-only schema — list a schema under shell_only_schemas in the --exclusions config and pgstd emits its shell (create schema, owner, comment, grants) but skips every object inside, because those belong to a separate application's migrations (flyway/goose).
• Runtime-managed objects named in the --exclusions config (e.g. cycling-partition children) — declared in TOML, applied uniformly so both sides of a later diff stay symmetric.

How it composes: pgstd → pgsf → pgsm

pgstd is one tool in a three-tool pipeline, and its plain-SQL-only design is what lets the other two stay independent of it:

live database  ─┐
                ├─pgstd──▶  plain-SQL tree  ──pgsf──▶  house-formatted tree  ──pgsm──▶  diff / plan / apply
pg_dump file   ─┘

• pgsf (postgres_sql_formatter) pretty-prints the tree to the team style guide, in place, as a separate post-hoc pass: pgsf -d <output_dir>. pgstd has no build or runtime dependency on the formatter — any formatter could be applied, and a formatter change never touches pgstd's lockfile or CI. The bundled just regen <out> [pgstd args…] recipe runs both steps for you (an rm -rf then pgstd then pgsf), which is the idiomatic "regenerate the canonical tree" command.
• pgsm (postgres_schema_management) consumes the tree: it diffs this declarative repo against a live database to plan and apply convergence. This is why pgstd's fail-fast default matters so much — pgsm reads a missing object as a deletion, so pgstd refuses to ever hand it a partial tree. When the canonical tree is regenerated with --filter-extension-schemas, pgsm is told to acquire actual state with the same flag, so both sides of the diff stay symmetric.

The flow is one direction: pgstd produces the tree, pgsf beautifies it in place, pgsm consumes it. Keep that order in your head and the toolchain reads cleanly.

Where to go next

• The README is the full reference — every flag, every connection and SSL option with its PG* environment variable and default, and the complete list of what each object kind captures.
• design/repository-layout.md is the output contract: the tree structure, the naming and percent-encoding rules, COMMENT ON/ownership/privilege placement, and exactly what's excluded and why.
• design/dump-input.md covers reading a pg_dump file: why the tree it produces is byte-identical to a live extraction's, what the reader has to reconstruct to keep it that way, and the one thing a dump does not describe (what a fresh database already has — plpgsql and the public schema).
• technical.md is the per-object-kind implementation reference — the catalog source, output location, and DDL-generation details for all ~22 passes, plus the snapshot, parallel-extraction, and version-floor internals.