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

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