aurora-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Sirois <>
Subject Re: Handling migrations if dbScript exists in snapshot?
Date Wed, 23 Mar 2016 20:29:16 GMT
On Wed, Mar 23, 2016 at 2:21 PM, Joshua Cohen <> wrote:

> Hi Aurorans,
> As you may have seen (,
> we ran into an issue when upgrading a cluster that uses dbScripts in
> snapshots. To restate the problem from the ticket, when the scheduler
> starts up it creates the H2 database from schema.sql which contains newly
> added (or changed) tables. If the dbScript exists in the snapshot, it then
> drops all tables and runs the script which re-creates the database in the
> form it was previously in and repopulates all the data. At this point, if a
> table was added that was not in the snapshot, that table is lost.
> The solution here is to be run a migration after restoring the database, my
> question is what level of complexity do we want for migrations. I see two
> possible options:
>    1. The relatively simple approach where we have a single migration.sql
>    alongside schema.sql which contains any changes to run against the
>    database. After restoring the db from a dbScript in the snapshot, we
> would
>    run this script. On release boundaries(?), we clear the script out. The
>    main caveat here is that the ddl statements in this script must be
>    idempotent since they would potentially be run multiple times. Another
>    issue is this would make it more complicated to jump over versions
>    (upgrading from v0.12.0 to v0.14.0 means you might miss a migration that
>    only existed in the 0.13.0 release).
>    2. A slightly more involved approach would be to track the schema
>    version in the database itself. Instead of having a single migration.sql
>    script that contains all migrations, we could instead have
>    migrations/001.sql, migrations/002.sql, etc. We'd add a new table to the
>    database to track which migrations have been run. After restoring via
>    dbScript, we'd check the table and run any migrations necessary (or all
> of
>    the table does not exist).
> Thoughts on these options?

I have used 2 a few times in the past and my take is it only more complex
in 1 fixed piece of (java) code that can be well unit tested.  The sql
scripts are easier to read and write and reason about for devs and
operators alike.

> Anyone have any alternatives?
> Cheers,
> Joshua

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message