How to migrate a production database to the cloud safely

The database is the part of a cloud migration you can’t casually retry, it holds state, it changes every second, and everyone notices when it’s wrong. Here is the method that keeps it boring.

First decision: managed or self-managed?

Before moving the database, decide what it’s moving into:

	Managed (RDS, Cloud SQL, Azure SQL)	Self-managed (database on your own VM)
Patching, backups, failover	Provider’s problem	Your problem, forever
Cost for same hardware	~20-40% premium	Cheapest raw price
Engine versions/extensions	Provider’s supported list	Anything you want
OS/filesystem-level tuning	Not available	Full control
Real monthly effort	Near zero	Several engineer-hours/month, plus 3 a.m. failovers

Default to managed. A small RDS PostgreSQL with automatic failover runs ~$120-250/month; the DBA hours it replaces cost more. Self-manage only for a concrete blocker: an unsupported extension or version, exotic tuning, or a large fleet where the ~30% premium is real money. (The managed-vs-self question is a version of the IaaS vs PaaS tradeoff.)

Second decision: dump-and-restore or continuous replication?

Two fundamentally different migration mechanics. Choose by size and downtime tolerance:

	Dump & restore	Continuous replication
How it works	Snapshot the DB, copy it, restore it, repoint the app	Tool copies data live, then streams every change (CDC) until you cut over
Downtime	The whole copy cycle, DB is frozen (or diverging) meanwhile	Minutes: stop writes, drain lag, repoint
Complexity	Low, native tools (pg_dump, mysqldump, .bak)	Moderate, a replication tool to set up and watch
Tools	Built into every engine	AWS DMS, Azure DMS, GCP Database Migration Service, or native replication
Right when	< ~50 GB and a maintenance window is acceptable	Big data, low downtime tolerance, or both

Rule of thumb: a 20 GB database with a Sunday-night window doesn’t need replication machinery. A 500 GB database behind a 24/7 product does. In between, the deciding question is “what does an hour of downtime cost us?”, if the answer is “a rough number we can live with,” dump-and-restore is simpler and has fewer moving parts.

One warning on the replication tools: they’re excellent at moving table data, and mediocre at everything else. DMS-class tools routinely skip or mangle indexes, sequences, triggers, stored procedures, and users. The standard practice is: migrate the schema natively first (e.g., pg_dump --schema-only, or the engine’s own tooling), then let the replication tool fill in data. For cross-engine moves (Oracle → PostgreSQL, SQL Server → MySQL) add a schema-conversion step and real testing time, that’s a replatform project, not a copy job.

The migration, step by step

Step 1: Baseline the source

Record what “correct” looks like while the source is still the only truth: row counts per table, checksums or aggregates on critical tables (sums of balances, max IDs, latest timestamps), database size, and peak write rate (needed to judge whether replication can keep up). Save the queries, you’ll rerun them verbatim at cutover.

Step 2: Provision the target

Create the managed instance in the VPC that your migrated app will live in, never publicly exposed. Match or slightly exceed source specs (you can right-size down later), enable automated backups before loading data, and confirm engine-version compatibility (replicating from PostgreSQL 13 to 16 works; know your pair’s rules).

Step 3: Move the schema, then the data

• Dump-and-restore path: announce the window → stop writes (maintenance mode) → dump → transfer → restore → jump to Step 5. Time it on a staging copy first so the window is a measurement, not a hope.
• Replication path: load the schema natively → start the replication task (full load, then CDC) → watch replication lag until it holds near zero. Initial load for a few hundred GB typically takes hours to a day; let CDC run for days if you like, the cutover date is now yours to choose.

Step 4: Rehearse the cutover

Do a dry run against a staging copy of the app, pointed at the target database, executing the exact runbook: stop writes, drain lag, validate, repoint, smoke-test. Rehearsal converts your cutover window from an estimate into a measurement, and finds the missed sequence or the hardcoded connection string on a Tuesday afternoon instead of during the window.

Step 5: Cut over

In the announced window (pick your lowest-traffic hour):

1. Put the application in maintenance mode / stop writes.
2. Wait for replication lag to hit zero (seconds, if it was already near zero).
3. Validate: rerun the Step-1 queries against the target. Row counts and checksums must match. On PostgreSQL, reset sequences (setval), replication tools frequently leave them behind, and stale sequences cause duplicate-key errors within minutes of go-live.
4. Repoint the application, via one config change or, better, a DNS/connection-string indirection you prepared earlier.
5. Smoke-test the paths that write: log in, create a record, run a checkout.
6. Reopen traffic. Watch error rates and latency for the first hour.

Total downtime with replication: 5-30 minutes, most of it validation. For the no-downtime variants (reverse replication, dual-write patterns), see zero-downtime migration.

Step 6: Keep the rollback warm

Do not decommission the source on migration day.

• Keep it running read-only for at least a week.
• For high-stakes systems, configure reverse replication (target → source) at cutover, so rolling back loses zero data instead of “everything since cutover.”
• Define the rollback trigger in advance, e.g., “sustained error rate above X% in the first 2 hours”, so it’s a decision made calmly beforehand, not an argument during an incident.

After a clean week: take a final snapshot of the source, archive it to cheap object storage, then decommission.

What this costs, worked

A 200 GB production PostgreSQL moving on-prem → AWS with DMS:

Item	Cost
DMS replication instance (t3.medium, ~2 weeks)	~$30
RDS target (db.m6g.large, Multi-AZ, 200 GB), ongoing	~$390/mo
Data transfer in	$0 (ingress is free on all three clouds)
Engineering: setup, rehearsal, validation, cutover	20-40 hours, the real cost

The recurring line is the one to scrutinize: right-size after a month of real metrics, and remember the same instance runs ~30-40% cheaper on a 1-year commitment, see estimating your cloud bill.

FAQ

How long does a database migration take?

The data transfer is usually the small part, 100 GB moves in hours on a decent link. The full project takes 2-6 weeks: schema conversion checks, replication setup, at least one test cutover against real data, then a production cutover window of 5-30 minutes with continuous replication (or 1-8 hours with dump-and-restore).

Should we use a managed database or run our own on cloud VMs?

Managed, unless you have a concrete blocker. RDS/Cloud SQL/Azure SQL cost roughly 20-40% more than raw VM + disk, and in exchange the provider handles patching, backups, failover, and replicas, work that otherwise consumes real engineer hours every month. Self-manage only for unsupported extensions/versions, unusual tuning needs, or strict cost constraints at large scale.

How much downtime does a database cutover need?

With continuous replication, typically 5-30 minutes: stop writes, let the replica catch up (seconds if lag was near zero), run validation, repoint the app. With dump-and-restore, downtime is the whole dump+transfer+restore+validate cycle, minutes for tiny databases, hours for large ones.

What’s the rollback plan if the migration goes wrong?

Before cutover: just stop, the source is untouched. After cutover: repoint the app back to the source database, accepting the loss of writes made since cutover unless you configured reverse replication (target back to source), which is the professional standard for high-stakes cutovers. Keep the source alive and read-only for at least a week; never decommission on migration day.

---

Moving a database the business can’t afford to lose? Talk to a Webisoft cloud engineer, we’ll review your replication plan, rehearse the cutover with you, or run the whole migration. The first conversation is free and specific.