Database Migration Strategies

Downtime is the axis everything turns on

There is no best way to move a database. There is a spectrum, and your position on it is set by one question: how long can writes stop?

At one end, a dump and restore is simple, cheap and needs no special infrastructure, and it stops the application for as long as the copy takes. At the other, streaming replication keeps the destination continuously current and reduces the interruption to seconds, at the cost of setup complexity and a longer project.

Answer the downtime question with the business before choosing a technique. Teams routinely build sophisticated replication pipelines for systems whose users would happily accept a Sunday morning outage, and occasionally attempt a simple dump on a system that cannot stop at all.

The second question, close behind, is whether the engine changes. Moving PostgreSQL to PostgreSQL is a logistics exercise. Moving PostgreSQL to a different engine is a software project involving data types, SQL dialect, stored procedures and application code.

The strategies in order of ambition

Move down the table only when the row above genuinely cannot meet the requirement. Each step adds components that can fail during the window, and the failure modes get harder to diagnose under time pressure.

PostgreSQL in practice

For a straightforward move, pg_dump in custom format with pg_restore on the destination handles most databases, and parallel jobs speed up both ends considerably on multi-core machines.

For larger datasets on the same major version, pg_basebackup takes a physical copy and streaming replication keeps it current until you promote the standby. This is the classic low-downtime path when nothing else is changing.

Across major versions or between providers, logical replication is the tool of choice. Create a publication on the source, a subscription on the destination, let the initial copy complete, then wait for the replication lag to reach zero before switching. Watch for the known gaps: sequences are not advanced automatically, and large objects and some DDL changes are not replicated.

Managed platforms wrap these primitives. AWS Database Migration Service, Azure Database Migration Service and Google Cloud Database Migration Service all handle the orchestration, and the last is built directly on PostgreSQL logical replication for homogeneous moves. The broader decision about running your own instance is covered in managed databases versus self-hosted, and running the engine yourself is covered in running PostgreSQL on a cloud server.

MySQL and MariaDB in practice

The same ladder exists with different tools. A mysqldump is the simple option and becomes slow on large datasets; mydumper and myloader parallelise the same job and are worth reaching for once the database is more than a few gigabytes.

Percona XtraBackup takes a physical, non-blocking copy that can be restored and then caught up using the binary log, which is the equivalent of the PostgreSQL physical path.

For minimal downtime, binary log replication from source to destination is the established approach. Record the log position at the moment the backup was taken, start replication from there, and promote when the replica has caught up. Global transaction identifiers make this considerably less error prone than tracking file and position by hand.

Check the storage engine and character set on both sides before starting. Older tables using MyISAM behave differently under backup tools, and a collation difference between source and destination is a quiet way to change how your queries sort.

Where the destination platform is still open, the managed database offerings differ more than the marketing suggests, particularly around version support, replication features and maintenance windows. The AWS and Oracle Cloud comparison illustrates how differently two platforms can approach the same database problem.

Proving the copy is correct

A restore that completes without error is not a validated migration. Build the verification into the plan as a named step with a named owner.

• Compare row counts table by table, not just the total, and investigate any difference rather than explaining it away.
• Checksum a sample of tables on both sides, or use an engine-specific comparison tool where one exists.
• Verify schema objects that replication commonly misses: sequences and their current values, indexes, constraints, triggers, views and stored routines.
• Confirm the destination's collation and time zone settings match, because both silently change query results.
• Run a set of known business queries on both databases and compare the output, which catches problems that structural checks do not.
• Recreate application users and their grants, which never travel with the data.

The promotion moment

The switch itself is short and should be scripted. Stop writes at the source, either through the application or by revoking write permissions, which is the more reliable of the two.

Wait for replication lag to reach zero and confirm it from the destination rather than assuming. Run the verification checks. Promote the destination to primary. Update the application's connection configuration, ideally by changing a secret or a DNS alias rather than by redeploying, because a redeployment adds minutes and a new set of risks.

Then run smoke tests that exercise a write, a read of that write, and a transaction that touches several tables. Only after those pass do you release traffic.

Keep the source database running, readable and no longer written to for at least a week. It is the reference you will want when a report looks wrong on Thursday. The wider choreography around this moment is described in moving between cloud providers without downtime.

Common questions

See database backups and point-in-time recovery and migration testing and rollback plans for those two topics. Vendor references are the PostgreSQL logical replication documentation and the AWS Database Migration Service user guide; the AWS account listings describe what that platform provides on the destination side.