| = PostgreSQL Database Migration |
| |
| :flyway_install_url: https://flywaydb.org/download/community |
| :flyway_migrate_existing_url: https://flywaydb.org/documentation/learnmore/existing |
| :kogito_ddl_script_url: https://repository.apache.org/content/groups/snapshots/org/kie/kogito/kogito-ddl |
| :flyway_url: https://flywaydb.org/ |
| :flyway_baseline_migration_url: https://documentation.red-gate.com/fd/baseline-migrations-184127465.html |
| |
| When working with {product_name} PostgreSQL database, you can either opt to use link:{flyway_url}[Flyway] or manually upgrade your database via DDL scripts. |
| |
| When you upgrade your {product_name} version, by default it **won't** pick up the latest changes available. |
| |
| == How to migrate |
| |
| === Migrate using Flyway Config |
| |
| * Kogito provides a mechanism for migrating your database while updating the Kogito version using the following `Flyway` properties (default value is `false`, not upgrade): |
| + |
| -- |
| [source,properties] |
| ---- |
| quarkus.flyway.migrate-at-start=true |
| quarkus.datasource.db-kind=postgresql |
| ---- |
| -- |
| This will create a schema history table `flyway_schema_history` in your database to track the version of each database, recording in it every versioned migration file applied to build that version. |
| |
| NOTE: When using `kie-addons-persistence-jdbc`, it is mandatory to set the `quarkus.datasource.db-kind` property, so that Flyway can locate the appropriate scripts for the database. |
| |
| === Migrate using Flyway CLI |
| If you want to migrate manually you can use the Flyway migration CLI tool. |
| |
| * Download Flyway migration command-line tool from the link:{flyway_install_url}[Download Flyway Community Edition] website. |
| * Add the following properties in the `/conf/flyway.conf` file: |
| + |
| -- |
| [source,properties] |
| ---- |
| flyway.url=jdbc:postgresql://localhost:5432/foobardb |
| flyway.user=foo |
| flyway.password=bar |
| ---- |
| -- |
| * You can specify these options with commands as well. The Flyway CLI will prompt for the username and password if they are missing in the configuration. |
| + |
| -- |
| [source,console] |
| ---- |
| $ flyway migrate -url=jdbc:postgresql://localhost:5432/foobardb -user=foo -password=bar |
| ---- |
| -- |
| * Download the Kogito DDL zip from link:{kogito_ddl_script_url}[this link], choose the correct version, extract the files and use the scripts from the `postgresql` folder. |
| * You can specify the location of the SQL files that need to be migrated using the `flyway.locations` option. For example, |
| + |
| -- |
| [source,console] |
| ---- |
| flyway.locations=classpath:com.mycomp.migration,database/migrations,filesystem:/sql-migrations,s3:migrationsBucket,gcs:migrationsBucket |
| ---- |
| -- |
| |
| [#manually-executing-scripts] |
| === Manually executing scripts |
| You can use the provided SQL scripts in the zip file to migrate the database by executing them one by one. |
| |
| * You can find the Kogito DDL scripts by visiting link:{kogito_ddl_script_url}[this link]. |
| * Download the zip file of the scripts from the needed version for the Kogito upgrade. |
| * Extract the files. |
| * Execute the `sql` files from `postgresql` folder. |
| + |
| -- |
| [source,console] |
| ---- |
| > psql -H host -U username -d database_name -a -f create_table.sql |
| ---- |
| -- |
| |
| == Baseline migration |
| In order to apply migrations to an existing schema. It is necessary to establish the baseline from where Flyway should start executing new migrations. That means, in case your schema already contains a structure and from now on, new changes provided by Kogito should be applied via Flyway integration. In Flyway terms, only migrations above `baselineVersion` will then be applied. |
| Using below properties you can initialize flyway schema table. |
| |
| === Baseline using Quarkus Configuration: |
| -- |
| [source,properties] |
| ---- |
| quarkus.flyway.baseline-on-migrate=true |
| quarkus.flyway.baseline-version=1.32.0 |
| ---- |
| -- |
| === Baseline using Flyway CLI: |
| -- |
| [source,console] |
| ---- |
| $ flyway -baselineOnMigrate="true" -baselineVersion="1.32.0" migrate |
| ---- |
| -- |
| |
| NOTE: Be careful when enabling this feature as it removes the safety net which ensures that Flyway does not migrate the wrong database in case of a configuration mistake. |
| |
| === More details on Flyway migrations |
| * link:{flyway_baseline_migration_url}[Baseline Migrations Documentation]. |
| * link:{flyway_migrate_existing_url}[Existing Database Setup Documentation]. |
| |
| include::../../../../pages/_common-content/report-issue.adoc[] |
