Database Migrations: Compatibility, Cutover and Recovery Gates

An evidence-led migration method covering PostgreSQL 17 DDL, concurrent backfill, reconciliation, write authority and the limits of rollback.

Decision brief

Audience: database owners, application leads and operators planning a schema change or a move between data stores.

Decision: whether a proposed migration can preserve the application's required behavior, and what recovery remains possible at each phase.

Thesis: migration safety depends on compatibility, write authority and retained information. A traffic switch is not data recovery, and recreating a dropped column does not restore its values.

The worked scenarios in this paper are illustrative. They do not represent customer migrations, measured availability or Ampity delivery results. PostgreSQL statements are discussed against version 17 documentation. The executable JavaScript fixture tests a small ordering model, not a database engine. Production approval requires integration and crash tests on the actual database, driver, replication and application versions.

The existing route retains “zero downtime” for continuity, but this paper does not promise it. Define the intended user-level availability before deciding whether that description is accurate.

The migration lead owns the evidence packet and phase decisions. Database, application, security and product owners supply acceptance conditions within their responsibilities. A migration tool can move records; it cannot decide whether a delayed order, changed permission or unrecoverable update is acceptable to the business. Assign those decisions before choosing the tool.

Use the system architecture paper to define the operation's invariant and data authority. This paper addresses how to preserve that contract while representations and serving systems change.

1. Define downtime at the transaction boundary

Measure whether a user can complete an operation correctly within its agreed time budget. A healthy load balancer and successful health check do not prove checkout, account creation or a write-dependent workflow remains available.

If cutover pauses writes, state the impact. A queue may absorb submissions while processing waits, but that changes acceptance and completion semantics. Capacity, persistence, maximum wait, cancellation and status lookup must be designed and tested. An acknowledgement of queued intent must not masquerade as a completed transaction.

If a bounded maintenance period is the safer option, document it plainly. Compare the operational complexity and failure exposure of continuous migration with the agreed interruption. Neither option is universally preferable. Product and support owners must accept the behavior customers will experience.

Define a measurement window, operation denominator and treatment of retries. Record failures hidden by automatic retry and requests abandoned while waiting. Without those definitions, a statement such as “no errors during cutover” is not useful evidence of uninterrupted service.

2. Inventory compatibility before changing schema

List every reader and writer: application versions, jobs, reporting tools, exports, administrative scripts, integrations and recovery procedures. A column that appears unused in application code may still be part of a customer export or a scheduled job.

Write a compatibility matrix across old and new application versions and old, expanded and contracted schemas. Include deserialization, null behavior, defaults, generated values and authorization checks. Treat stored data semantics separately from column names.

For a same-database example, suppose an application introduces an optional display label while retaining the existing display name. New readers can fall back to the old field during transition. This is easier to reverse than replacing a value with a lossy normalization and deleting the original.

Do not infer reversibility from a syntactically reversible schema operation. A new application may write values the old version cannot understand. A schema can look compatible while a changed business rule makes the old application unsafe.

Include a semantic inventory alongside the schema inventory. Record time zones, decimal precision, collation, identifier generation, empty-string handling, uniqueness rules and deletion meaning for affected fields. An engine conversion can preserve every row while changing sort order or whether two customer identifiers compare as equal. Test the behavior the application depends on, including rejected inputs and historical values that current validation would no longer allow.

The application owner selects representative operations across that matrix. Run an old client against the expanded schema, a new client against partially backfilled data, and the intended fallback against data written by the new version. Include a delayed worker carrying an older payload. If a combination is deliberately unsupported, the release mechanism must prevent it rather than relying on everyone remembering the exception.

Choose the least complex migration that meets the constraint

