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

# External Plan Database

The `plan` and `apply` commands can use an external PostgreSQL database instead of the default embedded PostgreSQL instance for validating desired state schemas. This is useful in environments where embedded PostgreSQL has limitations.

## Overview

By default, the `plan` command (and `apply` command in File Mode) spins up a temporary embedded PostgreSQL instance to apply and validate your desired state SQL. However, you can optionally provide your own PostgreSQL database using the `--plan-*` flags or `PGSCHEMA_PLAN_*` environment variables.

**Note**: For the `apply` command, these options only apply when using File Mode (`--file`). When using Plan Mode (`--plan`), the plan has already been generated, so plan database options are not applicable.

### When to Use External Database

Use an external database for plan generation when:

* Your schema uses **PostgreSQL extensions** (like `hstore`, `postgis`, `uuid-ossp`, etc.) - The embedded database doesn't have extensions pre-installed, causing plan generation to fail with "type does not exist" errors ([#121](https://github.com/pgplex/pgschema/issues/121))
* Your schema has **cross-schema foreign key references** to tables you do not manage (for example Supabase `auth.users`) - If the referenced table exists on the target database, [ignore that schema or table](/cli/ignore) and pgschema stubs it automatically during plan (embedded postgres is enough). Use the fallbacks below only when the table is missing on target ([#122](https://github.com/pgplex/pgschema/issues/122), [#548](https://github.com/pgplex/pgschema/issues/548))

### How It Works

When using an external database:

1. **Temporary Schema Creation**: pgschema creates a temporary schema with a unique timestamp (e.g., `pgschema_tmp_20251030_154501_123456789`)
2. **SQL Application**: Your desired state SQL is applied to the temporary schema
3. **Schema Inspection**: The temporary schema is inspected to extract the desired state
4. **Comparison**: The desired state is compared with your target database's current state
5. **Cleanup**: The temporary schema is dropped (best effort) after plan generation

## Basic Usage

### With Plan Command

```bash theme={null}
# Use external database for plan generation
pgschema plan \
  --file schema.sql \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres

# With all options specified
pgschema plan \
  --file schema.sql \
  --host localhost --port 5432 --db myapp --user postgres \
  --plan-host localhost --plan-port 5432 --plan-db pgschema_plan --plan-user postgres --plan-password secret
```

### With Apply Command (File Mode)

```bash theme={null}
# Use external database when applying from file
pgschema apply \
  --file schema.sql \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres \
  --auto-approve

# The apply command generates a plan internally using the external database,
# then applies the changes to the target database
```

## Common Use Cases

### Using PostgreSQL Extensions

If your schema uses extensions like `hstore`, `postgis`, or `uuid-ossp`, you need to install them in the plan database first:

```sql theme={null}
-- In your plan database, install required extensions
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
```

Then run plan or apply with the external database:

```bash theme={null}
# Install extensions in plan database
psql -h localhost -U postgres -d pgschema_plan -c "CREATE EXTENSION IF NOT EXISTS hstore;"

# Now run plan - it will work because extensions are available
pgschema plan \
  --file schema.sql \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres

# Or use apply command directly (File Mode)
pgschema apply \
  --file schema.sql \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres \
  --auto-approve
```

Your `schema.sql` can now use extension types:

```sql theme={null}
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  attributes HSTORE,  -- Works because hstore extension is installed
  location GEOGRAPHY(POINT, 4326)  -- Works because postgis is installed
);
```

### Handling Cross-Schema Foreign Keys

If your schema has foreign keys that reference tables in other schemas (for example `REFERENCES auth.users`), those referenced objects must exist when `plan` applies your desired-state SQL to its temporary database.

**Recommended: ignore the referenced schema or table** (default embedded plan database — no manual stub, no external plan DB):

```toml theme={null}
# .pgschemaignore
[schemas]
patterns = ["auth"]

# Or a single table:
# [tables]
# patterns = ["auth.users"]
```

```sql theme={null}
-- schema.sql — your app schema only
CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  auth_user_id UUID NOT NULL UNIQUE REFERENCES auth.users (id) ON DELETE CASCADE
);
```

When you run `pgschema plan`, pgschema clones a structural stub of each ignored FK target from the **target** database (columns plus PRIMARY KEY / UNIQUE constraints) into the temporary plan instance. The stub is not managed: dump does not emit `auth.users`, and plan will not create or drop it on the target.

Requirements:

* The referenced table must already exist on the **target** database (for example Supabase already has `auth.users`).
* Ignore patterns for cross-schema tables must be schema-qualified (`auth.users`, `auth.*`, or `[schemas] auth`). A bare `users` pattern only matches tables in the schema you are managing.

See [Ignore](/cli/ignore) for full details.

**Fallback: stub in the schema file** — when the referenced table is not on the target:

```sql theme={null}
CREATE SCHEMA IF NOT EXISTS auth;
CREATE TABLE IF NOT EXISTS auth.users (
  id UUID PRIMARY KEY
);

CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  auth_user_id UUID NOT NULL UNIQUE REFERENCES auth.users (id)
);
```

`pgschema dump` of your app schema will not emit the stub, so keep it in source (for example via `\i`).

**Fallback: external plan database** — when you cannot rely on the target during plan, or need objects that only exist in another database:

```sql theme={null}
-- In your plan database
CREATE SCHEMA IF NOT EXISTS auth;
CREATE TABLE IF NOT EXISTS auth.users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);
```

```bash theme={null}
pgschema plan \
  --file schema.sql \
  --schema public \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres
```

Your `schema.sql` references `auth.users` without inlining the stub:

```sql theme={null}
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES auth.users(id),
  total DECIMAL(10,2)
);
```

## Configuration Options

### Using Command-Line Flags

<ParamField path="--plan-host" type="string">
  Plan database server host. If provided, uses external database instead of embedded PostgreSQL.

  Environment variable: `PGSCHEMA_PLAN_HOST`
</ParamField>

<ParamField path="--plan-port" type="integer" default="5432">
  Plan database server port.

  Environment variable: `PGSCHEMA_PLAN_PORT`
</ParamField>

<ParamField path="--plan-db" type="string" required="conditional">
  Plan database name. Required when `--plan-host` is provided.

  Environment variable: `PGSCHEMA_PLAN_DB`
</ParamField>

<ParamField path="--plan-user" type="string" required="conditional">
  Plan database user name. Required when `--plan-host` is provided.

  Environment variable: `PGSCHEMA_PLAN_USER`
</ParamField>

<ParamField path="--plan-password" type="string">
  Plan database password. Can also be provided via `PGSCHEMA_PLAN_PASSWORD` environment variable.

  Environment variable: `PGSCHEMA_PLAN_PASSWORD`
</ParamField>

<ParamField path="--plan-sslmode" type="string" default="prefer">
  Plan database SSL mode. Valid values: `disable`, `allow`, `prefer`, `require`, `verify-ca`, `verify-full`

  Environment variable: `PGSCHEMA_PLAN_SSLMODE`
</ParamField>

### Using Environment Variables

<CodeGroup>
  ```bash .env File (Recommended) theme={null}
  # Target database connection
  PGHOST=localhost
  PGPORT=5432
  PGDATABASE=myapp
  PGUSER=postgres
  PGPASSWORD=mypassword

  # Plan database connection (optional)
  PGSCHEMA_PLAN_HOST=localhost
  PGSCHEMA_PLAN_PORT=5432
  PGSCHEMA_PLAN_DB=pgschema_plan
  PGSCHEMA_PLAN_USER=postgres
  PGSCHEMA_PLAN_PASSWORD=planpassword
  PGSCHEMA_PLAN_SSLMODE=prefer

  # Run plan with external database
  pgschema plan --file schema.sql

  # Or apply (File Mode) with external database
  pgschema apply --file schema.sql --auto-approve
  ```

  ```bash Environment Variables theme={null}
  # Set environment variables
  export PGHOST=localhost
  export PGDATABASE=myapp
  export PGUSER=postgres
  export PGPASSWORD=mypassword

  export PGSCHEMA_PLAN_HOST=localhost
  export PGSCHEMA_PLAN_DB=pgschema_plan
  export PGSCHEMA_PLAN_USER=postgres
  export PGSCHEMA_PLAN_PASSWORD=planpassword
  export PGSCHEMA_PLAN_SSLMODE=prefer

  # Run plan
  pgschema plan --file schema.sql

  # Or apply (File Mode)
  pgschema apply --file schema.sql --auto-approve
  ```

  ```bash Command Line Only theme={null}
  # Plan command - all options as flags (no environment variables)
  pgschema plan \
    --file schema.sql \
    --host localhost \
    --db myapp \
    --user postgres \
    --password mypassword \
    --plan-host localhost \
    --plan-db pgschema_plan \
    --plan-user postgres \
    --plan-password planpassword

  # Apply command (File Mode) - all options as flags
  pgschema apply \
    --file schema.sql \
    --host localhost \
    --db myapp \
    --user postgres \
    --password mypassword \
    --plan-host localhost \
    --plan-db pgschema_plan \
    --plan-user postgres \
    --plan-password planpassword \
    --auto-approve
  ```
</CodeGroup>

## Database Permissions

The plan database user needs the following permissions:

```sql theme={null}
-- Minimum required permissions
GRANT CREATE ON DATABASE pgschema_plan TO your_plan_user;
GRANT USAGE ON SCHEMA public TO your_plan_user;
```

The user must be able to:

* Create and drop schemas
* Create tables, indexes, functions, and other schema objects
* Set search\_path

## See Also

* [Plan Command](/cli/plan) - Main plan command documentation
* [Apply Command](/cli/apply) - Applying migration plans
* [Environment Variables](/cli/dotenv) - Managing environment configuration