Skip to main content
The 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:
  1. Single schema targeting (defaults to ‘public’)
  2. Dependency-aware object ordering
  3. Cross-schema reference handling with smart qualification (or forced full qualification via --qualify-schema)
  4. Developer-friendly SQL output format
  5. Single-file and multi-file organization options

​
Basic Usage

# Dump default schema (public)
pgschema dump --host localhost --db myapp --user postgres --password mypassword

# Dump specific schema
pgschema dump --host localhost --db myapp --user postgres --password mypassword --schema myschema

# Save to file
pgschema dump --host localhost --db myapp --user postgres --password mypassword > schema.sql

# Multi-file organized output
pgschema dump --host localhost --db myapp --user postgres --password mypassword --multi-file --file schema.sql

​
Integration with Plan/Apply

# 1. Dump current production schema
pgschema dump --host prod-host --db myapp --user postgres --schema public > current.sql

# 2. Make modifications to current.sql (now desired state)

# 3. Plan changes against staging
pgschema plan --host staging-host --db myapp --user postgres --file current.sql

# 4. Apply changes if plan looks good
pgschema apply --host staging-host --db myapp --user postgres --file current.sql

​
Connection Options

​
--host
string
default:"localhost"
Database server host (env: PGHOST)
​
--port
integer
default:"5432"
Database server port (env: PGPORT)
​
--db
string
required
Database name (required) (env: PGDATABASE)
​
--user
string
required
Database user name (required) (env: PGUSER)
​
--password
string
Database password (optional, can also use PGPASSWORD env var or .pgpass file)You can provide the password in multiple ways (in order of precedence):
# Create .env file with:
# PGHOST=localhost
# PGPORT=5432
# PGDATABASE=myapp
# PGUSER=postgres
# PGPASSWORD=mypassword

pgschema dump
Password Resolution Order:
  1. Command line --password flag (highest priority)
  2. PGPASSWORD environment variable
  3. .pgpass file in user’s home directory
  4. PostgreSQL will prompt for password if none found
See dotenv (.env) for detailed configuration options.
​
--sslmode
string
default:"prefer"
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).
​
--schema
string
default:"public"
Schema name to dump

​
Output Options

​
--multi-file
boolean
default:"false"
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
Requires --file to specify the main output file path.
​
--file
string
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.
​
--no-comments
boolean
default:"false"
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.
​
--qualify-schema
boolean
default:"false"
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

# Dump default schema (public)
pgschema dump --host localhost --db myapp --user postgres

# Dump specific schema
pgschema dump --host localhost --db myapp --user postgres --schema analytics
Example output: 
--
-- pgschema database dump
--

-- Dumped from database version PostgreSQL 17.5
-- Dumped by pgschema version 1.0.0


--
-- Name: users; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--
-- Name: idx_users_email; Type: INDEX; Schema: -; Owner: -
--

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

​
Multi-File Output

pgschema dump \
  --host localhost \
  --db myapp \
  --user postgres \
  --multi-file \
  --file schema.sql
This creates a structured directory layout: 
schema.sql              # Main file with header and includes
├── tables/
│   ├── users.sql
│   ├── orders.sql
│   └── products.sql
├── views/
│   └── user_stats.sql
├── functions/
│   └── update_timestamp.sql
└── procedures/
    └── cleanup_old_data.sql
The main schema.sql file contains: 
--
-- pgschema database dump
--

-- Dumped from database version PostgreSQL 17.5
-- Dumped by pgschema version 1.0.0


\i tables/users.sql
\i tables/orders.sql
\i tables/products.sql
\i views/user_stats.sql
\i functions/update_timestamp.sql
\i procedures/cleanup_old_data.sql
Each individual file (e.g., tables/users.sql) contains the specific object definition: 
--
-- Name: users; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--
-- Name: idx_users_email; Type: INDEX; Schema: -; Owner: -
--

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

--
-- Name: users; Type: RLS; Schema: -; Owner: -
--

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

​
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
This approach makes the dump suitable as a baseline that can be applied to different schemas, particularly useful for multi-tenant applications. 
# Dump the 'public' schema
pgschema dump --host localhost --db myapp --user postgres --schema public
Output for objects within ‘public’ schema (no qualification): 
-- Objects in the dumped schema have no qualifier
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),  -- Same schema, no qualifier
    product_id INTEGER REFERENCES catalog.products(id)  -- Different schema, qualified
);
This qualification strategy enables using one schema as a template for multiple tenants: 
# 1. Dump the template schema
pgschema dump --host localhost --db myapp --user postgres --schema template > template.sql

# 2. Apply to different tenant schemas
pgschema apply --host localhost --db myapp --user postgres --schema tenant1 --file template.sql
pgschema apply --host localhost --db myapp --user postgres --schema tenant2 --file template.sql
pgschema apply --host localhost --db myapp --user postgres --schema tenant3 --file template.sql
Because objects within the schema are not qualified, they will be created in whichever schema you specify during the apply command.

​
Forced Qualification

Pass --qualify-schema to force schema qualification on object names and references, even for objects in the dumped schema: 
pgschema dump --host localhost --db myapp --user postgres --schema public --qualify-schema
Object names and references keep their schema prefix: 
-- Objects in the dumped schema are now fully qualified
CREATE TABLE public.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE public.orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES public.users(id),         -- Same schema, now qualified
    product_id INTEGER REFERENCES catalog.products(id)   -- Different schema, still qualified
);

CREATE INDEX idx_orders_user_id ON public.orders(user_id);
PostgreSQL keeps 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.object resolves the same way regardless of the session search_path, avoiding the ambiguity described in #320. (Reserved-word names are handled separately by quoting, which pgschema always 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.user from api.user).
Forced qualification covers object names and structural references where PostgreSQL syntax permits — table, view, sequence, type, function, procedure, and policy names; index targets and comments; and 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.