Database Migration Patterns

Database migration patterns describe how to move schema and data between environments or engine versions while preserving integrity, minimizing downtime, and providing safety nets. They matter whenever schema changes are needed in production, when upgrading engines, or when consolidating fleets. This page is not a tutorial for a specific product; it is a catalog of repeatable approaches, the commands and flags you’ll actually type, and the failure modes that catch teams off guard.

Add a column without taking the database offline

Reader intent: you need to add a column (or modify an existing one) while writes continue uninterrupted. A naïve ALTER TABLE ... ADD COLUMN ... NOT NULL rewrites the entire table, taking an exclusive lock and blocking all traffic for the duration of the rewrite.

Pattern: three‑step approach that keeps the table available throughout.

1. Add the column as nullable with a default value.
2. Backfill existing rows with the intended value.
3. Apply the NOT NULL constraint.

Concrete:

psql -c "ALTER TABLE orders ADD COLUMN status TEXT DEFAULT 'pending';"
psql -c "UPDATE orders SET status = 'pending' WHERE status IS NULL;"
psql -c "ALTER TABLE orders ALTER COLUMN status SET NOT NULL;"

Flag note: psql -c sends a single command string. When scripting, psql -f migration.sql reads from a file. The --set ON_ERROR_STOP=on meta‑command can be added to halt on the first error, but it does not catch logical errors such as constraint violations during the UPDATE step.

Sharp edge: on very large tables, even the ALTER TABLE ... SET NOT NULL step may trigger a table rewrite if the engine cannot validate not‑null inline. Some engines offer an online “rebuild” option, but those are product‑specific and outside the scope of this pattern.

Confused option: many practitioners reach for ALTER TABLE ... ADD COLUMN IF NOT EXISTS, which is syntactic sugar that some engines support and others do not. If your engine does not implement the IF NOT EXISTS clause, the statement fails on an existing table, and you must check the column’s presence beforehand with a metadata query.

Migrate to a new engine version or a different engine

Reader intent: you are upgrading a major version (e.g., PostgreSQL 12 → 15) or switching the underlying engine entirely (e.g., MySQL → PostgreSQL). The core pattern is export, adjust, import, verify.

Pattern:

• Export the current schema and data using your engine’s dump utility.
• Inspect the export for engine‑specific constructs (types, functions, syntax) and rewrite or drop them for the target.
• Import the adjusted dump into the new engine.
• Run verification queries to confirm row counts, checksums, and application‑level behavior.

Concrete:

# Export (engine‑provided utility)
pg_dump --schema=public --format=custom --no-owner --no-acl -Fc -f dump.dump old_host

# Import
pg_restore -d new_db --jobs=4 dump.dump

If you are moving between engine families, the dump format may be textual SQL; in that case the import command simplifies to:

psql -f schema_and_data.sql new_db

Error text you may encounter:

pg_restore: error: could not execute query: ERROR: type "public.status_enum" does not exist on target

This indicates that an enum or domain used in the source engine has no equivalent in the target. The fix is to replace the construct with a CHECK constraint or a native type before the import step.

Verification pattern: after import, run a selective count and a hash of a representative column set, e.g.:

psql -c "SELECT count(*), md5(array_agg(id::text)) FROM orders;" new_db

and compare the output to the pre‑migration baseline.

Cutover application traffic after migration

Reader intent: you have moved schema and data, and now you need to switch the running application to the new database endpoint without losing in‑flight requests or corrupting state.

Pattern:

1. Update the application’s data source name (DSN) or connection string to point at the new engine.
2. Run a health‑check probe against the new endpoint to ensure it accepts connections and basic queries.
3. Drain the connection pool of the old endpoint: stop accepting new connections, allow existing ones to finish or timeout.
4. Flip a feature flag or route shift in your load balancer or API gateway to direct 100 % traffic to the new database.
5. Monitor for the next several minutes for connection errors, timeout spikes, or unexpected query failures.

Concrete:

# Example: update a Kubernetes deployment’s environment variable
kubectl set env deployment/app DB_URL=new_db_connection_string

# Health‑check probe
curl -s "$NEW_DATABASE_URL/healthz"

# If using a connection pooler such as PgBouncer, reload or restart the pooler to pick up the new DSN

Sharp edge: connection pool clients may hold references to the old endpoint until their lease expires. If the pool has a long max_lifetime, old connections can continue sending queries to the retired host, causing “connection refused” errors that are hard to trace until you realize the pool hasn’t refreshed. Always verify pool reload or restart after a DSN change.

Confused option: some teams attempt the cutover by simply updating a DNS A record pointing at the new host. This works only if the application resolves the name per‑request (no DNS caching). Most production systems cache DNS for the TTL duration, so a record change can take minutes to propagate, during which some clients still hit the old address. Prefer an explicit DSN override or a feature‑flag controlled route.

Rollback and verification

Reader intent: the cutover produced errors or data discrepancies, and you need to revert to the previous state quickly and safely.

Pattern:

1. Preserve a point‑in‑time snapshot of the new engine before any data mutation, typically a physical backup or a logical dump taken immediately after import.
2. If anomalies are detected, halt further traffic to the new engine.
3. Restore the snapshot to the original engine name or a temporary name, then re‑point the application DSN to the restored endpoint.
4. Run the same verification queries used in the migration phase (row counts, checksums) to confirm the restored state matches the pre‑migration baseline.

Concrete:

# Assume you took a dump right after import:
pg_dump --schema=public --format=custom --no-owner --no-acl -Fc -f post_migration.dump new_db

# To rollback:
pg_restore -d old_db --redirect-db old_host post_migration.dump

# Or, if you kept a physical backup:
systemctl restart postgresql
pg_restore -d old_db /var/backups/dump_pre_migration.dump

Verification after rollback:

psql -c "SELECT count(*), md5(array_agg(id::text)) FROM orders;" old_db

If the checksums match the pre‑migration baseline, the rollback is confirmed successful.

Sharp edges – what silently fails, confused options, and production‑lesson patterns

• Replication lag invisibility: many engines expose SELECT from the primary with near‑zero lag, but read replicas or cross‑cluster replication can be seconds or minutes behind. A migration that assumes “read‑your‑writes” consistency may appear successful while the replica still holds old data. Always query the primary or wait for replication‑lag metrics to report zero before cutover.

• DEFAULT vs ON UPDATE: adding a column with DEFAULT 'pending' only sets the value for new rows. An UPDATE ... SET status = DEFAULT is required to populate existing rows, or the backfill step (step 2 in the online‑schema‑change pattern) must be explicit. Forgetting this leaves NULLs or stale defaults in production.

• ALTER TABLE ... SET NOT NULL pitfall: if the column contains NULLs after the backfill step (e.g., due to a buggy UPDATE), the constraint application fails with an error such as ERROR: null value violates not‑null constraint. The pattern assumes the backfill succeeded; if it didn’t, you must investigate why the UPDATE affected zero rows.

• Connection pool drain timing: as noted in the cutover section, pool clients cache the DSN. A common production outage occurs when a team cuts over, sees no immediate errors, and declares success—only for reports to surface an hour later because stale pool connections are still querying the old host. Always restart or reload the pooler after a DSN change, and verify with SHOW CONNECTIONS‑equivalent diagnostics.

• Enum and type migration gaps: when moving between engines, custom types, enums, and domains rarely transfer automatically. The dump import will fail with “type does not exist” errors. The pattern requires you to materialize these as CHECK constraints or native types before import, or accept that those columns will be lost or typed as generic text.

• Silent data truncation: some engines silently truncate data that exceeds a column’s defined length during import or ALTER TABLE, especially when moving from a wider type (e.g., PostgreSQL TEXT) to a narrower one (e.g., MySQL VARCHAR(255)). Always inspect the import logs for warnings like WARNING: truncating data for column name.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.