Slide 1

Slide 1 text

DATABASE SCHEMA MIGRATIONS WITHOUT DOWNTIME MICHIEL ROOK @michieltcs

Slide 2

Slide 2 text

@michieltcs

Slide 3

Slide 3 text

@michieltcs CONTINUOUS DEPLOYMENT

Slide 4

Slide 4 text

@michieltcs 50+ DEPLOYS PER DAY

Slide 5

Slide 5 text

@michieltcs HIGH AVAILABILITY

Slide 6

Slide 6 text

@michieltcs DATABASE MIGRATIONS

Slide 7

Slide 7 text

@michieltcs WITHOUT DOWNTIME?

Slide 8

Slide 8 text

@michieltcs DATABASE SCHEMA MIGRATIONS

Slide 9

Slide 9 text

@michieltcs DATABASE SCHEMA

Slide 10

Slide 10 text

@michieltcs

Slide 11

Slide 11 text

@michieltcs

Slide 12

Slide 12 text

@michieltcs DATABASE SCHEMA MIGRATIONS

Slide 13

Slide 13 text

@michieltcs SQL

Slide 14

Slide 14 text

@michieltcs UP

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

@michieltcs UP DOWN

Slide 17

Slide 17 text

@michieltcs UP DOWN DROP TABLE addresses;

Slide 18

Slide 18 text

@michieltcs TRANSACTIONS

Slide 19

Slide 19 text

@michieltcs

Slide 20

Slide 20 text

@michieltcs FLYWAY

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

@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)

Slide 23

Slide 23 text

@michieltcs mysql> select * from flyway_schema_history;
 +----------------+----------------+------------------------+------+---------------------- | installed_rank | version | description | type | script +----------------+----------------+------------------------+------+---------------------- | 1 | 20170228150619 | create addresses table | SQL | V20170228150619__crea +----------------+----------------+------------------------+------+---------------------- 1 row in set (0.00 sec) --------------+-----------+--------------+---------------------+----------------+---------+
 | checksum | installed_by | installed_on | execution_time | success |
 --------------+-----------+--------------+---------------------+----------------+---------+
 ses_table.sql | 671780326 | root | 2018-04-10 17:37:50 | 10 | 1 |
 --------------+-----------+--------------+---------------------+----------------+---------+


Slide 24

Slide 24 text

@michieltcs LEGACY APPLICATIONS

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

@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

Slide 27

Slide 27 text

@michieltcs --------------+----------+--------------+---------------------+----------------+---------+
 | checksum | installed_by | installed_on | execution_time | success |
 --------------+----------+--------------+---------------------+----------------+---------+
 y Baseline >> | NULL | root | 2018-04-11 08:43:14 | 0 | 1 |
 --------------+----------+--------------+---------------------+----------------+---------+
 mysql> select * from flyway_schema_history;
 +----------------+---------+-----------------------+----------+-----------------------+- | installed_rank | version | description | type | script | +----------------+---------+-----------------------+----------+-----------------------+- | 1 | 1 | << Flyway Baseline >> | BASELINE | << Flyway Baseline >> | +----------------+---------+-----------------------+----------+-----------------------+- 1 row in set (0.00 sec)

Slide 28

Slide 28 text

@michieltcs REPEATABLE MIGRATIONS 1

Slide 29

Slide 29 text

@michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS 1 2

Slide 30

Slide 30 text

@michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS CALLBACKS 1 2 3

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

@michieltcs UNDOING MIGRATIONS

Slide 33

Slide 33 text

@michieltcs ROLL BACK
 OR
 ROLL FORWARD?

Slide 34

Slide 34 text

@michieltcs DESTRUCTIVE CHANGES 1

Slide 35

Slide 35 text

@michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS

Slide 36

Slide 36 text

@michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES

Slide 37

Slide 37 text

@michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES DELETES

Slide 38

Slide 38 text

@michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS 1 2

Slide 39

Slide 39 text

@michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING 1 2 3

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

@michieltcs RESTORING FROM BACKUPS?

Slide 42

Slide 42 text

