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.

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):

    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.

When using 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.

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 Download Flyway Community Edition website.

  • Add the following properties in the /conf/flyway.conf file:

    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 postgresql folder.

  • You can specify the location of the SQL files that need to be migrated using the flyway.locations option. For example,

    flyway.locations=classpath:com.mycomp.migration,database/migrations,filesystem:/sql-migrations,s3:migrationsBucket,gcs:migrationsBucket

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 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.

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

quarkus.flyway.baseline-on-migrate=true
quarkus.flyway.baseline-version=1.32.0

Baseline using Flyway CLI:

$ 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.

More details on Flyway migrations

Found an issue?

If you find an issue or any misleading information, please feel free to report it here. We really appreciate it!