Dump

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:

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

# 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

string

default:"localhost"

Database server host (env: PGHOST)

integer

default:"5432"

Database server port (env: PGPORT)

string

required

Database name (required) (env: PGDATABASE)

string

required

Database user name (required) (env: PGUSER)

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

PGPASSWORD=mypassword pgschema dump \
  --host localhost \
  --db myapp \
  --user postgres

pgschema dump \
  --host localhost \
  --db myapp \
  --user postgres \
  --password mypassword

# Create ~/.pgpass file with:
# hostname:port:database:username:password
# localhost:5432:myapp:postgres:mypassword

# Make sure file has correct permissions
chmod 600 ~/.pgpass

pgschema dump \
  --host localhost \
  --db myapp \
  --user postgres

Password Resolution Order:

Command line --password flag (highest priority)
PGPASSWORD environment variable
.pgpass file in user’s home directory
PostgreSQL will prompt for password if none found

See dotenv (.env) for detailed configuration options.

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).

string

default:"public"

Schema name to dump

Output Options

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.

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.

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.

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.

Getting started

CLI Reference

Workflow

Configuration

Overview

Basic Usage

Integration with Plan/Apply

Connection Options

Output Options

Ignoring Objects

Examples

Schema Dump

Multi-File Output

Schema Qualification

Forced Qualification

​Overview

​Basic Usage

​Integration with Plan/Apply

​Connection Options

​Output Options

​Ignoring Objects

​Examples

​Schema Dump

​Multi-File Output

​Schema Qualification

​Forced Qualification

Overview

Basic Usage

Integration with Plan/Apply

Connection Options

Output Options

Ignoring Objects

Examples

Schema Dump

Multi-File Output

Schema Qualification

Forced Qualification