@michieltcs ROLLBACKS

Slide 43

Slide 43 text

@michieltcs DOWNTIME?

Slide 44

Slide 44 text

@michieltcs SIMPLE DEPLOYMENT FLOW

Slide 45

Slide 45 text

@michieltcs BUILD IMAGE

Slide 46

Slide 46 text

@michieltcs BUILD IMAGE STOP
 CONTAINER

Slide 47

Slide 47 text

@michieltcs BUILD IMAGE STOP
 CONTAINER RUN
 MIGRATIONS

Slide 48

Slide 48 text

@michieltcs BUILD IMAGE STOP
 CONTAINER RUN
 MIGRATIONS START
 CONTAINER

Slide 49

Slide 49 text

@michieltcs DOWNTIME?

Slide 50

Slide 50 text

@michieltcs CONTINUOUS DELIVERY

Slide 51

Slide 51 text

@michieltcs CONTINUOUS DEPLOYMENT

Slide 52

Slide 52 text

@michieltcs ZERO DOWNTIME DEPLOYS

Slide 53

Slide 53 text

@michieltcs ZERO DOWNTIME DEPLOYMENT LOAD BALANCER APP 1.0

Slide 54

Slide 54 text

@michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

Slide 55

Slide 55 text

@michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

Slide 56

Slide 56 text

@michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

Slide 57

Slide 57 text

@michieltcs LOAD BALANCER APP 2.0 ZERO DOWNTIME DEPLOYMENT

Slide 58

Slide 58 text

@michieltcs DOWNTIME

Slide 59

Slide 59 text

@michieltcs NO MIGRATIONS!

Slide 60

Slide 60 text

@michieltcs DOWNTIME

Slide 61

Slide 61 text

@michieltcs DATABASE STATE?

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

@michieltcs OLD APP
 SHOULD WORK WITH
 NEW STATE

Slide 68

Slide 68 text

@michieltcs ONE DIFF BACK

Slide 69

Slide 69 text

@michieltcs ONE BUILD BACK

Slide 70

Slide 70 text

@michieltcs ONE BUILD BACK (git revert HEAD)

Slide 71

Slide 71 text

@michieltcs DECOUPLE

Slide 72

Slide 72 text

@michieltcs MIGRATION DECOUPLE DEPLOYMENT

Slide 73

Slide 73 text

@michieltcs NON-DESTRUCTIVE CHANGES

Slide 74

Slide 74 text

@michieltcs ADDING TABLES

Slide 75

Slide 75 text

@michieltcs ADDING TABLES ADDING COLUMNS

Slide 76

Slide 76 text

@michieltcs ADDING TABLES ADDING COLUMNS CREATING INDEXES

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

@michieltcs EXAMPLE:
 RENAMING A COLUMN

Slide 79

Slide 79 text

@michieltcs surname last_name

Slide 80

Slide 80 text

@michieltcs EXPAND - CONTRACT

Slide 81

Slide 81 text

@michieltcs CREATE COLUMN WITH NEW NAME 1

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

@michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW COLUMNS 1 2 public void setLastName(String lastName) {
 this.lastName = lastName;
 this.surname = lastName;
 }

Slide 85

Slide 85 text

@michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW COLUMNS 1 2 public String getLastName() {
 return (lastName ? lastName : surname);
 }

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

@michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW COLUMNS MIGRATE OLD RECORDS 1 2 3 UPDATE person SET person.last_name = person.surname
 WHERE person.last_name IS NULL;

Slide 88

Slide 88 text

@michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4

Slide 89

Slide 89 text

@michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 public String getLastName() {
 return lastName;
 }

Slide 90

Slide 90 text

@michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD & NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 remove old column and code

Slide 91

Slide 91 text

@michieltcs

Slide 92

Slide 92 text

@michieltcs NOSQL

Slide 93

Slide 93 text

@michieltcs NO SCHEMA

Slide 94

Slide 94 text

@michieltcs NO SCHEMA?

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

@michieltcs MIGRATE DURING READ

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

@michieltcs BACKGROUND MIGRATION

Slide 101

Slide 101 text

