Script-based Migrations

[enobayram]

We're currently using beam-automigrate + a number of custom migration commands at chainweb-data start up for managing our Postgres schema. This combination served us well so far, but going forward we're planning to use more and more advanced features of Postgres like partitioned tables, triggers etc. so it seems like we'd be better served by converting to the more conventional migration scheme of expressing the schema migrations as a sequence of SQL scripts to be executed at start up.

As part of this transition to the script-based migrations, we also need to consider the case of an existing chainweb-data deployment using a very old version of the chainweb-data binary. Here's how we're planning to perform this transition in a way that works for the existing chainweb-data deployments too:

• We declare that the next release is a "transition version".
• The transition version keeps migrating the database using beam-automigrate same as how been doing it so far.
• The transition version also runs the MigrationInitialization command of postgresql-simple-migration seems like a good library to support this alternative way of managing schema migrations. Marking the deployment as having gone through the "transition migration". I.e. when this command is run, we know that the DB schema is in the known transition state.
• We use pg_dump to take a snapshot of the Postgres DB schema as it appears at the transition version and manually go through the dump to make sure we don't have anything deployment-specific in there so that it can be run on any empty Postgres database. We call the resulting SQL dump init.sql.
• Post-transition versions of chainweb-data performs the following logic:
  • If blocks table doesn't exist) That means this is an entirely new deployment, so we run init.sql followed by the incremental migrations scripts.
  • If blocks table exists) Then we check whether MigrationInitialization has already been performed. (I.e. we run the MigrationValidation MigrationInitialization MigrationCommand.)
    • If MigrationInitialization was run) This means this is a properly transitioned deployment, so we just apply any missing migration scripts.
    • If MigrationInitialization was not run) That means this is a pre-transition deployment, so we exit with a message instructing the user to run the transition release