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.

postgres=# 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)

postgres=# 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.

postgres=# 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)

postgres=# 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');