| Option | Useful when | Additional obligation | Reject or revise when | | --- | --- | --- | --- | | Bounded maintenance and copy | The business accepts an explicit interruption and the copy can be rehearsed | Communicate the pause, protect accepted work, verify recovery | The measured copy or recovery exceeds the accepted window | | Expand and contract in one database | Both representations can coexist under one transaction boundary | Maintain compatibility and retire old readers deliberately | The transformation loses information needed by old clients | | Snapshot plus change capture | Writes must continue while a separate target is populated | Coordinate capture, replay, retention and authority transfer | A capture gap cannot be detected or recovered | | Application-managed propagation | Business events express the required transition semantics | Own durable intent, retries, duplicate handling and reconciliation | Untracked writers bypass the propagation path |

This comparison is a proposed decision aid, not a ranking of technologies. Record the rejected alternatives and the requirement that ruled each out. If continuous migration requires an untested reverse path and an owner who is unavailable during cutover, a rehearsed maintenance window may be the more defensible choice.

3. Use evidence gates, not a date-only sequence

4. Check PostgreSQL DDL by exact operation

A scan, a rewrite and a lock are different hazards. A command that avoids rewriting a table can still wait for a lock and block other work.

For PostgreSQL 17, the following distinctions matter. Review the complete command and all combined subcommands against the official documentation, not just the headline label. PostgreSQL 17 ALTER TABLE.

| Proposed change | Version-specific distinction | Rehearsal question | | --- | --- | --- | | Add column with default | A nonvolatile default can be represented without rewriting existing rows; volatile defaults can require rewriting | What expression is used, and how long does lock acquisition wait? | | Set NOT NULL | Existing rows generally need validation; a suitable validated CHECK can avoid the scan | Is the proving constraint valid, and what lock is required? | | Add a constraint without immediate validation | NOT VALID is available for specific constraint types, not every constraint | Does the exact constraint support it, and how will validation affect load? | | Combine changes | The strictest required lock can govern the combined command | Should independently safe operations be rehearsed separately? |

Set an approved lock-wait limit and statement budget for the rehearsal. Inspect long-running transactions, blocking sessions, disk headroom and replica lag before proceeding. Do not automatically terminate another session to make a migration fit its schedule.

A concurrent index build has its own constraints: it cannot run inside a transaction block, performs additional work, and failure can leave an invalid index requiring inspection. Uniqueness enforcement and retry implications need particular care. Do not blindly repeat or delete indexes after an error. PostgreSQL 17 CREATE INDEX.

5. Separate atomic writes from asynchronous propagation

There are two different designs, and their failure contracts must not be mixed.

Same-database transaction: the application changes both representations inside one transaction. If a statement puts that PostgreSQL transaction into an aborted state, the application cannot simply ignore the error and commit the earlier work. Recovering within the transaction requires an appropriate savepoint strategy; otherwise roll back and retry or fail. A savepoint does not make an external queue write atomic. PostgreSQL 17 transactions.

Source plus outbox or change capture: the source commits authoritative data and durable propagation intent, or a supported change-capture mechanism observes the committed change. A separate worker updates the target. Lag and duplicate delivery are part of the design. If the target is unavailable, the source may continue only within the approved backlog, retention and recovery limits.

Never label a best-effort write to a separate queue as durable merely because the primary database committed. Identify the exact atomic boundary. The transactional outbox pattern addresses source-side intent; target-side application still needs idempotency and reconciliation.

Describe where progress becomes durable. If the worker records its checkpoint before the target commits, a crash can make unprocessed work appear complete. If it records progress afterward, a crash in between can repeat committed work. The latter still needs a deliberate duplicate strategy; it is not permission to acknowledge every received event immediately. The database owner should be able to point to the transaction that protects target data and any associated progress metadata.

Separate database propagation from external effects. Replaying a migrated order must not automatically charge a customer or resend a shipment request. Either suppress those effects in the migration path or resolve them through the original business-operation identity and an authorized reconciliation process. A row-level version check cannot determine whether an external payment already completed.

6. Protect backfill from newer updates and deletes

A backfill reads old data while the application may create new versions. A stale snapshot must not overwrite a newer target update. A deletion must not be undone when an old row arrives late.

Design snapshot and change-capture coordination together. Record the snapshot boundary, capture position, retention assumptions and restart process. PostgreSQL logical decoding documents snapshot coordination and the possibility of repeated changes after a crash; consumers must account for repetition. PostgreSQL 17 logical decoding concepts.

