Hi 👋 How could I specify extra parameters for connection to an external database using helm charts?

Hi i hope this helps https://docs.airbyte.com/deploying-airbyte/on-kubernetes-via-helm/#database-external-secrets

Unfortunately it didn't help. Maybe I wasn't clear enough. I want to use an external config/temporal/temporal_visibility databases. I have managed to configure them in the helm chart via

global.database.secretName = 'airbyte-my-pg-secret'
global.database.secretValue = 'pg_pass'

temporal.env_vars.VISIBILITY_DBNAME = 'my-visibility'
temporal.env_vars.DBNAME = 'my-temporal'

postgresql.enabled = false
externalDatabase.host = 'my-host'
externalDatabase.user = 'my-user'
externalDatabase.database = 'db'
externalDatabase.port = '5432

It translates to valid connection strings (e.g.

jdbc:<postgresql://my-host>:my-port/<db>

, but I want it to be like

jdbc:<postgresql://my-host>:my-port/<db>?<extra_params>

. And the question is about those extra params.

Hi, Igor! I'm still new to temporal, so will ask my team for input if this doesn't help: I found an issue where users are having what looks like the same problem https://github.com/airbytehq/airbyte/issues/11157 While it looks like it's currently not possible to make temporal and temporal_visibility databases configurable with Helm charts there's a workaround: https://github.com/airbytehq/airbyte/issues/11157#issuecomment-1090223202 Let me know if this helps

It fits partly. I managed to set up airflow services via

DATABASE_URL

env variable. And it is quite painful to do for temporal service. I worked some problems out via env variables (how it is described above), but have found new ones 🙂 . They are mostly about temporal.io and the fact that airbyte uses old temoral setup docker image.

Could you elaborate on what other ones you've found? This is badly documented for now, unfortunately, but we're trying to make it better 🙂

Originally my task is to set up external database that is hidden behind pg_bouncer with ssl/tls and transaction pooling. Not sure if it helps anybody (because I am still in progress).

postgresql:
  enabled: false  # use external

externalDatabase:
  host: "${pg_host}"
  user: "${pg_user}"
  existingSecret: pg-secret  # gets ignored
  existingSecretPasswordKey: "pg_pass"  # gets ignored
  database: "${pg_db_main}"
  port: "${pg_port}"

global:
  database:
    # because externalDatabase secret ignored, need to set it up here
    secretName: secret
    secretValue: "pg_pass"

all services (not sure if it can't go to global, for some reason it is in this way in my code).

env_vars:
    DATABASE_URL: "jdbc:postgresql://${pg_host}:${pg_port}/${pg_db}?prepareThreshold=0"

If you set

env_vars

anywhere you need to set them in globals like

DUMMY: ""

or you will get an error (something about types). temporal

env_vars:
    VISIBILITY_DBNAME: "${pg_db_temporal_visibility}"
    DBNAME: "${pg_db_temporal}"
    DB_PORT: "${pg_port}" 
    SKIP_DB_CREATE: "true"
    SQL_TLS: "true"  # for migrations
    SQL_TLS_ENABLED: "true"  # for server
    SQL_CONNECT_ATTRIBUTES: "binary_parameters=yes"  # to work around prepared statements in transaction pooling mode

I still can't understand about DB_PORT. If I do not set it,

externalDatabase.port

is ignored, if I set it, DB_PORT gets duplicated in k8s deployment (in my setup it is duplicated).

SKIP_DB_CREATE

is needed if you create database in advance. This flag is absent in airbyte/temporal-auto-setup image for now but present in temoral.io So it is still not working, because

SQL_CONNECT_ATTRIBUTES

work only for temporal-sql-tool , and for server you need to specify it in temporal config (which is not supported yet, so right now I am figuring out how to change that config...)

To make sure I understand: everything comes down to being able to edit the temporal config, and this is currently not supported. You were able to make this work partially with your own fix but are still getting an error from the temporal server that binds on IPv6. Is this correct?

In general case - yes. But I encountered some additional problems, and I would still need to build my own

temporal

image even if that issue is solved. I want to try explain it in more structural format. Firstly about db connection. The problems are: • there is no explicit way to configure parameters for connection URL for airbyte-db. One cat work aroud it using

DATABASE_URL

environment variable. • in the issue above, there is no solution for connection parameters at all. One can implement them using

SQL_CONNECT_ATTRIBUTES

, but this setting works only for temporal database/schema setup. Temporal server does not use it, and the user must specify them in the config (example). But some parameters (e.g. sslmode) can be passed through environment variables. • if temporal database already exists, you can get an error (airbyte uses this version of setup). "Temporal" have made it configurable, but airbyte uses old forked temporal image. And about IPv6. "Temporal" server listens to

BIND_ON_IP

(set up here in newest version). And it is IPv6 in my case. I solved it by changing

hostname

->

hostname -i

and using my own image in helm chart.

Thank you so much for this detailed write up, I really appreciate it! Would you be able to create a feature request on GitHub? Then I'd bring it to the appropriate team and see if implementing changes regarding this is something that can be put on the roadmap!

I created a feature request about SKIP_DB_CREATE flag (because it is easy to implement). SSL can be worked around. Transaction pooling - not widespread and a bit dirty to implement. Also it is not widespread. Users can set up their bouncer with session pooling.

Thanks so much! Could you link it? Then I'll make sure it gets prioritized as much as possible 🙂

here it is https://github.com/airbytehq/airbyte/issues/22149

Thanks!