PostgreSQL Database Migration
When working with SonataFlow PostgreSQL database, you can either opt to use Flyway or manually upgrade your database via DDL scripts.
When you upgrade your SonataFlow version, by default it won’t pick up the latest changes available.
Kogito provides a mechanism for migrating your database while updating the Kogito version using the following
Flywayproperties (default value is
false, not upgrade):
This will create a schema history table
flyway_schema_historyin your database to track the version of each database, recording in it every versioned migration file applied to build that version.
kogito-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.
If you want to migrate manually you can use the Flyway migration CLI tool.
Download Flyway migration command-line tool from the Download Flyway Community Edition website.
Add the following properties in the
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.
$ flyway migrate -url=jdbc:postgresql://localhost:5432/foobardb -user=foo -password=bar
Download the Kogito DDL zip from this link, choose the correct version, extract the files and use the scripts from the
You can specify the location of the SQL files that need to be migrated using the
flyway.locationsoption. For example,
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 this link.
Download the zip file of the scripts from the needed version for the Kogito upgrade.
Extract the files.
> psql -H host -U username -d database_name -a -f create_table.sql
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.
$ flyway -baselineOnMigrate="true" -baselineVersion="1.32.0" migrate
|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.
If you find an issue or any misleading information, please feel free to report it here. We really appreciate it!