The following model assumes a strictly increasing version per record, full-row events, one authoritative writer and retained deletion tombstones. Those assumptions are design inputs, not properties supplied by arbitrary timestamps or CDC tools. Equal versions with conflicting payloads are an error.

~~~js

const target = new Map();

assert.equal(apply(update), "applied"); assert.equal(apply(snapshot), "stale"); assert.equal(target.get("A").value, "new"); assert.equal(apply(deletion), "applied"); assert.equal(apply(update), "stale"); assert.equal(target.get("A").deleted, true); assert.equal(apply(deletion), "duplicate"); assert.throws(() => apply({ ...deletion, deleted: false, value: "conflict" }), /version conflict/); ~~~

This dependency-free Node.js fixture demonstrates one stale-overwrite and deletion-resurrection defense. It does not provide a database transaction, crash durability or safe concurrent execution. A real implementation needs atomic conditional writes, durable version metadata and a policy for version generation, failover and tombstone retention.

If the target stores partial updates, relational dependencies or ordered side effects, this full-row model is insufficient. Multi-record invariants may require transaction grouping or additional reconciliation. Reject the model when its assumptions do not match the source instead of treating a passing test as permission to deploy.

7. Build a database-specific crash rehearsal

Use an isolated PostgreSQL 17 environment with the intended driver and transaction settings. Record schema, engine patch release, configuration, commands, failpoint and observed durable state. The checks below are an execution specification, not a claim that this paper ran an engine-level test.

| Rehearsal | Expected evidence | Unsafe interpretation to reject | | --- | --- | --- | | Secondary statement fails inside one transaction | Entire unit is rolled back unless a deliberately scoped savepoint path is tested | Primary success alone means the transaction committed | | Process ends before commit | A new connection observes no partial application state | In-memory success proves durability | | Process ends after commit but before acknowledgement | Stable identity resolves to the committed result | Timeout means retry with a new identity | | Relay stops after target commit but before checkpoint | Repetition leaves target state unchanged | Every delivery is unique | | Backfill races with update and delete | Conditional apply preserves newer version and tombstone | Last arrival is necessarily latest business state | | Disk or replication pressure exceeds the agreed bound | Work pauses safely and resumes from a durable checkpoint | Catch-up will be automatic regardless of retention |

Keep batches short enough to limit transaction duration and contention. Acquire locks in a consistent order where the application controls that order. Test the operator's stop and resume procedure, not just the happy-path script. Production evidence must include failures that exercise the actual database boundaries.

8. Reconcile business meaning and declare sample coverage

Row counts alone cannot detect every wrong value. Compare key coverage, duplicates, nullability, transformed values and business invariants. For a ledger, balanced totals can still conceal offsetting errors, so combine aggregate checks with record-level checks appropriate to the risk.

Use a consistent comparison boundary or account explicitly for in-flight changes. Otherwise a live source and lagging target can produce false differences. Record capture position, comparison time, excluded fields and transformations.

A sample in which every checked record matches is not exhaustive integrity evidence. State the sample selection method, number examined, population, known exclusions and what failures the sample could miss. Do not assign a statistical confidence level without an appropriate sampling model.

Classify discrepancies as expected lag, transformation differences, missing records, duplicates or unresolved conflicts. Every class needs an owner and closure criterion. Do not change the reconciliation rule merely to make the dashboard green.

Keep the comparison reproducible. The evidence record identifies query or tool revision, normalization rules, input boundary, result location and reviewer. If a hash comparison ignores a field, explain why that field is excluded and which separate check covers it. A migration identifier and capture position are more useful than a screenshot with no way to identify the underlying population.

Resolve differences through the authoritative source and approved transformation. Do not repair the target by choosing whichever value makes the comparison pass. For an unexpected missing order, trace its source commit, capture event, application outcome and checkpoint. That investigation distinguishes delayed processing from a skipped event, a transformation rejection or an incorrect comparison boundary. Each cause has a different recovery action.

9. Transfer write authority explicitly

