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'

[c-p-b]

Hi @EinavDanielDX is this still relevant?

[gasci]

Yes, it is still relevant. I am getting this error.

[CamiloAtencio]

Can we add an individual JDBC string for each too? - Im just thiking of a way to enable ssl on all the databases.

[lavData]

I follow your instruction @EinavDanielDX. But temporal keep determined create table temporal and temporal_visibility so I meet fail

│  temporal-sql-tool --plugin postgres --ep play-wfw-pgha-postgresql-ha-pgpool.sqldb.svc -u airbyte -p 5432 create --db temporal                              │
│ 2023-07-11T10:01:36.911Z    ERROR    Unable to create SQL database.    {"error": "pq: permission denied to create database", "logging-call-at": "handler.go │

[QuentinLuc]

I've got a similar problem and tested all the previous answers but none worked (ie.create airbyte, temporal and temporal_visibility with all privileges given to the airbyte user). I've also tested with creating no DB at all and I can see airbyte creates one but still get stuck with the temporal service. Here is the error message:

airbyte-temporal                  | 2023-07-12T10:19:40.227Z    ERROR   Unable to create SQL database.  {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: no pg_hba.conf entry for host \"172.16.1.68\", user \"airbyte_app\", database \"postgres\", no encryption pq: no pg_hba.conf entry for host \"172.16.1.68\", user \"airbyte_app\", database \"defaultdb\", no encryption]", "logging-call-at": "handler.go:97"}
airbyte-temporal                  | 2023-07-12T10:19:40.248Z    ERROR   Unable to connect to SQL database.  {"error": "pq: no pg_hba.conf entry for host \"172.16.1.68\", user \"airbyte_app\", database \"temporal\", no encryption", "logging-call-at": "handler.go:52"}
airbyte-temporal                  | 2023-07-12T10:19:40.286Z    ERROR   Unable to create SQL database.  {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: no pg_hba.conf entry for host \"172.16.1.68\", user \"airbyte_app\", database \"postgres\", no encryption pq: no pg_hba.conf entry for host \"172.16.1.68\", user \"airbyte_app\", database \"defaultdb\", no encryption]", "logging-call-at": "handler.go:97"}
airbyte-temporal                  | Starting to update the temporal DB
airbyte-temporal                  | 2023-07-12T10:19:40.350Z    ERROR   Unable to connect to SQL database.  {"error": "pq: no pg_hba.conf entry for host \"172.16.1.68\", user \"airbyte_app\", database \"temporal_visibility\", no encryption", "logging-call-at": "handler.go:52"}
airbyte-temporal                  | 2023-07-12T10:19:40.395Z    ERROR   Unable to connect to SQL database.  {"error": "pq: no pg_hba.conf entry for host \"172.16.1.68\", user \"airbyte_app\", database \"temporal\", no encryption", "logging-call-at": "handler.go:73"}

I noticed this line in the .env file but don't know which value to set other than the default one:

TEMPORAL_HOST=airbyte-temporal:7233

Any help would be much appreciated.

[yurivenancio30]

The solution to the problem is set this extra envs at the temporal: temporal: extraEnv:

• name: DBNAME value: "my_temporal"
• name: VISIBILITY_DBNAME value: "my_temporal_visibility"
• name: SKIP_DB_CREATE value: "true"

with this configuration, you can set a personalized database and jump the create data base problem.

[pjdufour]

How would I configure those settings with the docker deployment and the .env file?

[lschinasi]

@pjdufour I got this to work. I had to update the environment variables in the docker-compose.yaml for the airbyte-temporal service.

I added the following 4 rows to the environment variables:

      - SQL_TLS=true
      - SQL_TLS_DISABLE_HOST_VERIFICATION=true
      - SQL_TLS_ENABLED=true
      - SQL_HOST_VERIFICATION=false

here is what the airbyte-temporal service portion of the docker-compose.yaml now looks like:

  airbyte-temporal:
    image: airbyte/temporal:${VERSION}
    logging: *default-logging
    container_name: airbyte-temporal
    restart: unless-stopped
    environment:
      - DB=postgresql
      - DB_PORT=${DATABASE_PORT}
      - DYNAMIC_CONFIG_FILE_PATH=config/dynamicconfig/development.yaml
      - LOG_LEVEL=${LOG_LEVEL}
      - POSTGRES_PWD=${DATABASE_PASSWORD}
      - POSTGRES_SEEDS=${DATABASE_HOST}
      - POSTGRES_USER=${DATABASE_USER}
      - SQL_TLS=true
      - SQL_TLS_DISABLE_HOST_VERIFICATION=true
      - SQL_TLS_ENABLED=true
      - SQL_HOST_VERIFICATION=false
    volumes:
      - ./temporal/dynamicconfig:/etc/temporal/config/dynamicconfig
    networks:
      - airbyte_internal

let me know if this works for you!

[usama-shamim]

Hey everyone, I am also facing this same issue any one has got a fix for this? I have setup extraenv but still getting the error: Unable to create SQL database. {"error": "pq: permission denied to create database"

[lavData]

Hey everyone, I am also facing this same issue any one has got a fix for this? I have setup extraenv but still getting the error: Unable to create SQL database. {"error": "pq: permission denied to create database"

Because temporal config by Airbyte have actions to CREATE NEW DATABASE for Temporal services so u need use account has previlege or custom Airbyte-temporal. @usama-shamim

[ghost]

The solution to the problem is set this extra envs at the temporal: temporal: extraEnv: - name: DBNAME value: "my_temporal" - name: VISIBILITY_DBNAME value: "my_temporal_visibility" - name: SKIP_DB_CREATE value: "true"

with this configuration, you can set a personalized database and jump the create data base problem.

Thanks so much for this!

[raphaelauv]

temporal:
  extraEnv:
    - name: DBNAME
      value: "temporal"
    - name: VISIBILITY_DBNAME
      value: "temporal_visibility"
    - name: SKIP_DB_CREATE
      value: "true"
    - name: SQL_TLS
      value: "true"
    - name: SQL_TLS_DISABLE_HOST_VERIFICATION
      value: "true"
    - name: SQL_TLS_ENABLED
      value: "true"
    - name: SQL_HOST_VERIFICATION
      value: "false"