@michieltcs NO DOWNTIME

Slide 102

Slide 102 text

@michieltcs TESTING MIGRATIONS

Slide 103

Slide 103 text

@michieltcs CI/CD PIPELINE

Slide 104

Slide 104 text

@michieltcs 1.spin up (empty) db instance

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

@michieltcs STAGING / ACCEPTANCE

Slide 108

Slide 108 text

@michieltcs CHALLENGES

Slide 109

Slide 109 text

@michieltcs LONG MIGRATIONS

Slide 110

Slide 110 text

@michieltcs LONG MIGRATIONS FAILURES & RESTARTS

Slide 111

Slide 111 text

@michieltcs LONG MIGRATIONS MEMORY USAGE FAILURES & RESTARTS

Slide 112

Slide 112 text

@michieltcs LONG MIGRATIONS MEMORY USAGE CLEANING UP FAILURES & RESTARTS

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

@michieltcs ALTERNATIVE
 STRATEGIES

Slide 116

Slide 116 text

@michieltcs REPLICATION

Slide 117

Slide 117 text

@michieltcs RUN MIGRATION ON FAILOVER

Slide 118

Slide 118 text

@michieltcs MASTER SLAVE 1.0 1.0

Slide 119

Slide 119 text

@michieltcs MASTER SLAVE 1.0 1.0 STOP SLAVE SQL_THREAD;

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

@michieltcs MASTER SLAVE 1.0 2.0 START SLAVE SQL_THREAD;

Slide 122

Slide 122 text

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

Slide 123

Slide 123 text

@michieltcs SLAVE MASTER 1.0 2.0

Slide 124

Slide 124 text

@michieltcs SLAVE MASTER 1.0 2.0 STOP SLAVE SQL_THREAD;

Slide 125

Slide 125 text

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

Slide 126

Slide 126 text

@michieltcs SLAVE MASTER 2.0 2.0 START SLAVE SQL_THREAD;

Slide 127

Slide 127 text

@michieltcs SLAVE MASTER 2.0 2.0

Slide 128

Slide 128 text

@michieltcs MESSAGE QUEUE

Slide 129

Slide 129 text

@michieltcs UI @michieltcs

Slide 130

Slide 130 text

@michieltcs UI messages Queue / Messaging Bus @michieltcs

Slide 131

Slide 131 text

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

Slide 132

Slide 132 text

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

Slide 133

Slide 133 text

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

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

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

Slide 136

Slide 136 text

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

Slide 137

Slide 137 text

@michieltcs CQRS

Slide 138

Slide 138 text

@michieltcs COMMAND QUERY RESPONSIBILITY SEGREGATION

Slide 139

Slide 139 text

@michieltcs SEPARATE
 WRITES FROM READS

Slide 140

Slide 140 text

@michieltcs STORE UPDATES IN LOG

Slide 141

Slide 141 text

@michieltcs COMMIT LOG Write

Slide 142

Slide 142 text

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

Slide 143

Slide 143 text

@michieltcs REPLAY LOG TO (RE)BUILD DATABASE

Slide 144

Slide 144 text

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

Slide 145

Slide 145 text

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

Slide 146

Slide 146 text

@michieltcs PT ONLINE SCHEMA CHANGE

Slide 147

Slide 147 text

@michieltcs CREATE SHADOW COPY (TARGET TABLE) 1

Slide 148

Slide 148 text

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

Slide 149

Slide 149 text

@michieltcs CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD UPDATES COPY SOURCE DATA IN SMALL CHUNKS 1 2 3

Slide 150

Slide 150 text

@michieltcs CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD UPDATES COPY SOURCE DATA IN SMALL CHUNKS RENAME TARGET TABLE 1 2 3 4

Slide 151

Slide 151 text

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

Slide 152

Slide 152 text

@michieltcs RECAP

Slide 153

Slide 153 text

@michieltcs PLANNING

Slide 154

Slide 154 text

@michieltcs MULTIPLE STEPS

Slide 155

Slide 155 text

@michieltcs NO BREAKING CHANGES

Slide 156

Slide 156 text

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