Show Index Creation SQL skipped during Liquibase migration
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.
Create Index SQL
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.
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.
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.