Skip to main content

Documentation Index

Fetch the complete documentation index at: https://www.pgschema.com/llms.txt

Use this file to discover all available pages before exploring further.

The Plan-Review-Apply workflow separates change planning from execution, creating a safety gate that prevents unintended database modifications:
  1. Plan - Generate a detailed migration plan showing exactly what will change
  2. Review - Examine the plan for correctness, safety, and business impact
  3. Apply - Execute the reviewed and approved changes
This separation allows for thorough validation and team collaboration before making any database changes.

​
Workflow

​
Step 1: Plan Your Changes

Generate a migration plan to see what changes pgschema will make: 
# Print a human-readable plan
pgschema plan --host localhost --db myapp --user postgres --file schema.sql

# Or save the plan to a file for review
pgschema plan --host localhost --db myapp --user postgres --file schema.sql \
  --output-human plan.txt \
  --output-json plan.json

​
Step 2: Review the Plan

Examine the generated plan carefully: 
# View the saved plan
cat plan.txt

​
Step 3: Apply the Changes

Once the plan is approved, apply the changes: 
# Apply with interactive confirmation
# For automated deployments, use --auto-approve
pgschema apply --host localhost --db myapp --user postgres --plan plan.json

​
Detect Concurrent Changes with Fingerprinting

Fingerprinting ensures that the exact changes you reviewed in the plan are the changes that get applied, maintaining the integrity of the Plan-Review-Apply workflow.
pgschema uses a fingerprinting mechanism to ensure consistency between the plan and apply phases. When you run pgschema plan, it captures a snapshot (fingerprint) of the current database schema state. Later, when you run pgschema apply, it verifies that the database hasn’t changed since the plan was generated. This prevents scenarios where:
  • Someone else modified the database between your plan and apply
  • The database drifted from the expected state
  • Multiple developers are making concurrent schema changes

​
How Fingerprinting Works

  1. During Plan: pgschema calculates a fingerprint of the current database schema
  2. Plan Storage: The fingerprint is embedded in the plan 
    {
  ...
  "source_fingerprint": {
    "hash": "965b1131737c955e24c7f827c55bd78e4cb49a75adfd04229e0ba297376f5085"
  },
  ...
}
  3. During Apply: pgschema recalculates the database fingerprint and compares it with the stored one
  4. Safety Check: If fingerprints don’t match, apply is aborted with an error

​
Fingerprint Validation Example

# Generate plan (captures current state fingerprint)
pgschema plan --host prod.db.com --db myapp --user postgres --file schema.sql --output-json plan.json

# Time passes... other changes might occur...

# Apply checks fingerprint before proceeding
pgschema apply --host prod.db.com --db myapp --user postgres --plan plan.json
# ✅ Success: Database state matches plan expectations
# ❌ Error: schema fingerprint mismatch detected - the database schema has changed since the plan was generated.
#
#          schema fingerprint mismatch - expected: 965b1131737c955e, actual: abc123456789abcd
When fingerprint mismatches occur, you need to regenerate the plan: 
# If apply fails due to fingerprint mismatch
pgschema plan --host prod.db.com --db myapp --user postgres --file schema.sql --output-json new-plan.json

# Review the updated plan for any unexpected changes
diff plan.json new-plan.json

# Apply with the new plan
pgschema apply --host prod.db.com --db myapp --user postgres --plan new-plan.json

​
Online DDL

pgschema automatically generates migration plans that use online DDL strategies to minimize downtime. When possible, operations like index creation, constraint addition, and schema modifications are executed using PostgreSQL’s concurrent and non-blocking features. For detailed information about online DDL patterns and strategies, see the Online DDL documentation.