49.9.1smileutil: 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

49.9.2Options

• -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. Users can capture the output and apply these queries manually in order to migrate their database.
• --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

49.9.3Examples

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"