In a cross-store migration, identify which system is authoritative for each record during coexistence. Avoid two unconstrained writers unless conflict resolution is a deliberate, tested product requirement.

Before moving writes, drain or account for in-flight work and establish a verified capture position. Decide whether old clients are fenced, redirected or rejected. DNS changes alone may not move pooled connections or background workers.

A brief write pause can simplify this boundary, but it is a pause and must be reflected in the availability statement. A queue can alter the customer impact only if its semantics and limits are tested. Define how support identifies submissions that were accepted but not completed.

Once the new store accepts writes, the old store may be stale. Switching traffic back is safe only if the old system has the required data and can interpret its semantics. Reverse synchronization, conflict handling and authority transfer must be tested before calling that a rollback option.

10. Define the point of no return

| Phase | Potential reversal | Evidence required | | --- | --- | --- | | Expanded schema, old behavior retained | Stop new reads or deployment | Old application still accepts all stored values | | Backfill in progress | Stop worker and retain source authority | Durable checkpoint and no irreversible side effects | | New reads, compatible old writes retained | Route reads back | Old representation remains current and correct | | New store owns writes | Transfer authority back only after reconciliation | Tested reverse synchronization and compatible semantics | | Old data or schema removed | Forward repair or recovery from retained evidence | Restore procedure, recovery-point limit and replay plan |

Re-expanding a dropped column only creates schema. It does not recover the lost values, original ordering or application interpretation. A backup may restore an earlier state but omit later accepted writes. Define how those writes are replayed or how the loss is handled before approving contraction.

Separate the decision to stop maintaining the old path from the decision to delete its data. Retention, privacy and operational obligations can differ. Obtain the relevant owners' approval for both.

11. Use a runbook with observable stop conditions

A migration runbook should name the operator, decision owner, communications lead, artifact versions and approval window. Every phase needs an entry condition, command or procedure, observable exit condition and recovery action.

Include lock wait, application latency, error classification, replication lag, backlog age, disk growth and reconciliation differences. Set thresholds from the actual workload and recovery capacity. This paper supplies no universal batch size or acceptable lag.

Record the last safe state before each authority change. A rollback command without data prerequisites is not a recovery plan. Confirm that credentials, keys, backups and external dependencies are available to the person expected to execute recovery.

Rehearse the handoff between database and application operators. The person watching infrastructure may not see a customer-visible semantic error. A product-level observer should verify the critical operations throughout the change.

Use explicit commands for pause, resume and abandonment, with their prerequisites recorded. A pause should stop new batches without leaving the operator uncertain about the active transaction. A resume should read durable progress and reconcile the last uncertain unit. Abandonment should preserve the source of truth and evidence while disabling the paths that could continue writing unexpectedly. These procedures need separate tests because each leaves a different operating state.

The communications lead maintains a short status record: current authority, accepted-but-uncompleted work, customer impact, next decision and responsible owner. Avoid announcing completion when only bulk copy has finished. Support needs a way to resolve an individual customer's operation, including whether retry is safe and whether the new or old system holds the result.

12. Coordinate snapshot, capture and target progress

The diagram separates the initial copy from later committed changes. Both enter a controlled apply boundary; the target's durable result determines when progress can advance. The layout describes a custom version-aware application model like the fixture above, not a claim that every replication product exposes these exact components.

Capture positions and business versions answer different questions. A stream position identifies progress through captured changes; a per-record version can help reject an older state. Neither should be substituted for the other without a proven mapping. Across partitions or multiple sources, one scalar position may not describe a consistent business boundary. Record the relevant positions and transaction grouping in the evidence packet.

The migration owner must prove there is no unobserved interval between the snapshot and the change stream. A tool's initial-copy feature may coordinate this internally, while a custom implementation must establish it explicitly. Record the mechanism, the retained starting position and the recovery procedure if the snapshot fails. Starting capture after an arbitrary export finishes can leave writes missing from both inputs.

