Apply

The apply command applies database schema changes to a target database schema. You can either provide a desired state file to generate and apply a plan, or execute a pre-generated plan file directly.

Overview

The apply command supports two execution modes:

File Mode (Generate and Apply)

Read the desired state from a SQL file (with include directive support)
Apply the desired state SQL to a temporary PostgreSQL instance (embedded by default, or external via --plan-* flags)
Compare it with the current database state of the target schema
Generate a migration plan with proper dependency ordering
Display the plan for review
Apply the changes (with optional confirmation and safety checks)

By default, File Mode uses an embedded PostgreSQL instance to validate the desired state. Cross-schema FKs to ignored tables are stubbed from the target automatically; for extensions or other cases see External Plan Database.

Plan Mode (Execute Pre-generated Plan)

Load a pre-generated plan from JSON file
Validate plan version compatibility and schema fingerprints
Display the plan for review
Apply the changes (with optional confirmation and safety checks)

Basic Usage

# File mode: Generate and apply plan from desired state file
pgschema apply --host localhost --db myapp --user postgres --password mypassword --file schema.sql

# Plan mode: Execute pre-generated plan
pgschema apply --host localhost --db myapp --user postgres --password mypassword --plan plan.json

# Auto-approve mode for CI/CD (no confirmation prompt)
pgschema apply --host localhost --db myapp --user postgres --password mypassword --file schema.sql --auto-approve

Connection Options

string

default:"localhost"

Database server host (env: PGHOST)

integer

default:"5432"

Database server port (env: PGPORT)

string

required

Database name (required) (env: PGDATABASE)

string

required

Database user name (required) (env: PGUSER)

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

# Create .env file with:
# PGHOST=localhost
# PGPORT=5432
# PGDATABASE=myapp
# PGUSER=postgres
# PGPASSWORD=mypassword

pgschema apply --file schema.sql

PGPASSWORD=mypassword pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--file schema.sql

pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--password mypassword \
--file schema.sql

# Create ~/.pgpass file with:
# hostname:port:database:username:password
# localhost:5432:myapp:postgres:mypassword

# Make sure file has correct permissions
chmod 600 ~/.pgpass

pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--file schema.sql

Password Resolution Order:

Command line --password flag (highest priority)
PGPASSWORD environment variable
.pgpass file in user’s home directory
PostgreSQL will prompt for password if none found

See dotenv (.env) for detailed configuration options.

string

default:"prefer"

SSL mode for database connection (env: PGSSLMODE)Valid values: disable, allow, prefer, require, verify-ca, verify-fullFor verify-ca and verify-full modes, you can configure certificate paths using standard PostgreSQL environment variables (PGSSLROOTCERT, PGSSLCERT, PGSSLKEY).

string

default:"public"

Schema name to apply changes to

Plan Database Options

When using File Mode (--file), the apply command generates a plan internally using a temporary PostgreSQL instance. Cross-schema FKs to ignored tables work with the default embedded instance; extensions and other edge cases may need an external database. See External Plan Database for complete documentation. Note: These options only apply when using --file mode. When using --plan mode, the plan has already been generated.

Apply Options

string

Path to desired state SQL schema file (mutually exclusive with —plan)Used in File Mode to generate and apply a plan from the desired state.

string

Path to pre-generated plan JSON file (mutually exclusive with —file)Used in Plan Mode to execute a plan that was previously generated with pgschema plan --output-json.

boolean

default:"false"

Apply changes without prompting for approvalUseful for automated deployments and CI/CD pipelines.

boolean

default:"false"

Disable colored output in the plan displayUseful for scripts, CI/CD environments, or terminals that don’t support colors.

string

Maximum time to wait for database locks (e.g., ’30s’, ‘5m’, ‘1h’)If not specified, uses PostgreSQL’s default behavior (wait indefinitely). See PostgreSQL lock_timeout documentation.When set, a failed attempt to acquire a lock is automatically retried with exponential backoff instead of failing immediately. CREATE INDEX CONCURRENTLY is never retried.

string

default:"pgschema"

Application name for database connection (visible in pg_stat_activity) (env: PGAPPNAME)See PostgreSQL application_name documentation.

Ignoring Objects

You can exclude specific database objects from schema application using a .pgschemaignore file. See Ignore (.pgschemaignore) for complete documentation.

Examples

File Mode (Generate and Apply)

# Interactive mode - shows plan and prompts for confirmation
pgschema apply --host localhost --db myapp --user postgres --file desired_schema.sql

This will:

Generate a migration plan by comparing the desired state with current database
Display the plan with colored output
Prompt: “Do you want to apply these changes? (yes/no):”
Wait for confirmation before proceeding
Apply changes using transactions where possible

