> ## 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** - The embedded approach only loads one schema at a time, breaking foreign key constraints that reference tables in other schemas ([#122](https://github.com/pgplex/pgschema/issues/122))

### 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, you need to create those schemas in the plan database:

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

CREATE SCHEMA IF NOT EXISTS billing;
CREATE TABLE IF NOT EXISTS billing.customers (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES auth.users(id)
);
```

Then run plan or apply:

```bash theme={null}
# Set up referenced schemas in plan database
psql -h localhost -U postgres -d pgschema_plan << 'EOF'
CREATE SCHEMA IF NOT EXISTS auth;
CREATE TABLE IF NOT EXISTS auth.users (id SERIAL PRIMARY KEY, email TEXT NOT NULL);
EOF

# Now run plan for your main schema that references auth.users
pgschema plan \
  --file schema.sql \
  --schema public \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres

# Or use apply command to plan and apply in one step (File Mode)
pgschema apply \
  --file schema.sql \
  --schema public \
  --host localhost --db myapp --user postgres \
  --plan-host localhost --plan-db pgschema_plan --plan-user postgres \
  --auto-approve
```

Your `schema.sql` can now reference tables in other schemas:

```sql theme={null}
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES auth.users(id),  -- Cross-schema FK works
  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