Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Database schema migrations with zero downtime (...

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.

Michiel Rook

May 14, 2019
Tweet

More Decks by Michiel Rook

Other Decks in Technology

Transcript

  1. @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)
  2. @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 |
 --------------+-----------+--------------+---------------------+----------------+---------+

  3. @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
  4. @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)
  5. @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

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

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

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

    NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4
  10. @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;
 }
  11. @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
  12. @michieltcs { "firstName": "foo", "surName": "bar", "version": 1 } {

    "firstName": "foo", "lastName": "bar", "version": 2 }
  13. @michieltcs UI Data Layer Database messages queries DTOs Queue /

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

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

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

    Messaging Bus Backend messages @michieltcs
  17. @michieltcs CREATE SHADOW COPY (TARGET TABLE) ADD TRIGGERS TO FORWARD

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

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