smileutil: Migrate Database

The migrate-database command migrates a Smile CDR database to the latest version.

A typical Smile CDR installation will have two sets of tables:

• The Cluster Manager tables (contains configuration, audit events, etc.)
• The FHIR Storage tables (contains FHIR data)

It is recommended to configure Smile CDR to use separate schemas each database.

The following example shows the command in action:

# Migrate the Cluster Manager
bin/smileutil migrate-database --driver POSTGRES_9_4 --mode CLUSTERMGR --url "jdbc:postgresql://localhost/clustermgr" --username MYUSER --password MYPASS

# Migrate the Persistence Module
bin/smileutil migrate-database --driver POSTGRES_9_4 --mode PERSISTENCE --url "jdbc:postgresql://localhost/fhir_persistence" --username MYUSER --password MYPASS

Options

• -d [driver] (or --driver [driver]) – This argument tells the tool which database dialect to use. The values used here are identical to the values used in the Smile CDR configuration properties file.
• -n [username] (or --username [username]) – The database username.
• -p [password] (or --password [password]) – The database password.
• -u [url] (or --url [url]) – The JDBC database URL.
• -m [mode] (or --mode [mode]) – Specifies whether the cluster manager (CLUSTERMGR) or FHIR Storage (PERSISTENCE) module tables should be migrated, or both (CLUSTERMGR_AND_PERSISTENCE). In a typical Smile CDR installation these tables will both exist in the same database schema, so this command may be run once for each or by using the combined option. Some installations may separate these into separate schemas, or may not have FHIR Storage tables at all. Similarly, if the audit log and transaction log tables have been split into separate schemas, they can be migrated with the (AUDIT_LOG_PERSISTENCE) and (TRANSACTION_LOG_PERSISTENCE) modes respectively.
• -r (or --dry-run) – (optional) If included, instructs smileutil to perform a dry run, where no changes are made. Upon completion, smileutil prints the SQL that would have been executed to the terminal. While users may capture the output and pre-apply these queries manually ahead of an upgrade, please note that some queries may be missed in the dry-run output. Read Dry Run Output below before relying on it as a script to be executed manually.
• --debug – Enable debug mode.
• --no-column-shrink – If this flag is set, the system will not attempt to reduce the length of columns. This is useful in environments with a lot of existing data, where shrinking a column can take a very long time.
• --skip-versions <Versions> – A comma separated list of schema versions to skip. E.g. 4_1_0.20191214.2,4_1_0.20191214.4
• --enable-heavyweight-migrations – If this flag is set, additional migration tasks will be executed that are considered unnecessary to execute on a database with a significant amount of data loaded. This option is not generally necessary.

If you encounter issues with database migration locks, you can use the separate clear-migration-lock command to clear them. See the clear-migration-lock documentation for details.

Note that many of the options above require an argument from a list of possible values (such as the Database Driver). You can see a list of possible options by using the following command:

bin/smileutil help migrate-database

Dry Run Output

A dry run reports the SQL that the migration would run, without running any of it. This is useful for reviewing the scope of an upcoming upgrade.

Migration steps inspect the database before deciding what SQL to generate. An index, for example, is only created if it does not already exist. During a dry run nothing is actually executed, so those checks see the database as it was before the migration began. When one step changes something a later step inspects, the later step can reach the wrong conclusion.

The most common case is a migration that drops an index and recreates it with a different definition. The dry run reports the DROP, but since the index still exists as far as the database is concerned, the matching CREATE is left out. Pre-applying the dry-run output manually before an upgrade would drop the index and never recreate it. Note that any missed statements will be applied when the migration is next run (eg. migrate-database command) as part of the upgrade.

Note that any migrations that move data rather than change the schema will not show up in dry-runs and cannot be pre-applied. Instead, they are run as part of the upgrade itself.

Obtaining the exact SQL for an upgrade

1. Take a schema-only dump of the database you are upgrading, but include the rows of the migration tracking tables (FLY_HFJ_MIGRATION, FLY_CDR_MIGRATION, CDR_AUDIT_MIGRATION and CDR_TRANSACTION_MIGRATION, depending on which modes you migrate). Note that no patient data leaves your environment, only migration rows are dumped.
2. Restore the dump into a temporary database running the same engine, version and edition (edition can change the SQL some migrations generate) as the one you are upgrading.
3. Run migrate-database against the temporary database without --dry-run, using the Smile CDR version you are upgrading to. All statements are executed in the temporary database, so every check a migration step makes is accurate.
4. Capture the executed SQL from the temporary database server's own statement log.
5. Discard the temporary database.

Note that migrations that move data rather than change the schema will still not appear through this method, since they are driven by the contents of the tables they operate on. Such migrations will only run as part of the upgrade itself.

Examples

Migrating the default H2 database setup:

# Migrate the Cluster Manager
bin/smileutil migrate-database -d H2_EMBEDDED -m CLUSTERMGR -u "jdbc:h2:file:./database/h2_clustermgr" --username "SA" --password "SA"

# Migrate the Persistence Module
bin/smileutil migrate-database -d H2_EMBEDDED -m PERSISTENCE -u "jdbc:h2:file:./database/h2_fhir_persistence" --username "SA" --password "SA"