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)
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 and pgschema stubs it automatically during plan (embedded postgres is enough). Use the fallbacks below only when the table is missing on target (#122, #548)

How It Works

When using an external database:

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

Basic Usage

With Plan Command

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

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

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

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

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

# .pgschemaignore
[schemas]
patterns = ["auth"]

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

-- 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 for full details. Fallback: stub in the schema file — when the referenced table is not on the target:

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:

-- 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
);

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:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES auth.users(id),
  total DECIMAL(10,2)
);

Configuration Options

Using Command-Line Flags

string

Plan database server host. If provided, uses external database instead of embedded PostgreSQL.Environment variable: PGSCHEMA_PLAN_HOST

integer

default:"5432"

Plan database server port.Environment variable: PGSCHEMA_PLAN_PORT

string

required

Plan database name. Required when --plan-host is provided.Environment variable: PGSCHEMA_PLAN_DB

string

required

Plan database user name. Required when --plan-host is provided.Environment variable: PGSCHEMA_PLAN_USER

string

Plan database password. Can also be provided via PGSCHEMA_PLAN_PASSWORD environment variable.Environment variable: PGSCHEMA_PLAN_PASSWORD

string

default:"prefer"

Plan database SSL mode. Valid values: disable, allow, prefer, require, verify-ca, verify-fullEnvironment variable: PGSCHEMA_PLAN_SSLMODE

Using Environment Variables

# 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

# 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

# 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

Database Permissions

The plan database user needs the following permissions:

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

Getting started

CLI Reference

Workflow

Configuration

Overview

When to Use External Database

How It Works

Basic Usage

With Plan Command

With Apply Command (File Mode)

Common Use Cases

Using PostgreSQL Extensions

Handling Cross-Schema Foreign Keys

Configuration Options

Using Command-Line Flags

Using Environment Variables

Database Permissions

See Also

​Overview

​When to Use External Database

​How It Works

​Basic Usage

​With Plan Command

​With Apply Command (File Mode)

​Common Use Cases

​Using PostgreSQL Extensions

​Handling Cross-Schema Foreign Keys

​Configuration Options

​Using Command-Line Flags

​Using Environment Variables

​Database Permissions

​See Also

Overview

When to Use External Database

How It Works

Basic Usage

With Plan Command

With Apply Command (File Mode)

Common Use Cases

Using PostgreSQL Extensions

Handling Cross-Schema Foreign Keys

Configuration Options

Using Command-Line Flags

Using Environment Variables

Database Permissions

See Also