Example output:

Plan: 2 to add, 1 to modify.

Summary by type:
  tables: 1 to add, 1 to modify
  indexes: 1 to add

Tables:
  + posts
  ~ users
    + email (column)

Indexes:
  + idx_users_email

Transaction: true

DDL to be executed:
--------------------------------------------------

CREATE TABLE IF NOT EXISTS posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    user_id INTEGER REFERENCES users(id)
);

ALTER TABLE users ADD COLUMN email VARCHAR(255) UNIQUE;

CREATE INDEX idx_users_email ON users(email);

Do you want to apply these changes? (yes/no): 

Plan Mode (Execute Pre-generated Plan)

# First, generate a plan
pgschema plan --host localhost --db myapp --user postgres --file schema.sql --output-json plan.json

# Then apply the pre-generated plan
pgschema apply --host localhost --db myapp --user postgres --plan plan.json

Benefits of plan mode:

Separation of concerns: Generate plans in one environment, apply in another
Review process: Plans can be reviewed before deployment
Repeatability: Same plan can be applied to multiple environments
Version control: Plans can be stored and versioned
Rollback preparation: Generate rollback plans before applying changes

Auto-approve for CI/CD

pgschema apply \
  --host prod-db \
  --db myapp \
  --user deployer \
  --file schema.sql \
  --auto-approve \
  --no-color

With Lock Timeout

# Avoid blocking busy tables: wait at most 30s for a lock before backing off and retrying
pgschema apply \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --lock-timeout "30s"

Custom Application Name

pgschema apply \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --application-name "pgschema-deployment-v1.1.0"

-- Monitor active pgschema connections:
SELECT application_name, state, query_start, query 
FROM pg_stat_activity 
WHERE application_name LIKE 'pgschema%';

Safety Features

Schema Fingerprint Validation

When using plan mode, pgschema validates that the database schema hasn’t changed since the plan was generated:

# Generate plan
pgschema plan --host localhost --db myapp --user postgres --file schema.sql --output-json plan.json

# Database schema changes here...

# Apply plan - will detect changes and fail
pgschema apply --host localhost --db myapp --user postgres --plan plan.json

If schema changes are detected:

Error: schema fingerprint mismatch detected - the database schema has changed since the plan was generated.

Expected fingerprint: abc123...
Current fingerprint:  def456...
Difference: Table 'users' was modified

To resolve this issue:
1. Regenerate the plan with current database state: pgschema plan ...
2. Review the new plan to ensure it's still correct
3. Apply the new plan: pgschema apply ...

Version Compatibility

Plans include version information to ensure compatibility:

pgschema version: Must match the version used to generate the plan
Plan format version: Must be supported by the current pgschema version

Transaction Handling

pgschema automatically determines whether changes can run in a transaction:

Transactional mode (default): All changes run in a single transaction with automatic rollback on failure
Non-transactional mode: Some operations (like CREATE INDEX CONCURRENTLY) run outside transactions

Transaction: true    # Changes will run in a transaction
Transaction: false   # Some changes cannot run in a transaction

No-op Detection

If no changes are needed, pgschema skips execution:

No changes to apply. Database schema is already up to date.

Getting started

CLI Reference

Workflow

Configuration

Overview

File Mode (Generate and Apply)

Plan Mode (Execute Pre-generated Plan)

Basic Usage

Connection Options

Plan Database Options

Apply Options

Ignoring Objects

Examples

File Mode (Generate and Apply)

Plan Mode (Execute Pre-generated Plan)

Auto-approve for CI/CD

With Lock Timeout

Custom Application Name

Safety Features

Schema Fingerprint Validation

Version Compatibility

Transaction Handling

No-op Detection

​Overview

​File Mode (Generate and Apply)

​Plan Mode (Execute Pre-generated Plan)

​Basic Usage

​Connection Options

​Plan Database Options

​Apply Options

​Ignoring Objects

​Examples

​File Mode (Generate and Apply)

​Plan Mode (Execute Pre-generated Plan)

​Auto-approve for CI/CD

​With Lock Timeout

​Custom Application Name

​Safety Features

​Schema Fingerprint Validation

​Version Compatibility

​Transaction Handling

​No-op Detection

Overview

File Mode (Generate and Apply)

Plan Mode (Execute Pre-generated Plan)

Basic Usage

Connection Options

Plan Database Options

Apply Options

Ignoring Objects

Examples

File Mode (Generate and Apply)

Plan Mode (Execute Pre-generated Plan)

Auto-approve for CI/CD

With Lock Timeout

Custom Application Name

Safety Features

Schema Fingerprint Validation

Version Compatibility

Transaction Handling

No-op Detection