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 third-party PostgreSQL extensions (like postgis or pgvector) - The embedded database automatically mirrors every extension installed on the target, but only extensions bundled with PostgreSQL (contrib: hstore, pg_trgm, citext, uuid-ossp, etc.) are available to it. Anything else causes plan generation to fail with “type does not exist” errors (#121, #584)
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

pgschema does not manage extensions, so dump never emits CREATE EXTENSION and you should not add it to your schema files. Instead, the plan database must already have the extensions your schema uses, in the same schema as on the target. Extension-owned objects are also unmanaged. dump omits their definitions and privileges, and plan leaves them alone. This includes privileges changed after installation, such as a custom grant on spatial_ref_sys or a revoked PUBLIC grant on an extension function. Manage those privileges separately; do not put extension-member GRANT/REVOKE statements in the desired schema. In an external plan database those statements can affect the preinstalled extension itself, outside the temporary schema, without representing a managed change. Application objects that use extension types or functions remain managed, including their privileges. Membership is determined from PostgreSQL’s dependency catalog, not object names or the schema containing the extension. Unlike pg_dump, pgschema does not export changes from an extension’s initial privileges. The default embedded plan database handles this for you: it installs every extension found on the target database before applying your schema. This covers all extensions bundled with PostgreSQL (hstore, pg_trgm, citext, uuid-ossp, btree_gist, …). Third-party extensions such as postgis or pgvector are not bundled, so for those you need an external plan database. An external plan database is not mirrored automatically: install every extension your schema uses yourself, bundled ones included, in the same schema as on the target:

-- In your plan database, install required extensions
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS postgis;

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; CREATE EXTENSION IF NOT EXISTS postgis;"

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

Roles

pgschema does not manage roles either — they are cluster-global — so dump emits GRANT ... TO app_user but never CREATE ROLE app_user, and you should not add CREATE ROLE to your schema files. To keep that output usable by plan, every role your schema references (GRANT/REVOKE grantees, CREATE POLICY ... TO, ALTER DEFAULT PRIVILEGES FOR ROLE grantors) is stubbed in the plan database before your schema is applied:

Embedded plan database: stubs are created in the throwaway instance and vanish with it.
External plan database: stubs are created on the plan host and dropped on cleanup. Only roles pgschema created are ever dropped; a role that already exists there is left untouched. The plan user needs CREATEROLE (or the roles must already exist on the plan host). Stub roles are cluster-global, so concurrent plans sharing one plan host can contend on them — give each concurrent run its own plan host, or pre-create the roles there.

A referenced role must exist on the target database. If it does not, plan fails up front with role "x" is referenced by the schema but does not exist on the target database, because apply would fail on it anyway — create the role on the target first.

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

Roles

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

​Roles

​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

Roles

Handling Cross-Schema Foreign Keys

Configuration Options

Using Command-Line Flags

Using Environment Variables

Database Permissions

See Also