Database Migrations

Database Migrations

A database migration takes data from a source to a destination.

The obvious way is to stop the source, take a dump, upload it into the destination and start the new database. The data is perfectly consistent and the system is down while the dump moves.

A table with billions of records that is read and written every second is copied while it runs:

1. A copy. The Users table is copied to a New users table. This takes about an hour, and thousands of records arrive in that hour.
2. A second copy, of every operation in the database log with a timestamp greater than X, the last record copied. It takes a few seconds, and fewer records arrive.
3. A trigger. An insert and update trigger on the old table sends each new write to the new table.
4. The missing records. The operations between two timestamps, Y and Z, are copied.

A trigger that updates a record the new table does not have yet does an upsert, or fails.

The clients are never stopped.

Migrating the Database

A view is an abstract table: an SQL query over one or more real tables. It lets the servers move to a new table without a code change.

The brute force way points the servers at the new table one at a time. S3 adds a record to the new table while S1 still reads the old one. S1 runs a count, gets 2 where the answer is 3, and makes a wrong decision.

The migration with a view:

1. Create a view on the new table that behaves like the old table. The new table has columns A, B and C, the old one B and C, so the view selects B and C.
2. Rename the view to the old table's name and the old table to obsolete, in a single command. Every server now goes through the view.
3. Restart the servers with code that points to the new table.
4. Drop the view.
5. Validate the data, by hand or automatically.

The downtime is in seconds. The brute force migration took a few minutes.

Migration Across Regions

A migration from one cloud provider to another, Azure to AWS, cannot use a database trigger or a view, because neither works across providers. A DB proxy replaces both.

The copy still works. An SQL dump and a second query move most of the data, and the records missed between two timestamps are moved afterwards.

The proxy:

1. Every server sends its SQL commands to the DB proxy.
2. A read goes to Azure. A write goes to both Azure and AWS, as the trigger did.
3. The missing records are copied across.
4. The proxy is pointed at the new tables. It behaves like the view, so no rename is needed.
5. The servers are restarted to point at AWS directly, and the proxy can be dropped.

A proxy that stays is a single point of failure. Each server needs two restarts: one to point at the proxy and one to point at the new databases.

Where some downtime is acceptable, a copy followed by a restart of every server is simpler.