timescale/pg-aiguide / skills/postgres-database-migration
postgres-database-migration
VerifiedUse this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases. **Trigger when user asks to:** - Test a schema migration before applying it to production - Add, remove, or rename columns safely on a live table - Change a column's data type without downtime - Add or drop indexes, constraints, or foreign keys on large tables - Understand which ALTER TABLE operations lock the table - Roll back a failed migration - Plan a zero-downtime migration strategy - Fork a database to test a migration safely **Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, rollback, backfill, deploy Covers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 24.2 KB |
| references/backfill-strategies.md | markdown | 2.3 KB |
| references/complete-example.md | markdown | 2.0 KB |
| references/validation-queries.md | markdown | 3.4 KB |
references/complete-example.md
View on GitHub# Complete Migration Example
End-to-end example: adding a `status` column with a default, a NOT NULL constraint, and an index to a large table.
## Step 1: Plan and Document
```
Migration: Add order_status column to orders table
- Table: orders (~5M rows, 2 GB)
- Change: Add TEXT column with default 'pending', NOT NULL, partial index
- Risk: Low (no rewrite needed on PG 11+)
- Rollback: DROP COLUMN order_status
- Estimated duration: < 1 second for DDL, ~5 minutes for index build
```
## Step 2: Test on a Fork
Fork your database using your provider's fork feature (Neon, or dump/restore).
## Step 3: Run on Fork
```sql
-- Fast: non-volatile default, no rewrite (PG 11+)
ALTER TABLE orders ADD COLUMN order_status TEXT NOT NULL DEFAULT 'pending';
-- Non-blocking index
CREATE INDEX CONCURRENTLY idx_orders_active
ON orders (order_status, created_at DESC)
WHERE order_status NOT IN ('completed', 'cancelled');
```
## Step 4: Validate on Fork
```sql
-- Column exists with correct type
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'order_status';
-- All existing rows have the default
SELECT order_status, COUNT(*) FROM orders GROUP BY order_status;
-- Index is valid and used
EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_status = 'pending' ORDER BY created_at DESC LIMIT 10;
```
## Step 5: Apply to Production
```sql
-- Set timeouts to avoid blocking traffic
SET lock_timeout = '5s';
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN order_status TEXT NOT NULL DEFAULT 'pending';
RESET lock_timeout;
RESET statement_timeout;
-- Index creation is non-blocking, safe to run anytime
CREATE INDEX CONCURRENTLY idx_orders_active
ON orders (order_status, created_at DESC)
WHERE order_status NOT IN ('completed', 'cancelled');
-- Verify index is valid (not left in INVALID state)
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indrelid = 'orders'::regclass AND NOT indisvalid;
```
## Step 6: Clean Up
Delete the test fork if you created one.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the work | Stars: 319k | Updated | Claude CodeCodex | Verified | Install |
| systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixes | Stars: 319k | Updated | Claude CodeCodex | Verified | Install |
| test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation code | Stars: 319k | Updated | Claude CodeCodex | Verified | Install |
| skill-creatoranthropics/skillsCreate new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, edit, or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |