Daml
Sharing of PostgreSQL database with other applications
Sharing of PostgreSQL database with other applications
An application making use of the PQS datastore may also manage its own database migrations via Flyway - either embedded, command-line, or other supported means. An example of such a scenario is the creation of application specific indexes.
With default settings, the application’s Flyway produces an error similar to the one seen below because its view of available/valid migrations is different from PQS:
$ flyway -configFiles=conf/flyway.toml migrate
Flyway Community Edition 10.12.0 by Redgate
See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 15.5)
ERROR: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 001
-> Applied to database : -332813992
-> Resolved locally : 422692347
Either revert the changes to the migration, or run repair to update the schema history.
Need more flexibility with validation rules? Learn more: https://rd.gt/3AbJUZE
Note
Here, the command-line Flyway was used for demonstration purposes. The same applies to other methods of running Flyway.
However, it is trivial to instruct the application’s Flyway to use a different, non-default table name to store its versioning information, which allows both Flyways to coexist in the same database.
select version, description, script, success from flyway_schema_history;
version | description | script | success
---------+-------------------------------------------------------+-----------------------------------------------------------------+---------
001 | Create initial schema | V001__Create_initial_schema.sql | t
002 | Make initializecontractimplements function idempotent | V002__Make_initializecontractimplements_function_idempotent.sql | t
003 | Fix create index for contract | V003__Fix_create_index_for_contract.sql | t
004 | Add tmp to tx references cascade constraint | V004__Add_tmp_to_tx_references_cascade_constraint.sql | t
005 | Make watermark progression safer | V005__Make_watermark_progression_safer.sql | t
006 | Make checkpoint functions stable | V006__Make_checkpoint_functions_stable.sql | t
007 | Expose effective at | V007__Expose_effective_at.sql | t
(7 rows)
$ flyway -configFiles=conf/flyway.toml migrate -table=myapp_version -baselineOnMigrate=true -baselineVersion=0
Flyway Community Edition 10.12.0 by Redgate
See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 15.5)
Schema history table "public"."myapp_version" does not exist yet
Successfully validated 1 migration (execution time 00:00.029s)
Creating Schema History table "public"."myapp_version" with baseline ...
Successfully baselined schema with version: 0
Current version of schema "public": 0
Migrating schema "public" to version "1.0 - Add index on Ping sender"
Successfully applied 1 migration to schema "public", now at version v1.0 (execution time 00:00.036s)
select version, description, script, success from myapp_version;
version | description | script | success
---------+--------------------------+------------------------------------+---------
0 | << Flyway Baseline >> | << Flyway Baseline >> | t
1.0 | Add index on Ping sender | V1.0__Add_index_on_Ping_sender.sql | t
(2 rows)
Now both PQS and the application can manage their own schema versions independently. Assuming the application limits itself to only adding indexes and other non-conflicting changes, the two Flyways can coexist without issues.
-- new PQS release applies missing migrations
org.flywaydb.core.internal.command.DbValidate: Successfully validated 8 migrations (execution time 00:00.018s) application=scribe
org.flywaydb.core.internal.command.DbSchemas: Skipping creation of existing schema: "public" application=scribe
org.flywaydb.core.internal.command.DbMigrate: Current version of schema "public": 007 application=scribe
org.flywaydb.core.internal.parser.Parser: Parsing V008__Add_new_table.sql ... application=scribe
org.flywaydb.core.internal.sqlscript.ParserSqlScript: Found statement at line 1: create table _foo(value int) application=scribe
org.flywaydb.core.internal.command.DbMigrate: Starting migration of schema "public" to version "008 - Add new table" ... application=scribe
org.flywaydb.core.internal.command.DbMigrate: Migrating schema "public" to version "008 - Add new table" application=scribe
org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor: Executing SQL: create table _foo(value int) application=scribe
org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor: 0 rows affected application=scribe
org.flywaydb.core.internal.command.DbMigrate: Successfully completed migration of schema "public" to version "008 - Add new table" application=scribe
org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory: Schema History table "public"."flyway_schema_history" successfully updated to reflect changes application=scribe
org.flywaydb.core.internal.command.DbMigrate: Successfully applied 1 migration to schema "public", now at version v008 (execution time 00:00.006s) application=scribe
## the application applies missing migrations
$ flyway -configFiles=conf/flyway.toml migrate -table=myapp_version
Flyway Community Edition 10.12.0 by Redgate
See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 15.5)
Successfully validated 3 migrations (execution time 00:00.060s)
Current version of schema "public": 1.0
Migrating schema "public" to version "1.1 - Add index on Ping receiver"
Successfully applied 1 migration to schema "public", now at version v1.1 (execution time 00:00.020s)
As can be observed below, PQS and the application can even opt for different versioning schemes.
select version, description, script, success from flyway_schema_history ;
version | description | script | success
---------+-------------------------------------------------------+-----------------------------------------------------------------+---------
001 | Create initial schema | V001__Create_initial_schema.sql | t
002 | Make initializecontractimplements function idempotent | V002__Make_initializecontractimplements_function_idempotent.sql | t
003 | Fix create index for contract | V003__Fix_create_index_for_contract.sql | t
004 | Add tmp to tx references cascade constraint | V004__Add_tmp_to_tx_references_cascade_constraint.sql | t
005 | Make watermark progression safer | V005__Make_watermark_progression_safer.sql | t
006 | Make checkpoint functions stable | V006__Make_checkpoint_functions_stable.sql | t
007 | Expose effective at | V007__Expose_effective_at.sql | t
008 | Add new table | V008__Add_new_table.sql | t
(8 rows)
select version, description, script, success from myapp_version ;
version | description | script | success
---------+----------------------------+--------------------------------------+---------
0 | << Flyway Baseline >> | << Flyway Baseline >> | t
1.0 | Add index on Ping sender | V1.0__Add_index_on_Ping_sender.sql | t
1.1 | Add index on Ping receiver | V1.1__Add_index_on_Ping_receiver.sql | t
(3 rows)
postgres=# \di+ __contracts_1_ping_sender_idx
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+-------------------------------+-------+----------+---------------+-------------+---------------+-------+-------------
public | __contracts_1_ping_sender_idx | index | postgres | __contracts_1 | permanent | hash | 32 kB |
(1 row)
postgres=# \di+ __contracts_1_ping_receiver_idx
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+---------------------------------+-------+----------+---------------+-------------+---------------+-------+-------------
public | __contracts_1_ping_receiver_idx | index | postgres | __contracts_1 | permanent | hash | 32 kB |
(1 row)
$ tree .
.
├── conf
│ └── flyway.toml
└── migrations
├── V1.0__Add_index_on_Ping_sender.sql
└── V1.1__Add_index_on_Ping_receiver.sql
$ head migrations/*
==> migrations/V1.0__Add_index_on_Ping_sender.sql <==
call create_index_for_contract('ping_sender', 'PingPong:Ping', '(payload->>''sender'')', 'hash');
==> migrations/V1.1__Add_index_on_Ping_receiver.sql <==
call create_index_for_contract('ping_receiver', 'PingPong:Ping', '(payload->>''receiver'')', 'hash');
