pgschema: A Go repository from aadrian

pgschema is a CLI tool that brings terraform-style declarative schema migration workflow to Postgres:

Dump a Postgres schema in a developer-friendly format with support for all common objects
Edit a schema to the desired state
Plan a schema migration by comparing desired state with current database state
Apply a schema migration with concurrent change detection, transaction-adaptive execution, and lock timeout control

Think of it as Terraform for your Postgres schemas - declare your desired state, generate plan, preview changes, and apply them with confidence.

Key differentiators from other tools

Comprehensive Postgres Support: Handles virtually all schema-level database objects across Postgres versions 14 through 17
State-Based Terraform-Like Workflow: No separate migration table needed to track migration history - determines changes by comparing your schema files with actual database state
Schema-Level Focus: Designed for real-world Postgres usage patterns, from single-schema applications to multi-tenant architectures
No Shadow Database Required: Works directly with your schema files and target database - no temporary databases needed for validation

See more details in the introduction blog post.

Watch in action:

Installation

Visit https://www.pgschema.com/installation

Note

Windows is not supported. Please use WSL (Windows Subsystem for Linux) or a Linux VM.

Getting help

Quick example

Step 1: Dump schema

# Dump current schema
$ PGPASSWORD=testpwd1 pgschema dump \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public > schema.sql

Step 2: Edit schema

# Edit schema file declaratively
--- a/schema.sql
+++ b/schema.sql
@@ -12,5 +12,6 @@

 CREATE TABLE IF NOT EXISTS users (
     id SERIAL PRIMARY KEY,
-    username varchar(50) NOT NULL UNIQUE
+    username varchar(50) NOT NULL UNIQUE,
+    age INT NOT NULL
 );

Step 3: Generate plan

$ PGPASSWORD=testpwd1 pgschema plan \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public \
    --file schema.sql \
    --output-human stdout \
    --output-json plan.json

Plan: 1 to modify.

Summary by type:
  tables: 1 to modify

Tables:
  ~ users
    + age (column)

Transaction: true

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

ALTER TABLE users ADD COLUMN age integer NOT NULL;

Step 4: Apply plan with confirmation

# Or use --auto-approve to skip confirmation
$ PGPASSWORD=testpwd1 pgschema apply \
    --host localhost \
    --db testdb \
    --user postgres \
    --schema public \
    --plan plan.json

Plan: 1 to modify.

Summary by type:
  tables: 1 to modify

Tables:
  ~ users
    + age (column)

Transaction: true

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

ALTER TABLE users ADD COLUMN age integer NOT NULL;

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

Applying changes...
Changes applied successfully!

LLM Readiness

Development