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

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

Does your application or service use a database? When that application changes because of new business requirements, you may need to make changes to the database schema. These database migrations could lead to downtime and can be an obstacle to implementing continuous delivery/deployment.

How can we deal with database migrations when we don’t want our end-users to experience downtime, and want to keep releasing?

In this talk we’ll discuss non-destructive changes, rollbacks, large data sets, useful tools and a few strategies to migrate our data safely, with minimum disruption to production.

2f4800411154a8c66dde489448a044d2?s=128

Michiel Rook

May 14, 2019
Tweet

Transcript

  1. DATABASE SCHEMA MIGRATIONS WITHOUT DOWNTIME MICHIEL ROOK @michieltcs

  2. @michieltcs

  3. @michieltcs CONTINUOUS DEPLOYMENT

  4. @michieltcs 50+ DEPLOYS PER DAY

  5. @michieltcs HIGH AVAILABILITY

  6. @michieltcs DATABASE MIGRATIONS

  7. @michieltcs WITHOUT DOWNTIME?

  8. @michieltcs DATABASE SCHEMA MIGRATIONS

  9. @michieltcs DATABASE SCHEMA

  10. @michieltcs

  11. @michieltcs

  12. @michieltcs DATABASE SCHEMA MIGRATIONS

  13. @michieltcs SQL

  14. @michieltcs UP

  15. @michieltcs UP CREATE TABLE addresses(id INT NOT NULL,
 street VARCHAR(255)

    NOT NULL, PRIMARY KEY('id'));
  16. @michieltcs UP DOWN

  17. @michieltcs UP DOWN DROP TABLE addresses;

  18. @michieltcs TRANSACTIONS

  19. @michieltcs

  20. @michieltcs FLYWAY

  21. @michieltcs V20170228150619__create_addresses_table.sql
 
 CREATE TABLE addresses (id INT NOT NULL,

    street VARCHAR(255) NOT NULL, PRIMARY KEY(id));
  22. @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)
  23. @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 |
 --------------+-----------+--------------+---------------------+----------------+---------+

  24. @michieltcs LEGACY APPLICATIONS

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

  26. @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
  27. @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)
  28. @michieltcs REPEATABLE MIGRATIONS 1

  29. @michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS 1 2

  30. @michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS CALLBACKS 1 2 3

  31. @michieltcs REPEATABLE MIGRATIONS JAVA MIGRATIONS CALLBACKS ... AND THE PRO

    VERSION 1 2 3 4
  32. @michieltcs UNDOING MIGRATIONS

  33. @michieltcs ROLL BACK
 OR
 ROLL FORWARD?

  34. @michieltcs DESTRUCTIVE CHANGES 1

  35. @michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS

  36. @michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES

  37. @michieltcs DESTRUCTIVE CHANGES 1 LONG-LIVED LOCKS RENAMES DELETES

  38. @michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS 1 2

  39. @michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING 1 2 3

  40. @michieltcs DESTRUCTIVE CHANGES IMPLICIT COMMITS SHARDING MULTIPLE DATABASES 1 2

    3 4
  41. @michieltcs RESTORING FROM BACKUPS?

  42. @michieltcs ROLLBACKS

  43. @michieltcs DOWNTIME?

  44. @michieltcs SIMPLE DEPLOYMENT FLOW

  45. @michieltcs BUILD IMAGE

  46. @michieltcs BUILD IMAGE STOP
 CONTAINER

  47. @michieltcs BUILD IMAGE STOP
 CONTAINER RUN
 MIGRATIONS

  48. @michieltcs BUILD IMAGE STOP
 CONTAINER RUN
 MIGRATIONS START
 CONTAINER

  49. @michieltcs DOWNTIME?

  50. @michieltcs CONTINUOUS DELIVERY

  51. @michieltcs CONTINUOUS DEPLOYMENT

  52. @michieltcs ZERO DOWNTIME DEPLOYS

  53. @michieltcs ZERO DOWNTIME DEPLOYMENT LOAD BALANCER APP 1.0

  54. @michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

  55. @michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

  56. @michieltcs LOAD BALANCER APP 1.0 APP 2.0 ZERO DOWNTIME DEPLOYMENT

  57. @michieltcs LOAD BALANCER APP 2.0 ZERO DOWNTIME DEPLOYMENT

  58. @michieltcs DOWNTIME

  59. @michieltcs NO MIGRATIONS!

  60. @michieltcs DOWNTIME

  61. @michieltcs DATABASE STATE?

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

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

    1.0 Migration
  64. @michieltcs LOAD BALANCER APP 1.0 DATABASE 2.0 expects 1.0 DATABASE

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

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

    1.0 expects 2.0 DATABASE VERSION
  67. @michieltcs OLD APP
 SHOULD WORK WITH
 NEW STATE

  68. @michieltcs ONE DIFF BACK

  69. @michieltcs ONE BUILD BACK

  70. @michieltcs ONE BUILD BACK (git revert HEAD)

  71. @michieltcs DECOUPLE

  72. @michieltcs MIGRATION DECOUPLE DEPLOYMENT

  73. @michieltcs NON-DESTRUCTIVE CHANGES

  74. @michieltcs ADDING TABLES

  75. @michieltcs ADDING TABLES ADDING COLUMNS

  76. @michieltcs ADDING TABLES ADDING COLUMNS CREATING INDEXES

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

  78. @michieltcs EXAMPLE:
 RENAMING A COLUMN

  79. @michieltcs surname last_name

  80. @michieltcs EXPAND - CONTRACT

  81. @michieltcs CREATE COLUMN WITH NEW NAME 1

  82. @michieltcs CREATE COLUMN WITH NEW NAME 1 ALTER TABLE person


    ADD last_name VARCHAR(255);
  83. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

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

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

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

    NEW COLUMNS MIGRATE OLD RECORDS 1 2 3
  87. @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;
  88. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4
  89. @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;
 }
  90. @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
  91. @michieltcs

  92. @michieltcs NOSQL

  93. @michieltcs NO SCHEMA

  94. @michieltcs NO SCHEMA?

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

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

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

    "firstName": "foo", "lastName": "bar", "version": 2 }
  98. @michieltcs MIGRATE DURING READ

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

  100. @michieltcs BACKGROUND MIGRATION

  101. @michieltcs NO DOWNTIME

  102. @michieltcs TESTING MIGRATIONS

  103. @michieltcs CI/CD PIPELINE

  104. @michieltcs 1.spin up (empty) db instance

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

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

  107. @michieltcs STAGING / ACCEPTANCE

  108. @michieltcs CHALLENGES

  109. @michieltcs LONG MIGRATIONS

  110. @michieltcs LONG MIGRATIONS FAILURES & RESTARTS

  111. @michieltcs LONG MIGRATIONS MEMORY USAGE FAILURES & RESTARTS

  112. @michieltcs LONG MIGRATIONS MEMORY USAGE CLEANING UP FAILURES & RESTARTS

  113. @michieltcs LONG MIGRATIONS MEMORY USAGE LOGGING & PROGRESS FAILURES &

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

    FAILURES & RESTARTS CLEANING UP
  115. @michieltcs ALTERNATIVE
 STRATEGIES

  116. @michieltcs REPLICATION

  117. @michieltcs RUN MIGRATION ON FAILOVER

  118. @michieltcs MASTER SLAVE 1.0 1.0

  119. @michieltcs MASTER SLAVE 1.0 1.0 STOP SLAVE SQL_THREAD;

  120. @michieltcs MASTER SLAVE 1.0 2.0 ALTER TABLE person
 DROP COLUMN

    surname;
  121. @michieltcs MASTER SLAVE 1.0 2.0 START SLAVE SQL_THREAD;

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

  123. @michieltcs SLAVE MASTER 1.0 2.0

  124. @michieltcs SLAVE MASTER 1.0 2.0 STOP SLAVE SQL_THREAD;

  125. @michieltcs SLAVE MASTER 2.0 2.0 ALTER TABLE person
 DROP COLUMN

    surname;
  126. @michieltcs SLAVE MASTER 2.0 2.0 START SLAVE SQL_THREAD;

  127. @michieltcs SLAVE MASTER 2.0 2.0

  128. @michieltcs MESSAGE QUEUE

  129. @michieltcs UI @michieltcs

  130. @michieltcs UI messages Queue / Messaging Bus @michieltcs

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

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

    @michieltcs
  133. @michieltcs UI Data Layer Database messages queries DTOs Queue /

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

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

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

    Messaging Bus Backend messages @michieltcs
  137. @michieltcs CQRS

  138. @michieltcs COMMAND QUERY RESPONSIBILITY SEGREGATION

  139. @michieltcs SEPARATE
 WRITES FROM READS

  140. @michieltcs STORE UPDATES IN LOG

  141. @michieltcs COMMIT LOG Write

  142. @michieltcs @michieltcs UI Data Layer Database messages queries DTOs Message


    queue Backend messages Commit Log
  143. @michieltcs REPLAY LOG TO (RE)BUILD DATABASE

  144. @michieltcs @michieltcs UI Data Layer Database messages queries DTOs Backend

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

    messages Database Commit Log Message
 queue
  146. @michieltcs PT ONLINE SCHEMA CHANGE

  147. @michieltcs CREATE SHADOW COPY (TARGET TABLE) 1

  148. @michieltcs CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD

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

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

    UPDATES COPY SOURCE DATA IN SMALL CHUNKS RENAME TARGET TABLE 1 2 3 4
  151. @michieltcs pt-online-schema-change --execute
 --alter "add index name_idx (name)" D=test,t=test1,h=localhost

  152. @michieltcs RECAP

  153. @michieltcs PLANNING

  154. @michieltcs MULTIPLE STEPS

  155. @michieltcs NO BREAKING CHANGES

  156. THANK YOU
 FOR LISTENING! @michieltcs / michiel@michielrook.nl www.michielrook.nl