Check what the selected replication mechanism does not copy. PostgreSQL 17 built-in logical replication does not propagate DDL or sequence state, and it does not replicate large objects. A target intended to accept writes therefore needs a separate schema and identifier-generation plan, with tests for the objects actually used. These restrictions are specific to the documented mechanism, not a description of every CDC product. PostgreSQL 17 logical replication restrictions.

Treat a capture gap as a change in the recovery plan. Stop the authority switch, preserve the last known applied position, and determine whether retained source history can fill the interval. If it cannot, a new baseline and reconciliation may be required. Continuing from the latest available event would conceal the missing interval. Retrying the worker is useful only when the evidence shows that the required history still exists.

13. Bound migration privileges and sensitive copies

Operational and security consequences meet at the migration account. An account capable of reading every tenant and rewriting the target may exceed normal application authority. The security owner records why each capability is required, where it is usable, how its actions are logged, and when it will be removed. Application traffic should not inherit that role after cutover because it happened to work during rehearsal.

Use the actual replication security model. PostgreSQL 17 documents privileges for the replication connection, initial table copy and subscription apply process. It also warns that publication filtering is not a complete subscriber access boundary: another publication can expose data from the same database. Review the connection and database-wide exposure with the security owner rather than treating a selected table list as an isolation guarantee. PostgreSQL 17 logical replication security.

Inventory temporary exports, staging tables, error queues and comparison results. They can contain data that ordinary users cannot retrieve through the application. Use approved storage, encryption, access boundaries and retention rules. A rejected-record file deserves the same handling review as the table from which its records came. Redact operational logs deliberately; avoid recording raw credentials or full customer rows as routine troubleshooting output.

Test the target as the real application role, not only as the migration administrator. Verify that another tenant's identifier is denied, a revoked user remains denied, and administrative scripts cannot cross their intended scope. Include restored data and delayed change events in that test because recovery must not reintroduce access that was removed during migration.

The security closeout record lists temporary identities, privileges, secrets, exports and network rules. Remove them only after the recovery owner confirms which paths still need them. If a rollback depends on a credential that will be revoked, replace that dependency before claiming the rollback remains available. Legal or regulatory retention and deletion applicability requires qualified review; an engineering runbook can demonstrate handling behavior but cannot establish compliance on its own.

14. Budget capacity and coexistence before increasing throughput

Backfill competes with ordinary work for I/O, CPU, connections, locks and replication capacity. The operator should start from a representative rehearsal and change one control at a time: batch size, worker concurrency or pause interval. Record the resulting application latency, completed work, backlog age and resource pressure. Faster copying is not progress if it makes customer transactions fail or leaves the target farther behind incoming changes.

Separate the bulk-copy backlog from the change-application backlog. Finishing the former can leave a large amount of newer work outstanding. Measure whether the apply path can reduce that backlog under representative ongoing write load and recovery conditions. A one-time throughput measurement during a quiet period is insufficient for approving cutover during a busier period.

Replication slots and retained history also need an owner. PostgreSQL's logical-decoding documentation explains that slots retain required resources even without an active consumer. Monitor retained data and the conditions that make the stream unusable. Do not drop or advance a migration slot merely to clear a disk alert without first recording the effect on recoverability. The operator may need to stop the migration and protect the source while a new baseline is planned.

Build the cost estimate from identified resources and duration assumptions. Include two running databases, transfer, temporary storage, backups, extra telemetry, reconciliation work and support coverage. Record what keeps coexistence running longer, such as an unresolved export consumer or an unavailable recovery reviewer. Avoid an invented universal payback period; the decision should compare the actual constraint and credible operating alternatives.

The completion gate needs a decommissioning owner. An abandoned target, forgotten replica or retained export can continue consuming money and exposing data after the visible migration work ends. Closing the migration means either removing those resources under the approved retention plan or explicitly transferring them to ordinary service ownership.

15. Rehearse recovery after the new system accepts writes

A pre-cutover restore proves only part of the recovery story. Once the new system accepts writes, the team must account for those operations before returning to an older representation. The database and application owners jointly choose a recovery strategy: reverse propagation, compatible replay, forward repair, or restoration with an explicitly accepted recovery-point limit. These are alternatives with different prerequisites, not interchangeable rollback commands.

