Dynamically create databases for multiple BPA instances

[matgnt]

At the moment only 1 business partner agent (BPA) can run on the posgres instance started with the docker-compose config. If one want to add a second BPA, the same data is loaded into the second BPA. If one tries to change the jdbc connect string to add another database name

-Doagent.pg.url=jdbc:postgresql://${POSTGRESQL_WALLET_HOST}/second-agent 

..., BPA fails to start with:

PSQLException: FATAL: database "second-agent" does not exist

BPA should be able to dynamically create the necessary database.

[matgnt]

Switching to another container-internal in-memory (H2) database is not possible because JSONB datatype is only supported in postgres.

[etschelp]

Yes we specifically use porstgres. Supporting multiple databases would require changing the stack to something like liquibase and hibernate.

Back to original issue, creating databases on the fly is not possible with flyway. The workaround is sharing the same database by using schemas meaning each table has a separate namespace. This can be achieved by exposing a new property. Thats the easy part, the hard part is that our init scripts have the public schema in it, which means we have to reset everything.

[frank-bee]

I think docker compose might no be the right tool to handle launching of a larger infrastructure with e.g. several agents being launched. what about switching to a cloud native approach?
Something like: A BPA could be a helm chart which can be configured to launch a database or to use and externally provided one ( ...e.g with another helm chart). For each BPA you need, another helm chart is instantiated....
For postgres clusters there are OSS variants available for K8s: e.g. https://stackgres.io/

[frank-bee]

The workaround is sharing the same database by using schemas meaning each table has a separate namespace

Why would you see this more like a workaround , @etschelp ?
If we work with different schemas instead of dbs, would it be possible to launch agents next to each other with different versions?

[etschelp]

See pull request. Once merged different agents can share the same database by setting -Doagent.pg.schema=second-agent, default is public. Schemas are separated from each other and can support different agent versions.

[matgnt]

@etschelp Thanks for the quick reaction!

Unfortunately, I can't get it working. I deleted all postgres volumes I could find and added the option -Doagent.pg.schema=...

On the first start, I get the following error:
Anything else to change?

15:59:52.803 [main] INFO  Schema - Creating schema """0fef2b1df326""" ...
15:59:52.815 [main] INFO  JdbcTableSchemaHistory - Creating Schema History table """0fef2b1df326"""."flyway_schema_history" ...
15:59:52.890 [main] INFO  DbMigrate - Current version of schema """0fef2b1df326""": null
15:59:52.946 [main] INFO  DbMigrate - Migrating schema """0fef2b1df326""" to version 1 - create-initial-schema
15:59:52.970 [main] ERROR DbMigrate - Migration of schema """0fef2b1df326""" to version 1 - create-initial-schema failed! Changes successfully rolled back.
15:59:52.973 [main] ERROR Micronaut - Error starting Micronaut server: Bean definition [javax.sql.DataSource] could not be loaded: 
Migration V1__create-initial-schema.sql failed
----------------------------------------------
SQL State  : 3F000
Error Code : 0
Message    : ERROR: no schema has been selected to create in
  Position: 14
Location   : databasemigrations/V1__create-initial-schema.sql (/file:/organizational-agent.jar!/databasemigrations/V1__create-initial-schema.sql)
Line       : 1
Statement  : CREATE TABLE did_doc_web (
    id uuid PRIMARY KEY,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    did_doc jsonb,
    profile_json jsonb
)

[etschelp]

Please pull and try again, there was another property missing.

[matgnt]

Issue resolved. Thank you!

12:49:13.980 [main] INFO  VersionPrinter - Flyway Community Edition 6.5.5 by Redgate
12:49:14.278 [main] INFO  DatabaseFactory - Database: jdbc:postgresql://postgres/bpa (PostgreSQL 12.4)
12:49:14.369 [main] INFO  Schema - Creating schema "8a7acead8d62" ...
12:49:14.379 [main] INFO  JdbcTableSchemaHistory - Creating Schema History table "8a7acead8d62"."flyway_schema_history" ...
12:49:15.318 [main] INFO  DbMigrate - Current version of schema "8a7acead8d62": null
12:49:15.353 [main] INFO  DbMigrate - Migrating schema "8a7acead8d62" to version 1 - create-initial-schema
12:49:16.765 [main] INFO  DbMigrate - Successfully applied 1 migration to schema "8a7acead8d62" (execution time 00:01.453s)
12:49:17.161 [pool-2-thread-1] INFO  DBOperations - Running startup database operations.