Quickstart

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:

Show you the migration plan
Ask for confirmation (unless using --auto-approve)
Apply the changes
Report success or any errors

Getting started

CLI Reference

Workflow

Configuration

Prerequisites

Step 1: Dump Your Current Schema

Step 2: Make Schema Changes

Step 3: Generate Plan

Step 4: Apply Changes

Alternative: Direct Apply

​Prerequisites

​Step 1: Dump Your Current Schema

​Step 2: Make Schema Changes

​Step 3: Generate Plan

​Step 4: Apply Changes

​Alternative: Direct Apply

Prerequisites

Step 1: Dump Your Current Schema

Step 2: Make Schema Changes

Step 3: Generate Plan

Step 4: Apply Changes

Alternative: Direct Apply