Note
Brought to you by Bytebase, open-source database DevSecOps platform.
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.
- Comprehensive Postgres Support: Handles virtually all schema-level database objects across Postgres versions 14 through 18
- 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:
Visit https://www.pgschema.com/installation
Note
Windows is not supported. Please use WSL (Windows Subsystem for Linux) or a Linux VM.
# Dump current schema $ PGPASSWORD=testpwd1 pgschema dump \ --host localhost \ --db testdb \ --user postgres \ --schema public > schema.sql
# 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 );
$ 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;
# 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!
[!NOTE] > For external contributors: If you require any features, please create a GitHub issue to discuss first instead of creating a PR directly.
git clone https://github.com/pgplex/pgschema.git cd pgschema go mod tidy go build -o pgschema .
# Run unit tests only go test -short -v ./... # Run all tests including integration tests (uses Postgres testcontainers with Docker) go test -v ./...
Bytebase - open source, web-based database DevSecOps platform.