Moving a database looks straightforward when nobody is using it. Stop the application, copy the data, change a connection string and start again. The copy represents a world that stayed still while the work happened.
A live application changes that world continuously. Customers create orders, workers update delivery information and support staff correct addresses while the copy is running. A successful migration must carry those changes across and decide exactly when the new database becomes responsible for accepting writes.
Introduction
Imagine a small booking application whose customers share one database server. A growing customer, Harbour Club, now needs to move to a separate server. Other customers should continue using the original database, and Harbour Club should experience at most a short, controlled interruption to changes.
We will call the original database the source and the new database the destination. The objective is to move Harbour Club's records without losing a confirmed booking, bringing back a cancelled booking or allowing both databases to accept competing edits after the move.
This is a data placement change: the records move to a different server. The same reasoning applies when splitting a large database into smaller pieces or replacing an ageing server. Changing column names and application schemas introduces additional concerns, so keeping the schema compatible during this move makes the problem easier to control.
The main stages are copying a starting state, following subsequent changes, checking the destination and transferring responsibility. The difficult part is the boundary between those stages. A progress bar reaching 100% proves that a copying task finished; it does not prove that every live change arrived or that old application instances stopped writing to the source.
Decide what moves together
Before copying anything, identify the complete set of records that belongs to Harbour Club. Bookings may refer to members, payments, schedules and attachments. Moving only the bookings table could leave records whose related information remains on another server.
Draw a small dependency map. For each table, record how ownership is identified, whether another customer's records can reference it and whether transactions update it alongside a booking. Include background jobs and administrative tools. A nightly reporting task with its own connection string is still a database client.
Suppose a booking and its seat reservation must commit together. Moving those records to different servers could turn an ordinary local transaction into a cross-server coordination problem. Keeping them in the same migration unit preserves the existing guarantee. Shared reference data, such as country codes, may instead be copied to both servers if its update process supports that arrangement.
The application needs an explicit way to find a customer's current database. A routing record might initially say:
customer: harbour-club
database: source
generation: 7
state: active
The generation is a version of the routing decision. It will help distinguish an old request from a request using the current placement. A routing table alone does not enforce ownership, but it gives the migration a named decision that can be inspected and changed deliberately.
Why an ordinary copy misses changes
Consider copying bookings in batches ordered by booking ID. The process copies booking 104, then continues with later IDs. A customer subsequently changes booking 104 from Tuesday to Wednesday. Unless another mechanism carries that update, the destination permanently retains Tuesday.
Scanning by an updated_at timestamp does not automatically solve the problem. A transaction might start before the scan boundary and commit afterwards. Clocks can disagree, timestamps can have limited precision and a deletion may leave no row to scan. The exact failure depends on how the application assigns timestamps, but an unexplained time filter is not a reliable migration protocol.
A destination can also become internally inconsistent. The copier reads a booking before a transaction updates it, then reads its reservation after that transaction commits. Both copied rows existed on the source, but they did not necessarily represent one valid database state together.
What we need is a supported relationship between a starting snapshot and the stream of changes that follows it. A snapshot is a database view representing a particular consistent state. A change stream records committed modifications after an identified position. Together, they allow the destination to move from a known starting state towards the live source without leaving an unobserved gap.
Copy a snapshot and capture the changes around it
Databases commonly record changes in a transaction log for recovery. Change data capture, usually shortened to CDC, exposes relevant changes in a form that another system can consume. The stream can include inserts, updates and deletes, along with positions that identify progress.
Use the database or migration tool's documented bootstrap procedure to connect the initial copy to that stream. In PostgreSQL, for example, the replication protocol can create a logical replication slot and export a snapshot with a defined relationship to subsequent changes. The logical decoding concepts documentation explains this relationship. It is a database feature to use correctly, not something to reproduce by recording the current time before a script starts.
Conceptually, the migration establishes a boundary called position P. It copies the snapshot associated with P and retains the changes needed to advance beyond that snapshot. While the snapshot is copying, the source continues accepting normal application transactions.
For booking 104, the snapshot might contain Tuesday. The captured update later changes it to Wednesday. If booking 105 is created after the boundary, the stream inserts it. If booking 106 is cancelled by deleting its row, the stream must carry that deletion as well.
Real tools differ in their snapshot and buffering behaviour. Follow the guarantees and restrictions of the selected source, target and task mode. AWS documents its full-load and ongoing-change stages in the high-level view of Database Migration Service. The useful general idea is the combination of initial state and subsequent changes; the exact implementation is product-specific.
Keep one database responsible for application writes
During the copy, the source remains the only database that accepts Harbour Club's business writes. The destination receives changes from the migration process. Ordinary application requests should not independently update it yet.
This arrangement avoids a tempting but dangerous shortcut: making every application operation write to both databases. If the source write succeeds and the destination write times out, the application must decide what to report and how to repair the difference. Retrying can introduce duplicates, and a later request may observe whichever database it happens to contact.
A migration tool still has to handle delivery failures, but it can use the source's committed history as the authority. It is reproducing decisions already made, rather than asking two independent databases to make the same decision simultaneously.
Keep destination access limited to the replication process and carefully controlled validation clients. A support script that edits the destination can create a difference which the source stream later overwrites. The migration needs an explicit ownership rule that applies to people, scripts and workers as well as the main web application.
If users need to test reads against the destination, make those reads observational. They must not trigger reminder emails, consume bookings or update last-access fields. A supposedly harmless comparison endpoint can otherwise become an unexpected destination writer.
Apply changes without duplicating or reordering their meaning
A migration worker can crash after applying a change but before recording that it finished. On restart, it may receive the same change again. The application process must tolerate that possibility using the guarantees of its replication system or a carefully designed application protocol.
For a custom projection, recording the source change position in the same destination transaction as the applied data can make recovery precise. Another approach uses per-record versions so that an older change cannot replace a newer record. These are design patterns, not interchangeable settings: they must match the source ordering and transaction semantics.
Suppose booking 104 changes from Tuesday to Wednesday and then to Thursday. Reapplying the final state Thursday may be harmless. Applying Wednesday after Thursday is not. A stream split into parallel workers must preserve the order required for each booking, and related records may need transaction boundaries preserved as well.
Deletes deserve explicit treatment. If a deleted booking simply disappears from an initial copy while a delayed update later recreates it, the destination becomes wrong. The migration must apply the complete change history according to its ordering rules, including removal information, rather than treating every received row as an unconditional upsert.
Do not invent a universal SQL consumer around these examples. Database-native replication and mature migration tools already implement important parts of this work. Check whether their guarantees cover primary keys, large values, transaction boundaries, retries and the particular transformations enabled in the task.
Make copying slower than your customers' tolerance
Copying is real production load. It reads source storage, uses network capacity, writes destination storage and may compete with backups or routine maintenance. Increasing parallelism can make the copy faster while making the application unusable.
Begin with a conservative rate and observe source query latency, storage pressure and error rates. The destination needs capacity for the initial load, index maintenance and incoming live changes. A small destination that is adequate for normal traffic may struggle while all three happen together.
There are two progress questions. How much of the starting snapshot remains, and how far behind is the destination's change application? A snapshot can finish while a growing change backlog makes cutover increasingly distant.
For a simplified calculation, suppose changes arrive at 2,000 records per second and the destination can apply 3,000 records per second after the copy finishes. It has room to reduce the backlog by approximately 1,000 records per second, ignoring variation and transaction overhead. If application capacity is only 1,500 records per second, waiting longer will not produce convergence under that workload.
Log retention is another limit. The source must retain the history that replication still needs. A stalled consumer can increase retained storage, or a configured retention limit can remove history and make a restart require a fresh copy. Monitor remaining retention capacity alongside lag; a paused migration is not automatically a harmless migration.
Validate more than the number of rows
Matching row counts are useful, but two tables with 100,000 rows can contain different bookings. Compare record identities, important values and relationships as well as totals. Run checks in manageable ranges so a mismatch can be located rather than producing one enormous unexplained failure.
For Harbour Club, useful checks include bookings per date, reservations per booking, cancelled booking absence and payment references. Sample ordinary journeys such as opening a member profile and finding the associated future bookings. Business checks catch mistakes that a raw table comparison can overlook.
Comparisons must account for ongoing writes. Reading the source at one moment and the destination a moment later can report a legitimate in-flight difference. Validate at a shared consistent boundary when the tools support it, or classify temporary differences and repeat them after the destination passes the relevant source position.
A shadow read sends a copy of a real read to the destination and compares the result without showing it to the user. This can reveal missing indexes, different collation behaviour or query assumptions. Limit shadow traffic so verification does not overload the system it is meant to protect.
Define acceptable results before the switch. A migration with three unexplained missing bookings is not ready because its percentage match rounds to 100%. Any tolerated difference needs an understood cause and an appropriate business decision, especially for money, permissions and reservations.
Check the database objects that row copying does not cover
Data migration is not always a complete database clone. Tables may arrive without all indexes, constraints, triggers, permissions or scheduled database jobs. Some objects are intentionally excluded by the selected replication mechanism.
PostgreSQL's logical replication restrictions provide a concrete example: schema changes and sequence state require separate consideration. A destination can contain the copied identity values while its sequence still generates a conflicting next value after promotion.
Prepare a destination readiness list based on the actual engine and tool. It should include schema compatibility, generated identifier allocation, application credentials, encryption settings, backups and the queries that must perform acceptably immediately after cutover.
Triggers deserve particular care. A replicated row change must not unexpectedly send the same notification that the source already sent. Conversely, a disabled trigger that maintains an essential destination field may need a documented replacement. Understand which behaviour occurs on replication writes and which occurs on normal application writes.
Rehearse starting the application against a disposable migrated copy. A successful connection test cannot reveal an absent stored procedure that appears only when a customer cancels a booking. Representative operations are a much better readiness check than a green database status icon.
Transfer write ownership at a controlled boundary
Once the destination is nearly caught up and validation passes, the migration needs a cutover: the point at which the destination becomes authoritative. The simplest safe design often includes a short pause for writes to the moving customer. The rest of the application can continue operating.
First, close admission for new Harbour Club writes and drain or terminate existing write transactions according to a documented policy. Merely changing a web server flag is insufficient if background workers and old application instances can still reach the source.
Next, establish that the source can no longer commit a new Harbour Club business write under the old ownership. This enforcement is often called fencing. It might be implemented by a database-supported migration system, a mandatory routing layer or a transactional ownership check. A cached flag that an isolated writer can ignore does not provide that guarantee.
Record the final source change position after the old writes are accounted for. Wait until the destination has durably applied through that position. Then activate destination ownership, advance the routing generation and allow writes to resume there.
Copying: source accepts writes; destination follows
Draining: new source writes stop; existing writes settle
Caught up: destination has applied the final source position
Transferred: destination owns generation 8
Serving: application writes resume at the destination
These are conceptual states, not a ready-made script. The exact ordering and enforcement must be supported by the migration technology. The essential property is that no interval permits two independent authorities to accept conflicting business writes.
Handle requests that still know the old address
Routing changes do not reach every client instantly. An application instance may cache generation 7, a connection pool may contain old connections and a worker may have loaded a job before cutover began. Those requests will continue trying the source for a while.
The old location must reject, redirect or safely forward them according to the chosen routing protocol. For a write, rejecting an obsolete generation is often easier to reason about than silently accepting it. The client refreshes placement and retries with the same operation identity so a lost response does not create a second booking.
Forwarding can reduce visible interruptions, but it adds its own rules. Prevent forwarding loops, preserve authentication and customer identity, and ensure the source does not partially execute the operation before forwarding it. A database connection cannot generally turn an arbitrary transaction into a transparent HTTP redirect.
Reads also need a policy. A stale read from the old source may hide a booking just created on the destination. Route reads requiring current customer state to the new owner, and avoid caches keyed only by a server-local identifier that changes meaning after the move.
Keep metrics for obsolete-route rejections. A long tail can reveal a forgotten worker or an administrative integration. Waiting an arbitrary ten minutes and assuming all clients updated is weaker evidence than identifying the remaining callers and confirming the ownership rule protects them.
Treat rollback as another data movement problem
Before destination writes begin, stopping the migration is relatively straightforward: keep serving the authoritative source and investigate the destination. Once the destination accepts new writes, the source becomes stale unless changes flow back.
Suppose a booking is created on the destination after cutover. Flipping the connection string back to the source would make that confirmed booking disappear. Retaining the source database does not, by itself, make rollback safe.
Some migration systems support reverse replication. The Vitess Reshard reference describes traffic switching and reverse replication capabilities for its documented version. Such facilities can provide a controlled route back, but their prerequisites and completion rules still matter.
A rollback after new writes therefore needs the same core questions as the forward move: which side owns writes, which changes are missing, how will they be copied, and how will old writers be fenced? If reverse replication is not available, the recovery plan may be to fix the destination while continuing to serve it.
Write this decision before cutover. Teams under pressure are prone to treating the old database as a clean escape hatch. An explicit point after which recovery means repairing forward prevents a fast-looking response from creating silent data loss.
Rehearse the failures between the happy steps
Use a test customer with a known set of bookings and a repeatable workload. Include creates, updates, cancellations and transactions that touch several related rows. Record accepted operation identifiers so the final state can be checked against what the application promised.
Stop the copy worker halfway through a batch and verify that restarting does not omit or duplicate data. Delay the change stream and confirm that lag grows visibly, cutover is blocked and retained-log capacity remains observable. Introduce an incompatible destination schema and confirm that a replication error cannot be mistaken for completion.
The most valuable cutover test keeps an old application instance running with the old route. Attempt a write after ownership transfers. The test passes when the obsolete writer is rejected or redirected safely, not merely when the newest application instance reaches the correct database.
Also interrupt the cutover controller between its recorded stages. Recovery should inspect durable migration state and continue or stop safely. It should not infer ownership from whichever database happens to respond first. A controller restart is a normal operational event and must not create a second source of truth.
Finally, exercise the chosen recovery direction after a destination write. Demonstrate either a supported reverse migration or a repair-forward procedure. This makes the practical cost of a late rollback visible before a real customer is waiting.
A worked cutover with an uncertain booking response
Consider a customer creating booking 207 just as Harbour Club enters the draining state. The source commits the booking, but the connection drops before the application receives the result. The customer sees a spinner and presses the button again after routing has switched.
The migration must carry both the booking and the durable information used to recognise that operation's retry. If an idempotency record lives in a separate table that was excluded from the move, the destination may treat the second request as a new booking. Correctly copying the business table would still leave an incorrect user journey.
With the operation record included, the destination can recognise the retried request and return the existing booking. This depends on the final cutover position including the committed source transaction and on the destination making that record available before accepting the retry.
Now change the example: the original source transaction never committed. Once the source is fenced and the destination owns the customer, the retried operation can create the booking there. The response may be delayed during the move, but the ownership boundary and operation identity give the application a clear way to settle the uncertainty.
This small example connects infrastructure correctness with customer-visible behaviour. Migration verification should test lost responses and retries as well as successful SQL statements. Users do not observe log positions; they observe whether a confirmed action survives and whether repeating an uncertain action creates an unwanted second result.
Make the migration state understandable to operators
Give the migration a durable record of its source, destination, customer, current stage and relevant replication positions. An operator should be able to distinguish copying from draining and draining from transferred without guessing from process logs. Record when each stage began and what evidence allowed the next transition.
Suppose the controller stops after fencing the source but before enabling the destination. Customers temporarily cannot write, but the system has avoided two writers. A replacement controller should discover this recorded state, verify the fence and destination progress, then finish the supported transition. Automatically reopening the source because the destination has not yet become writable could undermine a transition already in progress elsewhere.
Use explicit stop conditions. Unexpected validation differences, a lost change-stream position, insufficient destination capacity or an ownership check that cannot be enforced should prevent cutover. A scheduled maintenance window ending is not evidence that these conditions became safe.
Separate a planned abort from an emergency recovery. During copying, aborting can leave the source serving normally while temporary migration resources are cleaned up later. During ownership transfer, the same button may need to finish a carefully defined recovery procedure. Naming both actions simply "cancel" hides a difference that matters when somebody is responding under pressure.
This record need not be elaborate, but it must agree with the technology enforcing the move. A dashboard that says "complete" while a database still permits old-generation writes offers reassurance without protection. Prefer status derived from durable migration state and verified ownership checks, with links to the evidence an operator needs.
Retire the source only after the move is settled
After cutover, observe normal traffic for an agreed period. Compare application errors, query latency, destination resource usage and business totals against expected behaviour. Watch background jobs whose schedules mean they might not run during the first hour.
Keep the old copy protected from ordinary writes while it remains available for investigation or supported reverse replication. Make its status obvious to operators. A familiar server name can attract an emergency manual edit that never reaches the new authority.
Cleanup has dependencies: obsolete routes must no longer be used, the recovery window must be understood, required backups must exist and replication resources must be removed through their supported procedures. Forgotten replication slots, migration accounts and temporary copies can consume storage or retain access long after the successful switch.
Record the final placement, migration generation, completion time and any remaining follow-up work. A future incident responder should be able to tell which database owns Harbour Club without reconstructing chat messages from migration day.
Summary
A live database move combines a consistent starting copy with captured changes, validation and an enforced transfer of write ownership. The source stays authoritative while the destination catches up, and the cutover establishes a final boundary before destination writes begin.
The details that protect customers sit around that boundary: related records move together, retries retain their identity, old routes cannot keep writing and rollback accounts for changes made after the switch. Capacity limits, log retention and destination readiness make the plan operationally credible.
Start with a small migration unit and rehearse interruptions at each stage. When the copy, change stream, ownership rule and recovery direction are explicit, moving data becomes a controlled process whose correctness can be checked while the application continues serving its users.