SQL 127 🛢️ Cross-Database Migration Strategies and Schema Evolution
Cross-database migration and schema evolution are two different problems that are often confused because both involve moving data and changing structure. A cross-database migration moves data from one database engine to another—PostgreSQL to SQL Server, MySQL to PostgreSQL, on-premises to cloud. Schema evolution changes the structure of a database while it is running, with the application still serving traffic. The first problem is solved by extraction and loading tools; the second is solved by patterns and deployment discipline. The overlap is that a migration often requires schema changes, and a schema change often requires a migration. The distinction matters because the failure modes are different: a migration can lose data, and a schema change can break a running application.
Key point: A schema change that locks a table or removes a column that the running code still uses is not a migration problem; it is a deployment problem. The expand-and-contract pattern exists to separate the structural change from the code change, so that neither requires downtime and neither breaks the other.
Why these two problems require different strategies
The engine difference problem. Moving from PostgreSQL to SQL Server is not a matter of copying files. The data types are different, the SQL dialect is different, the transaction semantics are different, and the catalog is different. Tools like Microsoft’s SQL Server Migration Assistant (SSMA) exist to automate the conversion from Access, Db2, MySQL, Oracle, and SAP ASE to SQL Server, Azure SQL Database, or Azure SQL Managed Instance . For PostgreSQL-to-PostgreSQL upgrades, pg_upgrade and logical replication are the native tools . For PostgreSQL-to-anywhere, pg_dump and pg_restore remain the portable mechanism .
The running-application problem. A schema change that adds a column is safe: the old code does not know about the column, and the database accepts inserts without it. A schema change that renames a column or changes its type is not safe: the old code references the old name or type, and the change breaks it. The expand-and-contract pattern solves this by splitting the change into three phases: expand (add the new structure without removing the old), migrate (update the code to use the new structure while keeping the old one working), and contract (remove the old structure once no code uses it) .
The downtime problem. A migration or a schema change that requires downtime is a business decision, not a technical one. The tools and patterns that avoid downtime exist to give the business the option of continuous operation. pg_dump and pg_restore with parallel jobs can reduce the window for a PostgreSQL-to-PostgreSQL migration . pgroll and similar tools provide zero-downtime schema migrations by serving multiple schema versions simultaneously through views . Online schema change tools like gh-ost use a shadow table and binary log streaming to apply changes without locking the original table .
a. Cross-Database Migration: Tools and Trade-offs
The choice of migration tool depends on the source and target engines, the size of the data, and the acceptable downtime.
For PostgreSQL-to-PostgreSQL migrations, pg_dump produces a logical dump that is internally consistent—a snapshot of the database at the time the dump started . The custom format (-Fc) allows pg_restore to be selective and to reorder items, and it is compressed by default . For large databases, the -j flag parallelizes the dump and restore. The PostgreSQL community recommends a specific sequence for multi-database migrations: dump globals first, then dump each database in parallel, restore globals, then restore each database in parallel, and analyze afterward .
# Dump globals (roles, tablespaces)
pg_dumpall -h oldserver --globals > globals.sql
# Dump each database in parallel custom format
pg_dump -h oldserver -j 8 -Fc -f mydb.dump mydb
# Restore globals and database
psql postgres -f globals.sql
pg_restore -j 8 --exit-on-error -cC -d postgres mydb.dump
# Analyze after restore
vacuumdb --analyze --jobs=8 -d mydbFor minor version upgrades within the same major version, pg_upgrade is faster because it uses hard links to the old data files in link mode, avoiding a full copy . For major version upgrades, pg_upgrade still works but requires more post-upgrade processing. Logical replication is the alternative for zero-downtime upgrades: the old cluster remains the publisher, the new cluster is the subscriber, and the switchover happens after replication catches up .
For cross-engine migrations—PostgreSQL to SQL Server, MySQL to PostgreSQL—the tools are more varied. SSMA automates the schema conversion and data migration for Microsoft’s targets . Open-source tools like ingestra provide a general extraction and loading flow with schema alignment and upsert, supporting PostgreSQL, MySQL, BigQuery, Redshift, and others . The pattern is consistent: extract the data, align the target schema, load into a staging area, and merge into the final table.
b. Schema Evolution: The Expand-and-Contract Pattern
The expand-and-contract pattern is the standard approach for changing a schema without breaking running code . It is not a tool; it is a discipline that any migration tool can implement.
Phase 1: Expand. Add the new structure without removing the old. For a column rename, add the new column as nullable and backfill it from the old column. For a table rename, create the new table and copy the data. For a constraint change, add the new constraint without dropping the old one. The goal is that both the old code and the new code can run against the same schema during this phase.
-- Expand: add new column, keep old column
ALTER TABLE users ADD COLUMN display_name text;Phase 2: Migrate. Update the application code to read from the new structure and write to both. For a column rename, the code writes to both the old and new columns during the transition. For a table rename, the code reads from the new table but still writes to both. For a constraint change, the code adapts to the new constraint. This phase ends when all application instances are running the new code.
Phase 3: Contract. Remove the old structure. Drop the old column, drop the old table, drop the old constraint. This phase runs only after the migration is complete and no code references the old structure.
-- Contract: drop old column after all code uses display_name
ALTER TABLE users DROP COLUMN name;The trigger-based backfill is the mechanism that keeps the old and new columns synchronized during Phase 2. A trigger on the old column writes to the new column, and a trigger on the new column writes to the old one, so that any write that arrives from either code path is reflected in both . Without this, a write from the old code would update only the old column, and the new code reading the new column would see stale data.
c. Zero-Downtime Migration Tools
The expand-and-contract pattern requires coordination between the schema change and the code deployment. Tools like pgroll automate this coordination for PostgreSQL.
pgroll works by creating virtual schemas through views on top of the physical tables. When a migration starts, it performs the additive changes in the physical schema and creates a new view that exposes the new schema version. The old view continues to expose the old schema. Client applications connect to the schema version they expect by setting search_path to the view name . This means the old and new code can run simultaneously, each against its own view, while the physical schema is being changed underneath.
# Initialize pgroll
pgroll init --postgres-url postgres://...
# Start a migration (adds new column, creates new view)
pgroll start migration.json
# Applications switch search_path to the new view
# After all applications use the new version:
pgroll completeFor MySQL, the online schema change tools follow a different pattern. pt-online-schema-change creates a shadow table, attaches a trigger to the original table to capture changes, copies existing rows, and then renames the shadow table to replace the original . gh-ost does the same but uses binary log streaming instead of triggers, which avoids the trigger overhead on every DML operation . The triggerless design is faster on write-heavy tables because the shadow table writes are decoupled from the original table’s transaction.
The trade-off between trigger-based and triggerless online schema change is operational. Trigger-based tools are simpler to set up but add latency to every write. Triggerless tools require the database to have binary logging enabled and the tool to have replication privileges, but they do not interfere with the application’s write path .
Complete Example Session
-- ============================================
-- PART 1: CROSS-DATABASE MIGRATION (PG TO PG)
-- ============================================
-- Dump globals and database
pg_dumpall -h oldserver --globals > globals.sql
pg_dump -h oldserver -j 8 -Fc -f mydb.dump mydb
-- Restore on new server
psql postgres -f globals.sql
pg_restore -j 8 --exit-on-error -cC -d postgres mydb.dump
-- ============================================
-- PART 2: SCHEMA EVOLUTION — EXPAND PHASE
-- ============================================
-- Rename "name" to "display_name" without breaking old code
ALTER TABLE users ADD COLUMN display_name text;
-- Backfill from old column
UPDATE users SET display_name = name WHERE display_name IS NULL;
-- ============================================
-- PART 3: SYNC TRIGGERS DURING TRANSITION
-- ============================================
-- Keep both columns in sync while old and new code run
CREATE OR REPLACE FUNCTION sync_user_name() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
NEW.display_name := COALESCE(NEW.display_name, NEW.name);
NEW.name := COALESCE(NEW.name, NEW.display_name);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER sync_user_name_trigger
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_name();
-- ============================================
-- PART 4: MIGRATE APPLICATION CODE
-- ============================================
-- Deploy new code that reads display_name
-- Old code still reads name; trigger keeps both in sync
-- ============================================
-- PART 5: CONTRACT PHASE
-- ============================================
-- After all code uses display_name:
DROP TRIGGER sync_user_name_trigger ON users;
DROP FUNCTION sync_user_name();
ALTER TABLE users DROP COLUMN name;
-- ============================================
-- PART 6: ZERO-DOWNTIME SCHEMA CHANGE WITH pgroll
-- ============================================
-- migration.json: add column
{
"name": "add-display-name",
"operations": [
{"add_column": {"table": "users", "column": {"name": "display_name", "type": "text"}}}
]
}
pgroll start migration.json
-- New view created; old view still works
pgroll complete
-- Old view removed
-- ============================================
-- PART 7: MIGRATION VERIFICATION
-- ============================================
-- Check row counts match
SELECT count(*) FROM users;
-- Compare source and target counts
-- Check data integrity for a sample
SELECT id, name FROM oldserver.users LIMIT 10;
SELECT id, display_name FROM newserver.users LIMIT 10;
-- ============================================
-- PART 8: CROSS-ENGINE MIGRATION (PG TO MYSQL)
-- ============================================
-- Extract to NDJSON
COPY (SELECT * FROM users) TO '/tmp/users.ndjson' WITH (FORMAT csv, HEADER);
-- Load into MySQL
LOAD DATA INFILE '/tmp/users.ndjson' INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
-- ============================================
-- PART 9: POST-MIGRATION CLEANUP
-- ============================================
-- Drop old replication slots
SELECT pg_drop_replication_slot('old_slot');
-- Analyze tables
VACUUM ANALYZE;
-- ============================================
-- PART 10: ROLLBACK PLAN
-- ============================================
-- If migration fails before contract:
-- 1. Stop new code
-- 2. Revert to old view (pgroll)
-- 3. Drop new column
-- 4. Old column still exists and is intactThe ten parts trace the two problems separately and then show where they intersect. Parts 2 through 5 show the expand-and-contract pattern for a column rename. Part 6 shows the pgroll tool that automates the pattern. Parts 1 and 8 show the cross-database migration flows.
Quick Reference
Migration Tools by Scenario
| Scenario | Tool | Notes |
|---|---|---|
| PostgreSQL → PostgreSQL (same version) | pg_dump + pg_restore | Parallel with -j |
| PostgreSQL major upgrade | pg_upgrade | Link mode is fastest |
| Zero-downtime PostgreSQL upgrade | Logical replication | Publisher/subscriber switchover |
| Any → SQL Server | SSMA | Automates schema conversion |
| PostgreSQL → BigQuery/Redshift | ingestra or dbt-bridge | Extract, align, load, merge |
Expand-and-Contract Phases
| Phase | Action | Code Compatibility |
|---|---|---|
| Expand | Add new structure, keep old | Both old and new code work |
| Migrate | Backfill, sync triggers, deploy new code | Both work |
| Contract | Drop old structure | Only new code works |
Zero-Downtime Tools
| Tool | Engine | Mechanism |
|---|---|---|
pgroll | PostgreSQL | Views expose multiple schema versions |
pt-online-schema-change | MySQL | Shadow table + triggers |
gh-ost | MySQL | Shadow table + binary log streaming |
pg_dump Format Comparison
| Format | Flag | Compressed | Selective Restore |
|---|---|---|---|
| Plain | -Fp | No | No |
| Custom | -Fc | Yes | Yes |
| Tar | -Ft | No | Yes |
Best Practices
✅ Do This:
# Use parallel dump and restore for large databases
pg_dump -j 8 -Fc -f db.dump mydb && pg_restore -j 8 -d newdb db.dump # ✅-- Add new column as nullable during expand phase
ALTER TABLE users ADD COLUMN display_name text; -- ✅-- Use sync triggers to keep old and new columns consistent
CREATE TRIGGER sync_trigger BEFORE INSERT OR UPDATE ON users ...; -- ✅# Verify row counts and sample data before contract phase
SELECT count(*) FROM users; -- ✅❌ Don’t Do This:
-- Rename a column in a single step while code is running
ALTER TABLE users RENAME COLUMN name TO display_name; -- ❌-- Drop the old column before all code uses the new one
ALTER TABLE users DROP COLUMN name; -- ❌# Migrate without a rollback plan
pg_restore -d production db.dump; # no verification, no rollback -- ❌-- Assume a schema change is safe because it is "just" a type change
-- Changing text to varchar(50) can truncate data and break inserts -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Migration loses rows | No row count verification | Compare counts before and after |
| Application breaks after schema change | Column dropped before code updated | Expand-and-contract; contract only after deploy |
| Long lock during ALTER TABLE | Large table, no online tool | Use pgroll or off-peak window |
| Trigger overhead on high-write table | Trigger-based sync | Use triggerless tool (gh-ost) or accept latency |
| Migration takes too long | Single-threaded dump | Use -j flag for parallel |
Real-World Examples
1. Parallel Dump and Restore
pg_dump -j 8 -Fc -f mydb.dump mydb && pg_restore -j 8 -d newdb mydb.dump2. Expand Phase: Add Column
ALTER TABLE users ADD COLUMN display_name text;3. Backfill from Old Column
UPDATE users SET display_name = name WHERE display_name IS NULL;4. Sync Trigger
CREATE TRIGGER sync_trigger BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_name();5. Contract Phase: Drop Old Column
DROP TRIGGER sync_trigger ON users;
ALTER TABLE users DROP COLUMN name;6. pgroll Start Migration
pgroll start migration.json7. pgroll Complete
pgroll complete8. Verify Row Counts
SELECT count(*) FROM users;9. Drop Replication Slot After Migration
SELECT pg_drop_replication_slot('migration_slot');10. Analyze After Restore
vacuumdb --analyze --jobs=8 -d mydbVisual
The Expand-and-Contract Timeline
┌──────────────────────────────────────────────────────────────┐
│ PHASE 1: EXPAND │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ Add new column (nullable) ││
│ │ Old code: works (ignores new column) ││
│ │ New code: not deployed yet ││
│ └─────────────────────────────────────────────────────────┘│
│ │ │
│ ▼ │
│ PHASE 2: MIGRATE │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ Backfill new column from old ││
│ │ Add sync triggers (both columns stay consistent) ││
│ │ Deploy new code (reads new column) ││
│ │ Old code: still works (trigger syncs writes) ││
│ └─────────────────────────────────────────────────────────┘│
│ │ │
│ ▼ │
│ PHASE 3: CONTRACT │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ All code uses new column ││
│ │ Drop sync triggers ││
│ │ Drop old column ││
│ │ New code: works ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ At no point does the running code see a schema it cannot │
│ handle. The change is split across deployments. │
│ │
└──────────────────────────────────────────────────────────────┘pgroll Schema Versions
┌──────────────────────────────────────────────────────────────┐
│ PHYSICAL TABLE: users (name, display_name) │
│ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ VIEW: public_v1 (old schema) ││
│ │ SELECT id, name AS name, ... FROM users ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ VIEW: public_v2 (new schema) ││
│ │ SELECT id, display_name AS name, ... FROM users ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ Old code: SET search_path = public_v1; │
│ New code: SET search_path = public_v2; │
│ │
│ Both run simultaneously against the same physical table. │
│ The contract phase drops the old view and the old column. │
│ │
└──────────────────────────────────────────────────────────────┘Cross-Engine Migration Flow
┌──────────────────────────────────────────────────────────────┐
│ SOURCE DATABASE │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ Extract (pg_dump, COPY, or tool-specific) ││
│ └──────────────────────────┬──────────────────────────────┘│
│ ▼ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ Staging area (NDJSON, CSV, or cloud storage) ││
│ └──────────────────────────┬──────────────────────────────┘│
│ ▼ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ Align target schema (create tables, types) ││
│ └──────────────────────────┬──────────────────────────────┘│
│ ▼ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ Load into target (COPY, LOAD DATA, upsert) ││
│ └──────────────────────────┬──────────────────────────────┘│
│ ▼ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ Verify (row counts, sample data, constraints) ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ The pattern is consistent across tools. │
│ The differences are in the extraction and loading syntax. │
│ │
└──────────────────────────────────────────────────────────────┘Downtime Comparison
┌──────────────────────────────────────────────────────────────┐
│ APPROACH DOWNTIME RISK │
│ │
│ Stop, dump, restore Hours Data loss if restore │
│ (depends on fails, no rollback │
│ size) │
│ │
│ Logical replication Minutes Replication lag, │
│ (switchover) slot management │
│ │
│ pgroll Zero Tool complexity, │
│ (running app) view overhead │
│ │
│ Expand-and-contract Zero Deployment discipline, │
│ (manual) (running app) trigger overhead │
│ │
│ The zero-downtime approaches trade operational complexity │
│ for availability. The choice depends on the business │
│ requirement for uptime. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| PostgreSQL dump | pg_dump -Fc (custom format, compressed, selective) |
| Parallel dump/restore | -j flag |
| PostgreSQL upgrade | pg_upgrade (link mode fastest) |
| Zero-downtime upgrade | Logical replication |
| SQL Server migration | SSMA |
| Expand phase | Add new structure, keep old |
| Migrate phase | Backfill, sync triggers, deploy code |
| Contract phase | Drop old structure |
| pgroll | Zero-downtime schema migrations via views |
| gh-ost | Triggerless online schema change for MySQL |
Key takeaways:
- Cross-database migration and schema evolution are different problems with different tools. A migration moves data between engines; a schema change alters structure while the application runs. The first uses extraction and loading tools; the second uses patterns like expand-and-contract and deployment discipline .
- The expand-and-contract pattern separates structural change from code change. Add the new structure without removing the old, deploy code that works with both, then remove the old structure. The running application never sees a schema it cannot handle.
- Sync triggers are the mechanism that keeps old and new columns consistent during the transition. Without them, a write from the old code updates only the old column, and the new code reads stale data from the new column .
- Zero-downtime tools automate the pattern.
pgrollserves multiple schema versions through views, allowing old and new code to run simultaneously .gh-ostandpt-online-schema-changeuse shadow tables to apply schema changes without locking the original table . - The migration tool depends on the source and target.
pg_dumpandpg_restorefor PostgreSQL-to-PostgreSQL.pg_upgradefor major version upgrades. SSMA for SQL Server targets.ingestraanddbt-bridgefor general cross-engine flows .
Remember: Migration and schema evolution are both about change, but the change is different. A migration changes where the data lives. A schema evolution changes what the data looks like. The tools for the first are extraction and loading; the tools for the second are patterns and deployment discipline. The overlap is that a migration often requires a schema change, and the safest way to make that change is to separate it into expand, migrate, and contract phases. The expand phase is additive and safe. The migrate phase requires synchronization. The contract phase is destructive and requires confidence that no code depends on the old structure. The pattern is not a tool; it is a way of thinking about change that any tool can implement.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!