18 KiB
name, description
| name | description |
|---|---|
| database-postgres-migrations | Creating database migrations for schema changes |
PostgreSQL Migrations
Scope: Database migrations, schema versioning, zero-downtime deployments Lines: ~280 Last Updated: 2025-10-18
When to Use This Skill
Activate this skill when:
- Creating database migrations for schema changes
- Managing schema versions across environments
- Planning zero-downtime deployments
- Rolling back migrations safely
- Adding/removing columns, tables, or indexes
- Choosing migration tools or strategies
Core Concepts
What Are Migrations?
Migrations are versioned scripts that modify database schema over time.
Key properties:
- Versioned: Each migration has a unique version/timestamp
- Ordered: Applied sequentially (v1 → v2 → v3)
- Tracked: Database knows which migrations are applied
- Reversible: Can roll back (ideally)
- Idempotent: Safe to run multiple times (ideally)
Migration Tools Comparison
| Tool | Languages | Features | Best For |
|---|---|---|---|
| Flyway | Java/SQL | Simple, SQL-first, commercial support | Java apps, enterprise |
| Liquibase | Java/XML/YAML/SQL | Complex, rollback support, database-agnostic | Enterprise, multi-DB |
| golang-migrate | Go/SQL | Simple, CLI-focused, programmatic | Go apps, microservices |
| Alembic | Python/SQL | SQLAlchemy integration, autogenerate | Python apps, Django/Flask |
| dbmate | Any/SQL | Simple, language-agnostic, minimal | Simple projects, polyglot |
| Atlas | Go/HCL | Modern, declarative, schema-as-code | Modern Go apps, GitOps |
Migration File Structure
Flyway
db/migration/
├── V1__initial_schema.sql
├── V2__add_users_table.sql
├── V3__add_orders_table.sql
└── V4__add_user_email_index.sql
Naming: V{version}__{description}.sql
-- V1__initial_schema.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
golang-migrate
migrations/
├── 000001_initial_schema.up.sql
├── 000001_initial_schema.down.sql
├── 000002_add_users_table.up.sql
├── 000002_add_users_table.down.sql
Up/Down pattern: Each migration has .up.sql (apply) and .down.sql (rollback).
-- 000001_initial_schema.up.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
-- 000001_initial_schema.down.sql
-- Example of rollback migration - destructive operation for reverting schema
DROP TABLE users;
Alembic (Python)
# alembic/versions/abc123_add_users_table.py
def upgrade():
op.create_table(
'users',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('email', sa.String(255), unique=True, nullable=False),
sa.Column('created_at', sa.DateTime(), server_default=sa.func.now())
)
def downgrade():
op.drop_table('users')
Writing Safe Migrations
Rule 1: Make Migrations Reversible
-- ✅ GOOD: Can be reversed
-- Up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Down
ALTER TABLE users DROP COLUMN phone;
-- ❌ BAD: Cannot fully reverse (data loss)
-- Up
ALTER TABLE users DROP COLUMN phone;
-- Down (can't restore data!)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
Rule 2: Make Migrations Idempotent
-- ✅ GOOD: Safe to run multiple times
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email VARCHAR(255)
);
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
-- ❌ BAD: Fails if run twice
CREATE TABLE users (...); -- ERROR: relation "users" already exists
ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- ERROR: column already exists
Rule 3: Test Migrations Before Production
# 1. Apply migration on local copy of production data
pg_dump production | psql local_test
migrate up
# 2. Verify schema
psql local_test -c "\d users"
# 3. Test rollback
migrate down
migrate up
# 4. Test application against new schema
npm test
Rule 4: Use Transactions (When Possible)
BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone);
COMMIT;
Note: Some operations can't be in transactions:
CREATE INDEX CONCURRENTLYDROP INDEX CONCURRENTLYVACUUM
For these, split into separate migration files.
Zero-Downtime Migration Patterns
Pattern 1: Adding a Column (Nullable)
Simple case: Adding a nullable column is safe.
-- Migration 1: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
Deploy: Apply migration → Deploy new code (can read/write phone).
No downtime because:
- Old code ignores new column
- New code reads/writes new column
Pattern 2: Adding a Column (NOT NULL)
Problem: ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL locks table and fails if existing rows exist.
Solution: Multi-step approach.
-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Step 2: Backfill existing rows (in batches)
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;
-- Step 3: Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
Deploy sequence:
- Apply Step 1 → Deploy code that writes to phone
- Run Step 2 backfill (batch UPDATE with limits)
- Apply Step 3 → Deploy code that requires phone
Pattern 3: Removing a Column (Multi-Phase)
Problem: Removing a column immediately breaks old code.
Solution: Expand-Contract pattern.
Phase 1 (Expand): Stop writing to column
-- No migration yet, just deploy code that ignores the column
-- Old code still reads column, new code ignores it
Phase 2 (Wait): Ensure all old code is gone (all instances updated)
Phase 3 (Contract): Remove column
-- Migration: Remove column
ALTER TABLE users DROP COLUMN phone;
Timeline:
- Week 1: Deploy code that stops writing to
phone - Week 2: Verify no reads/writes in logs
- Week 3: Drop column via migration
Pattern 4: Renaming a Column (Multi-Phase)
Problem: Renaming breaks old code.
Solution: Dual-write pattern.
Phase 1: Add new column, dual-write
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- Trigger or application code writes to both columns
CREATE TRIGGER sync_email_to_email_address
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION sync_email_columns();
Phase 2: Backfill old data
UPDATE users SET email_address = email WHERE email_address IS NULL;
Phase 3: Switch reads to new column
-- Deploy code that reads email_address instead of email
Phase 4: Remove old column
DROP TRIGGER sync_email_to_email_address ON users;
ALTER TABLE users DROP COLUMN email;
Pattern 5: Adding an Index (CONCURRENTLY)
Problem: CREATE INDEX locks table for writes.
Solution: CREATE INDEX CONCURRENTLY
-- ✅ GOOD: No write lock, builds index in background
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
Trade-offs:
- Takes longer than regular
CREATE INDEX - Can't run inside a transaction
- Can fail and leave invalid index (must clean up)
Check for invalid indexes:
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;
-- Drop invalid index
DROP INDEX CONCURRENTLY idx_users_email;
Pattern 6: Adding a Constraint (Multi-Phase)
Problem: ALTER TABLE users ADD CONSTRAINT ... NOT VALID requires table scan.
Solution: Add constraint without validation first, then validate.
-- Step 1: Add constraint without validation (fast, allows new writes)
ALTER TABLE users ADD CONSTRAINT check_age_positive CHECK (age > 0) NOT VALID;
-- Step 2: Validate constraint (slow, but allows concurrent reads/writes)
ALTER TABLE users VALIDATE CONSTRAINT check_age_positive;
Benefit: Step 1 is fast, Step 2 doesn't block writes.
Data Migrations vs Schema Migrations
Schema Migrations
Changes to structure:
CREATE TABLEALTER TABLE ADD COLUMNCREATE INDEXALTER TABLE ADD CONSTRAINT
Fast: DDL operations (with CONCURRENTLY for indexes).
Data Migrations
Changes to data:
UPDATE users SET status = 'active' WHERE status IS NULL;- Backfilling new columns
- Data transformations
Slow: Can lock tables, requires batching.
Best Practice: Separate Data and Schema
-- Migration 1: Schema change (fast)
ALTER TABLE users ADD COLUMN status VARCHAR(20);
-- Migration 2: Data backfill (slow, run separately)
-- Run in batches to avoid long locks
DO $$
DECLARE
batch_size INT := 1000;
rows_updated INT;
BEGIN
LOOP
UPDATE users
SET status = 'active'
WHERE id IN (
SELECT id FROM users WHERE status IS NULL LIMIT batch_size
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated = 0;
COMMIT; -- Release locks between batches
PERFORM pg_sleep(0.1); -- Avoid overwhelming DB
END LOOP;
END $$;
Rollback Strategies
Strategy 1: Down Migrations (Ideal)
-- Migration up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Migration down
ALTER TABLE users DROP COLUMN phone;
Limitations:
- Some changes are irreversible (data deletion)
- Complex migrations are hard to reverse
Strategy 2: Backup Before Migration
# Backup before migration
pg_dump -Fc -f backup_before_migration.dump production_db
# Apply migration
migrate up
# If rollback needed
pg_restore -d production_db backup_before_migration.dump
Trade-offs:
- Safe, but slow for large databases
- Downtime during restore
Strategy 3: Blue-Green Deployment
- Blue: Current production database
- Green: New database with migrations applied
- Switch traffic from Blue → Green
- If issues, switch back Blue
Benefit: Instant rollback. Cost: Requires data replication, complex setup.
Common Migration Patterns
Adding a Table
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
Safe for zero-downtime: Yes (old code ignores new table).
Dropping a Table
-- Example of safe table drop - requires careful coordination with code deployment
DROP TABLE IF EXISTS old_logs;
Safe for zero-downtime: Only if no code references it (ensure via code deployment first).
Changing Column Type (Dangerous)
-- ❌ DANGEROUS: Locks table, can fail
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;
Better approach:
- Add new column with new type
- Dual-write to both columns
- Backfill old column → new column
- Switch reads to new column
- Drop old column
-- Step 1
ALTER TABLE users ADD COLUMN age_bigint BIGINT;
-- Step 2: Application writes to both
-- Step 3: Backfill
UPDATE users SET age_bigint = age WHERE age_bigint IS NULL;
-- Step 4: Application reads from age_bigint
-- Step 5
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN age_bigint TO age;
Migration Workflow
Step 1: Create Migration
# Flyway
flyway migrate
# golang-migrate
migrate create -ext sql -dir migrations -seq add_users_table
# Alembic
alembic revision -m "add users table"
Step 2: Write Migration SQL/Code
-- V1__add_users_table.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
Step 3: Test Locally
# Apply migration
migrate up
# Verify schema
psql -c "\d users"
# Test rollback
migrate down
migrate up
Step 4: Code Review
Checklist:
- Migration is idempotent?
- Migration is reversible?
- Tested locally with production-like data?
- Zero-downtime compatible?
- Indexes created with CONCURRENTLY?
- Large data migrations batched?
Step 5: Apply to Staging
migrate -database "postgres://staging" up
Verify: Run application tests against staging.
Step 6: Apply to Production
# Backup first
pg_dump -Fc production > backup_$(date +%Y%m%d).dump
# Apply migration
migrate -database "postgres://production" up
# Monitor
tail -f /var/log/postgresql/postgresql.log
Step 7: Monitor
Watch for:
- Migration completion
- Application errors
- Query performance changes
- Lock contention
Quick Reference
Migration Tool Commands
Flyway:
flyway migrate # Apply migrations
flyway info # Show migration status
flyway validate # Validate applied migrations
flyway repair # Fix migration metadata
golang-migrate:
migrate up # Apply all migrations
migrate up 1 # Apply one migration
migrate down 1 # Rollback one migration
migrate version # Show current version
migrate force VERSION # Force version (fix broken state)
Alembic:
alembic upgrade head # Apply all migrations
alembic downgrade -1 # Rollback one migration
alembic current # Show current version
alembic history # Show migration history
Safety Checklist
Before Running Migrations:
[ ] Backup database
[ ] Test migration locally with production-like data
[ ] Verify migration is idempotent
[ ] Verify migration is reversible (or document why not)
[ ] Check for table locks (avoid during high traffic)
[ ] Use CONCURRENTLY for index creation
[ ] Batch large data migrations
[ ] Plan rollback strategy
[ ] Schedule during low-traffic window (if needed)
[ ] Notify team of maintenance window
Common Pitfalls
❌ Adding NOT NULL column without default - Fails on existing rows ✅ Add as nullable first, backfill, then add NOT NULL
❌ Creating indexes without CONCURRENTLY - Locks table
✅ Use CREATE INDEX CONCURRENTLY
❌ Renaming columns directly - Breaks old code ✅ Use expand-contract pattern (add new, dual-write, drop old)
❌ Running large UPDATEs in one transaction - Long locks ✅ Batch updates with LIMIT and pg_sleep()
❌ Not testing rollback - Can't recover from failed migration
✅ Always test migrate down locally
❌ Mixing schema and data changes - Hard to debug ✅ Separate schema migrations from data migrations
Related Skills
postgres-query-optimization.md- Index strategies for migrationspostgres-schema-design.md- Designing schemas that are migration-friendlydatabase-connection-pooling.md- Migration impact on connectionsorm-patterns.md- ORM-specific migration tools (Alembic, etc.)
Level 3: Resources
Location: ~/.hanzo/skills/database/postgres-migrations/resources/
This skill includes comprehensive Level 3 resources for advanced migration management:
REFERENCE.md (~1,800 lines)
Comprehensive reference covering:
- Migration fundamentals and best practices
- Schema versioning strategies (forward-only, bidirectional, immutable)
- Deep dive into migration tools (Flyway, golang-migrate, Alembic, Liquibase, dbmate, Atlas)
- Writing safe migrations (idempotency, transactions, constraints)
- Zero-downtime patterns (add column, add index, rename, change type)
- PostgreSQL lock behavior reference
- Data migrations and batching strategies
- Testing migrations (local, staging, CI/CD)
- Rollback strategies (down migrations, backups, PITR, blue-green)
- Common pitfalls and solutions
- Advanced topics (schema drift, multi-tenancy, partitions)
Scripts (3 production-ready tools)
analyze_migration.py - Migration safety analyzer
- Detects unsafe DDL operations (locks, table rewrites)
- Identifies missing idempotency guards
- Warns about data loss operations
- Checks transaction compatibility
- Suggests safer alternatives
- JSON output for CI/CD integration
generate_migration.py - Migration file generator
- Supports multiple migration tools (Flyway, golang-migrate, Alembic, dbmate)
- Template-based generation (add table, add column, add index, etc.)
- Auto-generates safe, idempotent SQL
- Includes rollback scripts
- Dry-run mode for preview
test_migration.sh - Docker-based migration tester
- Spins up temporary PostgreSQL instance
- Loads production schema dumps
- Applies migrations
- Tests rollback (for supported tools)
- Verifies schema integrity
- Auto-cleanup on exit
- JSON output for CI/CD
Examples
python/alembic_migrations/ - Complete Alembic setup
- Configuration files (alembic.ini, env.py)
- Example migrations (initial schema, add tables)
- README with setup and usage instructions
sql/safe_migrations/ - Safe migration patterns
- Add nullable column
- Add index concurrently
- Add table with constraints
- Add constraint with NOT VALID pattern
sql/unsafe_migrations/ - What NOT to do
- Add NOT NULL column without default
- Create index without CONCURRENTLY
- Rename column directly
- Large UPDATE without batching
docker/ - Testing environment
- docker-compose.yml with PostgreSQL + Flyway + pgAdmin
- Complete testing workflows
- CI/CD integration examples
Usage
# Analyze migration for safety
./resources/scripts/analyze_migration.py migration.sql
# Generate migration from template
./resources/scripts/generate_migration.py --tool flyway --template add-column \
--table users --column phone:varchar
# Test migration in Docker
./resources/scripts/test_migration.sh --migrations-dir migrations/ --test-rollback
See resources/scripts/README.md for complete documentation.
Last Updated: 2025-10-27 Format Version: 1.0 (Atomic)