Indexes are specifically NOT applied during a liquibase upgrade, unless the target table is empty. This is to protect the database and application from locking behavior during index creation that could cause an outage. To review index creation SQL separately for each service targeted for upgrade, you can perform several steps:
Emit a sql file by referencing your target version liquibase changelog file and reference database using Liquibase utilities.
Repeat step 1 for each microservice you plan to upgrade.
The end result be a migrate.sql file that represents the delta between the reference and the target version. Moreover, it should only contain the relevant index creation statements. This SQL will then be useful to apply manually against your environment(s) during a window that best fits your needs.
Once you have identified a copy of a lower environment database (or local DB) where Liquibase is used, you may proceed with generating the index sql for the upgrade.
Identify the pom.xml for the FlexPackage (e.g. Min, Browse, Cart, Processing, Supporting), or granular service for which an upgrade is desired.
Add support for the liquibase maven plugin to the pom.xml file - similar to Liquibase Maven Example.
Upgrade your project pom.xml to reference the release train BOM for your target version (or upgrade your pom with more granular overrides) to cause your codebase to adopt the newer release of Broadleaf.
In the same directory as the pom.xml file, add a file called liquibase.properties.
In the liquibase.properties file, add values to inform liquibase of how to connect to your reference database. Refer to Liquibase Properties Example.
In a terminal in the same directory as the pom.xml file, execute mvn liquibase:updateSQL. This should result in a file called migrate.sql being created in the ./target directory of your maven project. Review this file. It should only contain index creation statements (see #7 in Liquibase Properties Example). You will most likely want to edit these statements to make then non-blocking before using. See Mitigate Index Creation Blocking. Note, this file may contain SQL updates to databasechangeloglock and databasechangelog (internal Liquibase maintenance tables) that are not interesting for the purpose of understanding the index creation SQL. If so, you can safely ignore these additional statements.
<build>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>4.9.1</version>
<configuration>
<propertyFile>liquibase.properties</propertyFile>
</configuration>
</plugin>
</plugins>
</pluginManagement>
</build>See https://docs.liquibase.com/tools-integrations/maven/workflows/using-liquibase-maven-plugin-and-springboot.html for more information on the liquibase maven plugin.
changelogFile: classpath:/db/changelog/adminnavigation.postgresql.changelog-master.xml (1)
url: jdbc:postgresql://localhost:5432/broadleaf (2)
username: broadleaf (3)
password: demo (4)
defaultSchemaName: adminnavigation (5)
liquibaseSchemaName: adminnavigation (6)
labels: index (7)Path pointing to the Broadleaf liquibase changelog for the target service. Note, if you have your own changelog that internally references the Broadleaf changelog, then you may use that here instead.
JDBC URL pointing to the temporary database
Username for the JDBC connection
Password for the JDBC connection
The name of the schema in which the baseline Broadleaf database objects should be created. You may wish to create this schema if it does not already exist in the temporary database (if applicable).
The name of the schema in which liquibase specific maintenance database objects should be created. This can be the same as the default schema declared above.
Filter on the index label to only include index creation related changesets.
See https://docs.liquibase.com/concepts/connections/creating-config-properties.html for more information on the contents and properties available to liquibase.properties.
MySQL - No changes to index required. MySQL already supports online behavior by default. See https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes.
Oracle - End the index creation statement with the ONLINE keyword. See https://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes003.htm.
MariaDB - You’ll likely want to rewrite the index creation sql according to this template: ALTER ONLINE TABLE [table name] ADD INDEX IF NOT EXISTS [index name] ([column names]). The column names value is a comma delimited list of table column names to include in the index. See https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-inplace-alter-algorithm/#alter-table-add-index-and-create-index.
Postgresql - Add CONCURRENTLY IF NOT EXISTS immediately after the CREATE INDEX portion of the SQL statement. For example, CREATE INDEX idxmsrmtlej61uxhx0yiwpb2rcrv ON blc_notification_state(acked, stopped, notification_name, change_timestamp, next_attempt); becomes CREATE INDEX CONCURRENTLY IF NOT EXISTS idxmsrmtlej61uxhx0yiwpb2rcrv ON blc_notification_state(acked, stopped, notification_name, change_timestamp, next_attempt);. See https://www.postgresql.org/docs/current/sql-createindex.html.
Yugabyte - Yugabyte already supports concurrent behavior during index creation. It is still useful to add the clause IF NOT EXISTS immediately after the CREATE INDEX portion of the SQL statement (similar to Postgresql). See https://github.com/yugabyte/yugabyte-db/issues/10799.