Database schema migrations with zero downtime (Continuous Lifecycle London 2019)

DATABASE SCHEMA MIGRATIONS WITHOUT DOWNTIME MICHIEL ROOK @michieltcs

@michieltcs

@michieltcs CONTINUOUS DEPLOYMENT

@michieltcs 50+ DEPLOYS PER DAY

@michieltcs HIGH AVAILABILITY

@michieltcs DATABASE MIGRATIONS

@michieltcs WITHOUT DOWNTIME?

@michieltcs DATABASE SCHEMA MIGRATIONS

@michieltcs DATABASE SCHEMA

@michieltcs

@michieltcs DATABASE SCHEMA MIGRATIONS

@michieltcs SQL

@michieltcs UP

@michieltcs UP CREATE TABLE addresses(id INT NOT NULL,  street VARCHAR(255)
NOT NULL, PRIMARY KEY('id'));

@michieltcs UP DOWN

@michieltcs UP DOWN DROP TABLE addresses;

@michieltcs TRANSACTIONS

@michieltcs

@michieltcs FLYWAY

@michieltcs V20170228150619__create_addresses_table.sql    CREATE TABLE addresses (id INT NOT NULL,
street VARCHAR(255) NOT NULL, PRIMARY KEY(id));

@michieltcs $ flyway migrate  Flyway Community Edition 5.0.7 by Boxfuse 
  Database: jdbc:mysql://localhost:3306/migrate (MySQL 5.7)  Successfully validated 1 migration (execution time 00:00.018s)  Creating Schema History table: `migrate`.`flyway_schema_history`  Current version of schema `migrate`: << Empty Schema >>  Migrating schema `migrate` to version 20170228150619 - create addresses table  Successfully applied 1 migration to schema `migrate` (execution time 00:00.086s)

@michieltcs LEGACY APPLICATIONS

@michieltcs V1__base_version.sql    < SNAPSHOT OF EXISTING DATABASE SCHEMA >

@michieltcs $ flyway baseline  Flyway Community Edition 5.0.7 by Boxfuse 
  Database: jdbc:mysql://localhost:3306/migrate (MySQL 5.7)  Creating Schema History table: `migrate`.`flyway_schema_history`  Successfully baselined schema with version: 1

@michieltcs REPEATABLE MIGRATIONS 1

@michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS 1 2

@michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS CALLBACKS 1 2 3

@michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS CALLBACKS ... AND THE PRO
VERSION 1 2 3 4

@michieltcs UNDOING MIGRATIONS

@michieltcs ROLL BACK  OR  ROLL FORWARD?

@michieltcs DESTRUCTIVE CHANGES 1

@michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS

@michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES

@michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES DELETES

@michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS 1 2

@michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING 1 2 3

@michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING MULTIPLE DATABASES 1 2
3 4

@michieltcs RESTORING FROM BACKUPS?

@michieltcs ROLLBACKS

@michieltcs DOWNTIME?

@michieltcs SIMPLE DEPLOYMENT FLOW

@michieltcs BUILD IMAGE

@michieltcs BUILD IMAGE STOP  CONTAINER

@michieltcs BUILD IMAGE STOP  CONTAINER RUN  MIGRATIONS

@michieltcs BUILD IMAGE STOP  CONTAINER RUN  MIGRATIONS START  CONTAINER

@michieltcs DOWNTIME?

@michieltcs CONTINUOUS DELIVERY

@michieltcs CONTINUOUS DEPLOYMENT

@michieltcs ZERO DOWNTIME DEPLOYS

@michieltcs ZERO DOWNTIME DEPLOYMENT LOAD BALANCER APP 1.0

@michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

@michieltcs LOAD BALANCER APP 2.0 ZERO DOWNTIME DEPLOYMENT

@michieltcs DOWNTIME

@michieltcs NO MIGRATIONS!

@michieltcs DOWNTIME

@michieltcs DATABASE STATE?

@michieltcs DATABASE VERSION LOAD BALANCER APP 1.0 DATABASE 1.0 expects
1.0

@michieltcs DATABASE VERSION LOAD BALANCER APP 1.0 DATABASE 1.0 expects
1.0 Migration

@michieltcs LOAD BALANCER APP 1.0 DATABASE 2.0 expects 1.0 DATABASE
VERSION

@michieltcs LOAD BALANCER APP 1.0 APP 2.0 DATABASE 2.0 expects
1.0 expects 2.0 DATABASE VERSION

@michieltcs OLD APP  SHOULD WORK WITH  NEW STATE

@michieltcs ONE DIFF BACK

@michieltcs ONE BUILD BACK

@michieltcs ONE BUILD BACK (git revert HEAD)

@michieltcs DECOUPLE

@michieltcs MIGRATION DECOUPLE DEPLOYMENT

@michieltcs NON-DESTRUCTIVE CHANGES

@michieltcs ADDING TABLES

@michieltcs ADDING TABLES ADDING COLUMNS

@michieltcs ADDING TABLES ADDING COLUMNS CREATING INDEXES

@michieltcs ADDING TABLES ADDING COLUMNS CREATING INDEXES sometimes ...

@michieltcs EXAMPLE:  RENAMING A COLUMN

@michieltcs surname last_name

@michieltcs EXPAND - CONTRACT

@michieltcs CREATE COLUMN WITH NEW NAME 1

@michieltcs CREATE COLUMN WITH NEW NAME 1 ALTER TABLE person 
ADD last_name VARCHAR(255);

@michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &
NEW COLUMNS 1 2

NEW COLUMNS 1 2 public void setLastName(String lastName) {  this.lastName = lastName;  this.surname = lastName;  }

NEW COLUMNS 1 2 public String getLastName() {  return (lastName ? lastName : surname);  }

NEW COLUMNS MIGRATE OLD RECORDS 1 2 3

NEW COLUMNS MIGRATE OLD RECORDS 1 2 3 UPDATE person SET person.last_name = person.surname  WHERE person.last_name IS NULL;

NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4

NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 public String getLastName() {  return lastName;  }

NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 remove old column and code

@michieltcs

@michieltcs NOSQL

@michieltcs NO SCHEMA

@michieltcs NO SCHEMA?

@michieltcs { "firstName": "foo", "surName": "bar"  }

@michieltcs { "firstName": "foo", "surName": "bar", "version": 1 }

@michieltcs { "firstName": "foo", "surName": "bar", "version": 1 } {
"firstName": "foo", "lastName": "bar", "version": 2 }

@michieltcs MIGRATE DURING READ

@michieltcs UPGRADE SCRIPT https://github.com/emirotin/mongodb-migrations

@michieltcs BACKGROUND MIGRATION

@michieltcs NO DOWNTIME

@michieltcs TESTING MIGRATIONS

@michieltcs CI/CD PIPELINE

@michieltcs 1.spin up (empty) db instance

@michieltcs 1.spin up (empty) db instance 2.run migrations

@michieltcs 1.spin up (empty) db instance 2.run migrations 3.assert

@michieltcs STAGING / ACCEPTANCE

@michieltcs CHALLENGES

@michieltcs LONG MIGRATIONS

@michieltcs LONG MIGRATIONS FAILURES & RESTARTS

@michieltcs LONG MIGRATIONS MEMORY USAGE FAILURES & RESTARTS

@michieltcs LONG MIGRATIONS MEMORY USAGE CLEANING UP FAILURES & RESTARTS

@michieltcs LONG MIGRATIONS MEMORY USAGE LOGGING & PROGRESS FAILURES &
RESTARTS CLEANING UP

@michieltcs LONG MIGRATIONS MEMORY USAGE (NON-)NULLABLE TYPES LOGGING & PROGRESS
FAILURES & RESTARTS CLEANING UP

@michieltcs ALTERNATIVE  STRATEGIES

@michieltcs REPLICATION

@michieltcs RUN MIGRATION ON FAILOVER

@michieltcs MASTER SLAVE 1.0 1.0

@michieltcs MASTER SLAVE 1.0 1.0 STOP SLAVE SQL_THREAD;

@michieltcs MASTER SLAVE 1.0 2.0 ALTER TABLE person  DROP COLUMN
surname;

@michieltcs MASTER SLAVE 1.0 2.0 START SLAVE SQL_THREAD;

@michieltcs MASTER SLAVE 1.0 2.0 CHANGE MASTER TO ...;

@michieltcs SLAVE MASTER 1.0 2.0

@michieltcs SLAVE MASTER 1.0 2.0 STOP SLAVE SQL_THREAD;

@michieltcs SLAVE MASTER 2.0 2.0 ALTER TABLE person  DROP COLUMN
surname;

@michieltcs SLAVE MASTER 2.0 2.0 START SLAVE SQL_THREAD;

@michieltcs SLAVE MASTER 2.0 2.0

@michieltcs MESSAGE QUEUE

@michieltcs UI @michieltcs

@michieltcs UI messages Queue / Messaging Bus @michieltcs

@michieltcs UI messages Queue / Messaging Bus Backend messages @michieltcs

@michieltcs UI Database messages Queue / Messaging Bus Backend messages
@michieltcs

@michieltcs UI Data Layer Database messages queries DTOs Queue /
Messaging Bus Backend messages @michieltcs

@michieltcs CQRS

@michieltcs COMMAND QUERY RESPONSIBILITY SEGREGATION

@michieltcs SEPARATE  WRITES FROM READS

@michieltcs STORE UPDATES IN LOG

@michieltcs COMMIT LOG Write

@michieltcs @michieltcs UI Data Layer Database messages queries DTOs Message 
queue Backend messages Commit Log

@michieltcs REPLAY LOG TO (RE)BUILD DATABASE

@michieltcs @michieltcs UI Data Layer Database messages queries DTOs Backend
messages Database Commit Log Message  queue

@michieltcs PT ONLINE SCHEMA CHANGE

@michieltcs CREATE SHADOW COPY (TARGET TABLE) 1

@michieltcs CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD
UPDATES 1 2

UPDATES COPY SOURCE DATA IN SMALL CHUNKS 1 2 3

UPDATES COPY SOURCE DATA IN SMALL CHUNKS RENAME TARGET TABLE 1 2 3 4

@michieltcs pt-online-schema-change --execute  --alter "add index name_idx (name)" D=test,t=test1,h=localhost

@michieltcs RECAP

@michieltcs PLANNING

@michieltcs MULTIPLE STEPS

@michieltcs NO BREAKING CHANGES

THANK YOU  FOR LISTENING! @michieltcs / [email protected] www.michielrook.nl

Database schema migrations with zero downtime (...

Database schema migrations with zero downtime (Continuous Lifecycle London 2019)

More Decks by Michiel Rook

Other Decks in Technology

Featured

Transcript