Database Migration Strategies: Cutover, Reconciliation and Rollback

Choose a database migration strategy with explicit write ownership, reconciliation gates and recovery boundaries. Includes PostgreSQL 17 DDL cautions and a worked...

Define the disruption and data-loss boundary first

Choose a database migration strategy from the application's write behavior, compatibility constraints and recovery requirements. Table size matters, but so do a forgotten batch writer, a long transaction and a schema change that the previous application cannot read.

Define the permitted write pause, read behavior, acceptable loss of acknowledged transactions and recovery time before choosing a tool. “Zero downtime” is too vague to be an acceptance criterion: an API returning success while its writes are lost has not met the requirement.

This guide covers two related decisions: changing a schema while application versions overlap, and moving the authoritative database to another target. PostgreSQL-specific behavior below is scoped to version 17. Other engines, versions, extensions and managed-service restrictions require their own review. The examples are planning models, not an executed migration or a tested production runbook.

Match the strategy to the change

| Situation | Strategy and limiting condition | |---|---| | A bounded maintenance window is acceptable | Stop writes, copy and validate, then resume. Include restore and abort time when estimating the window. | | Old and new code must share one database | Expand the schema, deploy compatible code, backfill, switch reads, then retire old fields. | | Writes must continue during a long copy | Use a consistent initial copy with change capture and replay, followed by a controlled cutover. | | The target has different data semantics | Define transformations and business reconciliation before copying. Row counts alone cannot prove equivalence. | | An engine upgrade has a supported vendor path | Rehearse that exact path, including extensions and client compatibility; do not assume an in-place downgrade exists. |

Offline migration can be the simpler, more auditable choice when the interruption fits the business. Continuous replication adds operational state, failure modes and a final handover. It does not eliminate the need to decide which database is allowed to accept writes.

Expand and contract preserves application compatibility

Suppose an application must replace a legacy status field with a richer representation. Add the new field without removing the old one, then deploy code that understands both. Keep the old representation valid while old readers or writers still exist.

If both fields live in the same transactional database, update them together in one transaction where possible. While old binaries still write only the old field, keep that field authoritative and use a tested synchronization mechanism, or defer the read switch until every writer maintains both fields. Define a single conversion rule. Backfill in resumable batches with an explicit concurrency policy, such as updating only an eligible row whose source version has not changed. A blind backfill can overwrite a newer application update.

Test old and new application versions against each intermediate schema. Include workers, exports, administrative scripts and delayed jobs in the consumer inventory. Switch reads only after the new representation is complete and reconciled. Delay richer values that old code cannot represent until rollback to that code is no longer required.

Removing the old field is a separate release. Its gate is evidence that no supported reader or writer needs it and that the retained recovery plan no longer depends on it. A successful deployment of new code does not establish that every background process has stopped using the old column.

PostgreSQL 17 DDL needs operation-specific checks

For ordinary, non-partitioned tables, these examples illustrate the checks to make. They are not a universal list of nonblocking operations.

| Operation | PostgreSQL 17 boundary | |---|---| | Add a nullable column without a default | Avoids a table rewrite, but the operation still needs a table lock. A long-running transaction can delay acquisition. | | Add a foreign-key or CHECK constraint with NOT VALID | Skips the initial existing-row validation scan, while enforcing the constraint on subsequent writes. It is not generic syntax for every constraint type. | | Validate that constraint later | Scans existing data under its documented lock mode. Schedule and observe the resulting load. | | Build an index with CREATE INDEX CONCURRENTLY | Allows writes during construction, but performs extra work and waits. It cannot run inside a transaction block. |

The PostgreSQL 17 ALTER TABLE reference describes lock levels, rewrites and constraint validation. Test lock acquisition against representative long transactions, set workload-appropriate lock and statement timeouts, and inspect the impact on application latency and replicas. Do not put a guessed timeout into a shared migration template.

Concurrent index creation can fail and leave an invalid index that still incurs maintenance overhead. A failed concurrent unique index may also continue enforcing uniqueness. Inspect its state before retrying and follow the PostgreSQL 17 index-build guidance. Transaction-wrapping migration tools need specific handling for this operation.

Keep one write authority during a platform move

Two sequential writes to independent databases are not one atomic operation. If the source commit succeeds and the target write fails, the databases diverge. Retrying the request can also repeat a business side effect. Changing the order of the writes only changes which failure occurs first.

A migration commonly keeps the source authoritative while a replication mechanism copies committed changes to a target that is read-only to application writers. Coordinate the initial snapshot and change-log position so writes during the copy are neither skipped nor overwritten by older snapshot rows.

For application-level propagation, a transactional outbox can record a change event in the same transaction as the source update. A separate relay sends committed events. The consumer still needs duplicate handling, ordering appropriate to the entity, retry recovery and reconciliation. The AWS transactional outbox pattern explains that boundary. An outbox supports propagation; it does not by itself migrate historical data or make both databases synchronously consistent.

