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
​
--port
integer
default:"5432"
Database server port
​
--db
string
required
Database name
​
--user
string
required
Database user name
​
--password
string
Database password (can also use PGPASSWORD environment variable)You can provide the password in two ways:
PGPASSWORD=mypassword pgschema dump \
  --host localhost \
  --db myapp \
  --user postgres
​
--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.

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