Skip to main content
This guide will walk you through the core pgschema workflow: dumping a schema, making edits, planning changes, and applying migrations. By the end, you’ll understand how to manage Postgres schemas declaratively.

​
Prerequisites

Before starting, ensure you have:
  • pgschema installed (see installation guide)
  • Access to a PostgreSQL database (14+)
  • Database credentials with appropriate permissions

​
Step 1: Dump Your Current Schema

First, let’s capture the current state of your database schema: 
$ PGPASSWORD=testpwd1 pgschema dump \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public > schema.sql
This creates a schema.sql file containing your complete schema definition. Let’s look at what it contains:
schema.sql
-- Example output
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
The output is clean and developer-friendly, unlike the verbose output from pg_dump.

​
Step 2: Make Schema Changes

Now, let’s modify the schema.sql file to add new features. Edit the file to include:
schema.sql (modified)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100),  -- New column
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()  -- New column
);

-- New index
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);

-- New table
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

​
Step 3: Generate Plan

$ PGPASSWORD=testpwd1 pgschema plan \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public \
    --file schema.sql \
    --output-json plan.json \
    --output-human stdout
This will save the migration plan to plan.json and print the human-readable version to stdout:
plan.txt
Plan: 2 to add, 1 to modify.

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

Tables:
  + public.posts
  ~ public.users
    + column name
    + column updated_at

Indexes:
  + public.idx_users_name

Transaction: true

DDL to be executed:
--------------------------------------------------
ALTER TABLE users 
ADD COLUMN name VARCHAR(100);

ALTER TABLE users 
ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();

CREATE INDEX idx_users_name ON users (name);

CREATE TABLE posts (
    id SERIAL NOT NULL,
    user_id integer,
    title VARCHAR(200) NOT NULL,
    content text,
    created_at timestamp DEFAULT NOW(),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id)
);

​
Step 4: Apply Changes

When you’re ready to apply the changes: 
# Apply using the plan file from Step 3
$ PGPASSWORD=testpwd1 pgschema apply \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public \
    --plan plan.json

​
Alternative: Direct Apply

You can also skip the separate planning step and apply changes directly from the schema file: 
# Apply directly from schema file (combines plan + apply)
$ PGPASSWORD=testpwd1 pgschema apply \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public \
    --file schema.sql
This approach automatically generates the plan internally and applies the changes in one command.
The two-phase workflow (plan then apply) is recommended for production environments where you want to review and save the plan before execution. Direct apply is convenient for development environments.
pgschema will:
  1. Show you the migration plan
  2. Ask for confirmation (unless using --auto-approve)
  3. Apply the changes
  4. Report success or any errors
⌘I