For PostgreSQL point-in-time recovery, a usable base backup and the required continuous WAL history are part of the recovery chain. The restore target and available history constrain the result. Verify that chain in an isolated environment instead of relying on a backup job's success status. A successful restore still needs application-level checks before it can be used as a serving system. PostgreSQL 17 continuous archiving and point-in-time recovery.

Build a post-cutover exercise around a known set of accepted operations. Include one new record, an update, a deletion and an operation with an external effect. Introduce the approved failure, execute the recovery procedure, then reconcile those operations against the recovered state. Record which identifiers were replayed, which were compensated, and which could not be recovered. This evidence makes the recovery-point claim testable.

Do not restore over the only available evidence of newer accepted work. Preserve the target state and relevant logs under the incident plan while deciding how to repair. A hurried overwrite can turn an application outage into permanent information loss. The incident authority should understand the difference between containing new writes and choosing the final recovered state.

The recovery rehearsal ends when an authorized operator other than the runbook author can identify the current authority, resolve uncertain transactions, restore the required behavior and explain any remaining loss. Record elapsed time and customer impact as observations from that environment. They are useful planning inputs, not guaranteed production outcomes.

16. Use a cutover decision record and closure checklist

Before the cutover meeting, the migration lead assembles a compact record that points to the detailed evidence. It should be possible to discover a missing prerequisite without searching through deployment chat history.

| Record field | Required evidence or decision | | --- | --- | | Scope and versions | Source, target, driver, application revisions and excluded consumers | | Current authority | Writer identities, routing rules, in-flight work and fencing test | | Copy and capture boundary | Snapshot identity, capture positions, retained history and gaps | | Compatibility | Tested client/schema combinations and explicitly blocked combinations | | Reconciliation | Population, rules, unresolved differences, reviewer and result location | | Recovery | Last reversible state, newer-write handling and independently executed rehearsal | | Security | Target application-role tests, temporary access and sensitive-copy inventory | | Decision | Named accountable roles, accepted conditions, stop criteria and next review |

During the meeting, ask the application owner to explain a timed-out request, the data owner to explain a mismatched record, and the operator to demonstrate where they would stop the next phase. If the answers depend on undocumented assumptions, defer that phase and repair the runbook. A signed checklist should summarize evidence, not substitute for it.

Use this closure checklist after the observation period agreed for the workload:

Critical user operations meet their defined behavior and availability conditions.
Old writers are fenced or intentionally supported, including scheduled and recovery jobs.
Capture and reconciliation have no unexplained gaps within the declared coverage.
Accepted post-cutover writes have a tested recovery path or an explicitly accepted limit.
Sequence, schema and non-table objects required by the target have been checked separately.
Temporary access and data copies have approved removal or retention decisions.
Monitoring and support now identify the new authority and its failure modes.
Contracting the old schema has its own approval, retained-evidence plan and owner.
Operating ownership, recurring cost and outstanding defects have been handed over.

Choose the next action from the unresolved evidence. A compatibility failure calls for an application change and repeat test. An unknown capture interval calls for recovery or reseeding. A missing approval calls for the accountable decision, not another copy run. Only schedule the next irreversible phase when its specific prerequisites are satisfied.

Limitations, evidence and next step

Primary references were checked on 21 September 2026. The paper contains no authenticated migration dataset, incident analysis, cost-of-downtime average or success-rate claim. PostgreSQL 17 behavior must be rechecked for the actual target release and managed-service configuration.

The JavaScript model is limited evidence about ordering logic. Engine-level crash tests, representative-volume rehearsals, cutover measurements and authorized recovery acceptance remain required. An accountable author and database reviewer have not been assigned, so factual and publication approval remain withheld. No unverified PDF is presented as a validated runbook.

For a scoped cloud migration and modernization review, bring the compatibility matrix, write-authority map, recovery requirement and a representative reconciliation sample. The first decision is whether the proposed migration and its recovery path are testable within those constraints.