PostgreSQL Schema Diff

CLI tool for comparing PostgreSQL database schemas.

Features

The tool allows you to compare schemas of two PostgreSQL databases and identify differences in:

• Tables and columns
• Indexes
• Triggers
• Sequences
• Functions
• Views
• Constraints

Quick Start

Installation

# Clone repository
git clone <repository-url>
cd postgres_schema_diff

# Build
make build

# Or build directly with go
go build -o postgres_schema_diff

Quick Example

# Compare two pg_dump files
./postgres_schema_diff -source-file tests/source.sql -target-file tests/target.sql

# Compare two databases
./postgres_schema_diff \
  -source-db "postgresql://user:password@localhost/db1?sslmode=disable" \
  -target-db "postgresql://user:password@localhost/db2?sslmode=disable"

Usage

Comparing pg_dump Files

./postgres_schema_diff -source-file tests/source.sql -target-file tests/target.sql

Comparing Live Databases

./postgres_schema_diff \
  -source-db "postgresql://user:password@localhost:5432/source_db?sslmode=disable" \
  -target-db "postgresql://user:password@localhost:5432/target_db?sslmode=disable"

Mixed Mode (File and Database)

./postgres_schema_diff \
  -source-file dump.sql \
  -target-db "postgresql://user:password@localhost:5432/target_db?sslmode=disable"

Flags

• -source-file - path to SQL file with source pg_dump
• -target-file - path to SQL file with target pg_dump
• -source-db - connection string to source database
• -target-db - connection string to target database
• -ignore-roles - ignore differences in object permissions/owners

Output Format

The tool outputs differences in diff style with automatic legend:

Legend (shown at the beginning of output):

• - : exists in SOURCE, missing or different in TARGET
• + : exists in TARGET, missing or different in SOURCE
• ! : modified between SOURCE and TARGET

If schemas are identical, output will be empty (exit code 0).

Output Example

Legend:
  - : exists in SOURCE, missing or different in TARGET
  + : exists in TARGET, missing or different in SOURCE
  ! : modified between SOURCE and TARGET

! TABLE [users] COLUMN email:
  - email VARCHAR(100) UNIQUE
  + email VARCHAR(255)
+ INDEX idx_users_login (added)
+ TABLE [users] COLUMN login: login VARCHAR(30) NOT NULL
- TABLE [users] COLUMN username: username VARCHAR(50) NOT NULL UNIQUE
- INDEX idx_items_price (removed)

Explanation:

• Table name is always shown in square brackets [table_name]
• ! - modified element, followed by indented lines:
  • - old value (SOURCE)
  • + new value (TARGET)
• + - element added in TARGET (missing in SOURCE)
• - - element removed from TARGET (exists in SOURCE but not in TARGET)
• For added/removed objects, suffix (added)/(removed) is used

PostgreSQL Connection String

Format: postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]

Examples:

postgresql://localhost/mydb
postgresql://user@localhost/mydb
postgresql://user:secret@localhost:5432/mydb
postgresql://user:secret@localhost:5432/mydb?sslmode=disable

CI/CD Usage Examples

The project includes ready-to-use examples for integration with various CI/CD systems. See examples/README.md for details.

Available examples:

• GitHub Actions - schema checks in PRs, scheduled drift detection
• GitLab CI - migration validation, environment comparison
• Jenkins - declarative pipeline with Docker
• CircleCI - orbs and workflows
• Ready-to-use bash scripts for common tasks
• Docker Compose for local development

Quick start:

# GitHub Actions
cp examples/ci-cd/github-actions.yml .github/workflows/schema-check.yml

# GitLab CI
cp examples/ci-cd/gitlab-ci.yml .gitlab-ci.yml

# Using ready-made scripts
export PRODUCTION_DB_URL="postgresql://..."
export STAGING_DB_URL="postgresql://..."
./examples/scripts/check_prod_vs_staging.sh

Testing

The project includes comprehensive tests for all operation modes. See tests/README.md for details.

Quick Test Run

# File-based tests only (without Docker)
./tests/scripts/run_tests.sh

# Start test databases
./tests/scripts/setup_db.sh

# Run all tests (including database tests)
./tests/scripts/run_tests.sh

# Stop databases
./tests/scripts/teardown_db.sh

Test Scenarios

1. Comparing pg_dump files - tests SQL parsing and schema comparison
2. Comparing databases - tests PostgreSQL connection and schema extraction
3. Mixed mode - tests combination of files and databases

Limitations

• Comparison is performed only for the public schema
• When comparing pg_dump files, standard SQL CREATE commands are supported
• Some PostgreSQL-specific constructs may require parser improvements