> ## Documentation Index
> Fetch the complete documentation index at: https://www.pgschema.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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:

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

```bash theme={null}
# 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

```bash theme={null}
# 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

<ParamField path="--host" type="string" default="localhost">
  Database server host (env: PGHOST)
</ParamField>

<ParamField path="--port" type="integer" default="5432">
  Database server port (env: PGPORT)
</ParamField>

<ParamField path="--db" type="string" required>
  Database name (required) (env: PGDATABASE)
</ParamField>

<ParamField path="--user" type="string" required>
  Database user name (required) (env: PGUSER)
</ParamField>

<ParamField path="--password" type="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):

  <CodeGroup>
    ```bash .env File (Recommended) theme={null}
    # Create .env file with:
    # PGHOST=localhost
    # PGPORT=5432
    # PGDATABASE=myapp
    # PGUSER=postgres
    # PGPASSWORD=mypassword

    pgschema dump
    ```

    ```bash Environment Variable theme={null}
    PGPASSWORD=mypassword pgschema dump \
      --host localhost \
      --db myapp \
      --user postgres
    ```

    ```bash Command Line Flag theme={null}
    pgschema dump \
      --host localhost \
      --db myapp \
      --user postgres \
      --password mypassword
    ```

    ```bash .pgpass File theme={null}
    # 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
    ```
  </CodeGroup>

  **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)](/cli/dotenv) for detailed configuration options.
</ParamField>

<ParamField path="--sslmode" type="string" default="prefer">
  SSL mode for database connection (env: PGSSLMODE)

  Valid values: `disable`, `allow`, `prefer`, `require`, `verify-ca`, `verify-full`

  For `verify-ca` and `verify-full` modes, you can configure certificate paths using standard PostgreSQL environment variables (`PGSSLROOTCERT`, `PGSSLCERT`, `PGSSLKEY`).
</ParamField>

<ParamField path="--schema" type="string" default="public">
  Schema name to dump
</ParamField>

## Output Options

<ParamField path="--multi-file" type="boolean" default="false">
  Output schema to multiple files organized by object type. See [Multi-file Schema Management Workflow](/workflow/multi-file-schema).

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

<ParamField path="--file" type="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.
</ParamField>

<ParamField path="--no-comments" type="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.
</ParamField>

## Ignoring Objects

You can exclude specific database objects from dumps using a `.pgschemaignore` file. See [Ignore (.pgschemaignore)](/cli/ignore) for complete documentation.

## Examples

### Schema Dump

```bash theme={null}
# 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:

```sql theme={null}
--
-- 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

```bash theme={null}
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:

```sql theme={null}
--
-- 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:

```sql theme={null}
--
-- 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.

```bash theme={null}
# Dump the 'public' schema
pgschema dump --host localhost --db myapp --user postgres --schema public
```

Output for objects within 'public' schema (no qualification):

```sql theme={null}
-- 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:

```bash theme={null}
# 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.