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 isfalse
, 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 frompostgresql
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. |
Found an issue?
If you find an issue or any misleading information, please feel free to report it here. We really appreciate it!