dump command extracts a PostgreSQL database schema for a specific schema and outputs it in a developer-friendly format. The dumped schema serves as a baseline that developers can modify and apply to target databases using the plan and apply commands.
Overview
The dump command provides comprehensive schema extraction with:- Single schema targeting (defaults to ‘public’)
- Dependency-aware object ordering
- Cross-schema reference handling with smart qualification (or forced full qualification via
--qualify-schema) - Developer-friendly SQL output format
- Single-file and multi-file organization options
Basic Usage
Integration with Plan/Apply
Connection Options
Database password (optional, can also use PGPASSWORD env var or .pgpass file)You can provide the password in multiple ways (in order of precedence):Password Resolution Order:
- Command line
--passwordflag (highest priority) PGPASSWORDenvironment variable.pgpassfile in user’s home directory- PostgreSQL will prompt for password if none found
SSL mode for database connection (env: PGSSLMODE)Valid values:
disable, allow, prefer, require, verify-ca, verify-fullFor verify-ca and verify-full modes, you can configure certificate paths using standard PostgreSQL environment variables (PGSSLROOTCERT, PGSSLCERT, PGSSLKEY).Output Options
Output schema to multiple files organized by object type. See Multi-file Schema Management Workflow.When enabled, creates a structured directory with:
- Main file with header and include statements
- Separate directories for different object types (tables, views, functions, etc.)
- Each database object in its own file
--file to specify the main output file path.Output file path (required when —multi-file is used)For single-file mode, this is optional (defaults to stdout).
For multi-file mode, this specifies the main file path.
Do not output object comment headers (e.g.,
-- Name: users; Type: TABLE; Schema: -; Owner: -).The dump header with pgschema version information is retained. This option is useful when you need pure DDL output without per-object commentary.Force full schema qualification of object identifiers in the dump, even for objects in the dumped schema.By default,
pgschema uses smart qualification and omits the schema prefix for objects in the dumped schema. With this flag, object names and references are schema-qualified (schema.object) — useful when the dump must resolve unambiguously regardless of search_path, or to simplify static analysis. A couple of positions are necessarily excepted (CREATE INDEX names, and same-schema type references) — see Forced Qualification below.Ignoring Objects
You can exclude specific database objects from dumps using a.pgschemaignore file. See Ignore (.pgschemaignore) for complete documentation.
Examples
Schema Dump
Multi-File Output
schema.sql file contains:
pgschema writes postponed constraints to separate
files under constraints/, such as constraints/0001_orders.sql. The numeric
prefix distinguishes multiple constraints on the same table. The exporter keeps
these files in dependency order in schema.sql, after the required tables.
Load files in the generated include order to preserve these dependencies.
For partitioned indexes, pgschema creates the tables and leaf indexes first,
then each parent index with ON ONLY, then attaches the children with
ALTER INDEX ... ATTACH PARTITION. This keeps custom child index names and
leaves unattached indexes unattached. Multi-file dumps write parent indexes and
their attachments under indexes/, such as indexes/0001_events_lookup.sql,
after the table includes.
A PRIMARY KEY or UNIQUE constraint on a partitioned table with partitions is
not written inline. PARTITION OF would copy it onto each partition under a
generated name, which can take the name of a standalone index created later.
Instead, pgschema adds the constraint after the tables with
ALTER TABLE ONLY ... ADD CONSTRAINT, adds each partition’s copy under its
original name the same way, and attaches the copies with
ALTER INDEX ... ATTACH PARTITION. Multi-file dumps write each constraint and
its copies under constraints/, such as constraints/0001_events.sql.
A row-level security policy that must wait for other objects, because it refers
to another new table or function or because its table’s keys are added after
the tables, is created after them. Multi-file dumps keep such a policy at that
position under policies/, such as policies/0001_events.sql, rather than in
its table’s file.
Each individual file (e.g., tables/users.sql) contains the specific object definition:
Schema Qualification
pgschema uses smart schema qualification to make dumps portable:
- Objects within the dumped schema: No schema qualifier added
- Objects from other schemas: Fully qualified with schema name
Forced Qualification
Pass--qualify-schema to force schema qualification on object names and references, even for objects in the dumped schema:
CREATE INDEX names unqualified (an index lives in its table’s schema), so only the target table is qualified.
This is useful when:
- Unambiguous resolution — a fully qualified
schema.objectresolves the same way regardless of the sessionsearch_path, avoiding the ambiguity described in #320. (Reserved-word names are handled separately by quoting, whichpgschemaalways applies where needed, e.g."user".) - Static analysis — keeping complete qualifiers means tools don’t have to infer schema context from directory layout (for example to distinguish
app.userfromapi.user).
ON, REFERENCES, OWNED BY, and COMMENT targets. Object comment headers also keep the schema name instead of collapsing it to -.
--qualify-schema does not currently qualify type references to the dumped schema — column types, function/procedure parameter and return types, domain base types, and similar. PostgreSQL reports these without a schema for types in the current schema, and pgschema stores them as-is, so they remain unqualified. Cross-schema type references are still fully qualified. Tracking: #493.Forced qualification applies to structural identifiers only — it does not rewrite free-form SQL text such as view definitions, policy
USING / WITH CHECK expressions, or function and procedure bodies. Identifiers inside those bodies are emitted exactly as PostgreSQL reports them, so a reference like example.user inside a function body is not added or changed by this flag.
