Helm Chart: make temporal and temporal_visibility databases configurable

[EinavDanielDX]

Tell us about the problem you're trying to solve

I'm willing to use the same postgresql instance/RDS for multiple Airbyte deployments.
To achieve this I have to preconfigure the databases such as airbte_db_1 airbte_db_2.
But - its not the only dependency, Airbyte deployment requires temporal_visibility and temporal databases as well ,but I can't preconfigure these when using helm chart deployment.

Describe the solution you’d like

I'd like to have configuration sections for these databases just like the configuration for externaldatabase:

externalTemporalDatabase:
  host: localhost
  user: 
  password: ""
  existingSecret: ""
  existingSecretPasswordKey: ""
  database: airbyte_temporal_1
  port: 

externalTemporalVisibilityDatabase:
  host: localhost
  user: 
  password: ""
  existingSecret: ""
  existingSecretPasswordKey: ""
  database: airbyte_temporal_vis_1
  port: 

[leehuwuj]

I get the same issue. It's not mentioned in the document for deployment.
I tried to create temporal_visibility and temporal and make it available for airbyte user but the temporal still stuck in creating database and keep restarting.

LOG

Done init

PostgreSQL started.

2022-03-19T12:51:56.640Z ERROR Unable to create SQL database. {"error": "pq: permission denied to create database", "logging-call-at": "handler.go:97"}

+ temporal-sql-tool --plugin postgres --ep host.docker.internal -u airbyte -p 5432 --db temporal setup-schema -v 0.0

2022-03-19T12:51:56.684Z INFO Starting schema setup {"config": {"SchemaFilePath":"","InitialVersion":"0.0","Overwrite":false,"DisableVersioning":false}, "logging-call-at": "setuptask.go:57"}

2022-03-19T12:51:56.684Z DEBUG Setting up version tables {"logging-call-at": "setuptask.go:67"}

2022-03-19T12:51:56.689Z ERROR Unable to setup SQL schema. {"error": "pq: relation \"schema_version\" already exists", "logging-call-at": "handler.go:57"}

+ temporal-sql-tool --plugin postgres --ep host.docker.internal -u airbyte -p 5432 create --db temporal_visibility

2022-03-19T12:51:56.738Z ERROR Unable to create SQL database. {"error": "pq: permission denied to create database", "logging-call-at": "handler.go:97"}

+ temporal-sql-tool --plugin postgres --ep host.docker.internal -u airbyte -p 5432 --db temporal_visibility setup-schema -v 0.0

2022-03-19T12:51:56.792Z INFO Starting schema setup {"config": {"SchemaFilePath":"","InitialVersion":"0.0","Overwrite":false,"DisableVersioning":false}, "logging-call-at": "setuptask.go:57"}

2022-03-19T12:51:56.792Z DEBUG Setting up version tables {"logging-call-at": "setuptask.go:67"}

2022-03-19T12:51:56.797Z ERROR Unable to setup SQL schema. {"error": "pq: relation \"schema_version\" already exists", "logging-call-at": "handler.go:57"}

+ echo 'Starting to update the temporal DB'

+ temporal-sql-tool --plugin postgres --ep host.docker.internal -u airbyte -p 5432 --db temporal update-schema -d /etc/temporal/schema/postgresql/v96/temporal/versioned

[EinavDanielDX]

Hi @leehuwuj,
Try setup your database somewhat like this:

    CREATE USER airbyte with password 'airbyte';
    
    GRANT airbyte TO postgres;

    CREATE DATABASE airbyte
    WITH 
    OWNER = airbyte
    ENCODING = 'UTF8';

    CREATE DATABASE temporal_visibility
    WITH 
    OWNER = airbyte
    ENCODING = 'UTF8';

    CREATE DATABASE temporal
    WITH 
    OWNER = airbyte
    ENCODING = 'UTF8';

and then if you are using helm chart,
create a secret

kubectl create secret generic my-airbyte-db \
    --from-literal=postgresql-password="airbyte"

and configure the database settings somewhat like this:

 postgresql:
    enabled: false
 externalDatabase:
    host: my-postgresql-host
    user: airbyte
    existingSecret: "my-airbyte-db"
    existingSecretPasswordKey: "postgresql-password"
    database: airbyte
    port: 5432

[leehuwuj]

@EinavDanielDX I set owner for airbyte user to those 3 tables and it's able to run successfully. I see your setup also grant permission to postgres database, is there any reason for that?

[EinavDanielDX]

Nothing that is relevant to Airbyte setup, just part of my testing environment

[EinavDanielDX]

After some investigating I've found out that the temporal is using this script

So, in order to control Airbyte temporal DB you may edit the values this way

temporal:
  extraEnv:
    - name: DBNAME
      value: "my_temporal"
    - name: VISIBILITY_DBNAME
      value: "my_temporal_visibility"

would be great if these were used as regular values and injected into the temporal deployment instead of using 'extraEnv'