PostgreSQL 17 logical replication is another option for compatible tables. Published tables need a suitable replica identity for updates and deletes, usually the primary key; inspect the publication rules for exceptions and performance implications. Inventory every table and operation rather than assuming the publication includes all business data.

PostgreSQL 17 does not replicate schema changes or sequence state through logical replication, and large objects are excluded. Before enabling target writes, separately prepare schema, sequence allocation and any excluded data. Review the logical replication restrictions, along with users, permissions, jobs, extensions and application connection behavior.

Monitor retained transaction logs as well as replication lag. A stalled or abandoned replication slot can retain WAL and consume source disk; the subscription documentation explains slot management. Do not drop a slot to clear an alert without understanding the effect on the migration's replay and recovery path.

Worked example: prove catch-up before booking cutover

Assume a hypothetical migration has a backlog of 90,000 comparable change events. The target applies 6,000 events per second while the source produces 3,000 per second.

Net drain rate = 6,000 - 3,000 = 3,000 events/second

Estimated catch-up time = 90,000 / 3,000 = 30 seconds

If application writes stop, the simplified drain estimate becomes 90,000 / 6,000 = 15 seconds. If target throughput is only 2,800 while 3,000 new events arrive each second, the backlog grows by 200 events per second instead of draining.

These are steady-rate teaching calculations. Transactions vary in size, indexes add work, apply workers may stall and resource contention can change throughput. Use representative rehearsal measurements and the replication system's actual units. A rate measured during an empty period does not establish peak-load capacity.

Turn the estimate into explicit cutover gates

For an illustrative same-engine move, suppose the business permits a write pause of up to 180 seconds and requires all acknowledged source transactions to be present on the target. Reads may continue only through a path that remains consistent with the agreed user experience.

The team would need to rehearse a pre-target-write abort that restores source service within a reserved 60 seconds. That leaves at most 180 - 60 = 120 seconds for the attempt before triggering that abort. These are hypothetical planning inputs, not a recommended window or measured result.

| Gate | Evidence required before proceeding | |---|---| | Ready to pause | Initial copy complete; replication healthy; reconciliation rehearsed; application, data and incident owners present. | | Source fenced | API writers, workers, imports and administrative paths stopped or denied writes; in-flight transactions accounted for. | | Final position applied | Capture the final source boundary after fencing and verify its application on the target using the selected replication mechanism. | | Target verified | Required reconciliations pass at that boundary; schema, sequences, permissions and application connections are ready. | | Write authority transferred | Source remains fenced, target becomes the sole writer and business-level checks confirm successful operations. |

Reserve time for verification, connection changes and recovery. If a pre-transfer gate cannot be proven before the abort deadline, keep the target fenced and resume the source through the rehearsed path. Once target writes begin, use the post-cutover recovery plan below instead. Queueing writes during the pause requires its own durable acceptance, ordering and idempotency design; do not silently claim requests succeeded before they were recorded.

Reconcile business state, not only counts

Compare data at a common, stable boundary. Live source and target queries taken at different times can disagree even when replication is working correctly.

Use row counts by key range to find omissions, deterministic content comparisons to find changed values, and business invariants to check meaning. Examples include order totals matching their line items, unique external identifiers remaining unique, and deleted records remaining deleted. Define canonical handling for timestamps, decimals, nulls and transformed fields before hashing or comparing them.

Shadow reads can expose result differences, but they add load and may observe different replication positions. Keep them read-only, protect sensitive data and prevent duplicate external side effects. Sampling helps discover defects; it does not prove that every row is correct. Specify which invariants require full verification and which comparisons are sampled, with the residual risk accepted by the owner.

Rollback changes after the first target write

Before the target accepts new writes, an abort can resume the intact source if it remains authoritative and no required state has been lost. After the target accepts writes, that source may be stale. Switching traffic back at that point can discard acknowledged work or create conflicting histories.

Choose the post-cutover recovery strategy in advance: a tested reverse-replication path with conflict and loop controls, a forward fix on the target, or a controlled restore and reconciliation procedure. State the recovery time and possible data loss honestly. Bidirectional replication is a separate design, not an emergency toggle.

Keep the old system protected from accidental writes during the observation period. Retire it only after acceptance, backup and restore checks, downstream validation and retention approval. Removing old tables, logs or backups can close recovery paths that were available during cutover.

Produce the migration decision record

Before scheduling production work, record the exact engine and version, table and writer inventory, transformation rules, allowed disruption, data-loss objective, replication method and reconciliation evidence. Add the go/no-go owner, abort deadline, last safely reversible point and post-cutover recovery procedure.

Next action

Rehearse one missed update, one replayed change, a long lock holder, an apply-worker failure and an abort before target writes. Record what detected each failure and whether recovery stayed within the proposed boundary. Resolve any gap before the production date is agreed.

For changes tied to application compatibility and replacement, see platform modernization. Use the enterprise CI/CD release guide to connect migration approval to the exact application artifact and release evidence.

Technical references checked September 20, 2026. PostgreSQL details are scoped to version 17. Numerical examples are hypothetical and require workload-specific rehearsal before use.