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 (Continuous Lifecycle London 2019)

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

More Decks by Michiel Rook

Other Decks in Technology

Featured

Transcript