Database migrations without down time (010PHP March 2017)

Database migrations without down time (010PHP March 2017)

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

March 10, 2017
Tweet

Transcript

  1. 3.
  2. 5.

    SQL

  3. 8.

    DOCTRINE @michieltcs class Version20170228150619 extends AbstractMigration
 {
 public function up(Schema

    $schema)
 {
 $this->addSql('CREATE TABLE addresses (id INT NOT NULL, street VARCHAR(255) NOT NULL, PRIMARY KEY(id))');
 }
 
 public function down(Schema $schema)
 {
 $this->addSql('DROP TABLE addresses');
 }
 }
  4. 9.

    DOCTRINE @michieltcs mysql> select * from migration_versions;
 +----------------+
 | version

    |
 +----------------+
 | 20170228150619 |
 +----------------+
 1 row in set (0,00 sec)
  5. 10.

    FLYWAY @michieltcs mysql> select * from schema_version;
 +----------------+----------------+------------------------+----------+--------- | installed_rank

    | version | description | type | script +----------------+----------------+------------------------+----------+--------- | 1 | 1 | << Flyway Baseline >> | BASELINE | << Flywa | 2 | 20170228150619 | create-addresses-table | SQL | V2017022 +----------------+----------------+------------------------+----------+--------- 2 rows in set (0,00 sec) +------------+--------------+---------------------+----------------+- | checksum | installed_by | installed_on | execution_time | +------------+--------------+---------------------+----------------+- | NULL | migrations_1 | 2017-02-28 16:27:21 | 0 | | 1789629568 | migrations_1 | 2017-02-28 16:27:21 | 16 | +------------+--------------+---------------------+----------------+-
  6. 26.
  7. 27.
  8. 33.
  9. 34.
  10. 36.
  11. 45.
  12. 53.

    @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS 1 2 3
  13. 54.

    @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4
  14. 55.
  15. 60.
  16. 61.

    @michieltcs CREATE COLUMN WITH NEW NAME WRITE TO OLD &

    NEW COLUMNS MIGRATE OLD RECORDS READ FROM NEW COLUMN 1 2 3 4 feature toggle feature toggle
  17. 62.
  18. 63.
  19. 74.
  20. 77.

    @michieltcs UI Event Handlers Command Data Layer Database Database commands

    events queries DTOs Repository @michieltcs Database
  21. 78.

    NEW READ MODEL @michieltcs Loop over existing records Apply conversion

    Process queued updates Use new read model Updates Queue
  22. 79.
  23. 82.