Help setting up Airbyte and using Kubernetes EKS

andrew · July 20, 2024, 3:13am

I’m trying to setup Airbyte using Helm on Qovery.

I’ve been following the Airbyte guides and i’ve got to the step where I need to setup the external database connection.

The guide shows the database-password being added to a kubernetes secret airbyte-config-secrets. and not added to the values.yaml override.

I’ve tested trying to include it in values and i’m getting the following error on deploy:

Unable to create SQL database. {“error”: "unable to connect to DB, tried default DB names: postgres,defaultdb…

Any help on how to set this up with Qovery will be great!

Link to your application/environment

rophilogene · July 20, 2024, 1:09pm

Hi @andrew .

I tried looking at your URL, but it no longer exists. Do you have another link? Did you succeed in making it work?

Best,

celvin · July 20, 2024, 4:52pm

To set up Airbyte using Helm on Qovery and resolve the external database connection issue, follow these steps:

Create Kubernetes Secret: Store the database password in a Kubernetes secret.

bash

Copy code

kubectl create secret generic airbyte-config-secrets \
  --from-literal=db-password=<your-database-password>

Helm Values Configuration: Ensure values.yaml does not include the database password directly. Instead, reference the Kubernetes secret.
Configure Airbyte Deployment: Update the Helm chart to use the secret:

yaml

Copy code

secrets:
  dbPassword:
    secretName: airbyte-config-secrets
    key: db-password

For more detailed guidance, refer to the Airbyte deployment guide.
Celvin Data Expert

rophilogene · July 20, 2024, 5:34pm

@celvin - thanks for participating; however, there is no need to use kubectl to install and set up Airbyte with Qovery.

–

andrew · July 20, 2024, 8:51pm

Thanks for coming back to me. I still don’t have it running yet.

I got a deployment to run without using an external database for now (i’ll come back to that soon).

The last issue i’m facing is setting up the networking/ proxy to access the webapp. Here’s the service i’ve setup: https://console.qovery.com/organization/e2c6428a-a4c9-4737-a4e1-ecfcdcf51504/project/4f869621-42a1-454e-a716-2542c53b6f68/environment/1f7cef2d-ad5d-4be7-b5d1-f3e1d9d8c059/application/5ab8b04b-d115-4ad9-b1b9-1e1f3bc4a6b6/general

rophilogene · July 21, 2024, 7:30am

Hi @andrew ,

I did look at your configuration, and you specified server as the service to be exposed publicly. But from what I see in the deployment logs, the service webapp seems to be the service that exposes port 8080.

To make it work, the complete service name should be indicated as helm-z5ab8b04b-ab-test-webapp with port 80 (not the port 8080) (default webapp exposed port ~ I found it on the default Helm Values file).

CleanShot 2024-07-21 at 08.54.50

You might ask how I found the service name. I went to the overview page and looked at the pod with a name that included webapp.

I copied the pod name helm-z5ab8b04b-ab-test-webapp-64fb5784db-jvf6f by clicking on it, and I kept only:

helm-z5ab8b04b-ab-test-webapp

and I added the -svc suffix.

helm-z5ab8b04b-ab-test-webapp-svc

Then you redeploy and tada

We are improving this part. We know that exposing a service from a Helm deployment is error-prone and could be more accessible. We’re working on it!

–

By the way, I see you didn’t override the default values file. I guess it’s on purpose, but to configure your external database, you will need to do this:

and then you can add your custom values on the left panel if you need to

For instance, this: (I didn’t try the conf)

postgresql:
  # Disable built in postgres database
  enabled: false

# External PostgreSQL configuration, All of these values are only used when postgresql.enabled is set to false
externalDatabase:
  # -- Database host
  host: qovery.env.POSTGRES_HOST
  # -- non-root Username for Airbyte Database
  user: qovery.env.POSTGRES_USER
  # -- Database password
  password: qovery.env.POSTGRES_PASSWORD
  # -- Database name
  database: qovery.env.POSTGRES_DB_NAME
  # -- Database port number
  port: qovery.env.POSTGRES_PORT

As you can see, qovery.env.XXX are environment variables that you set in Qovery that can be used in your Helm values file

andrew · July 21, 2024, 9:45am

Hi @rophilogene

Thanks so much for jumping in and having a look at my setup.

Networking Issue

In a previous iteration of the deployment I had everything configured as you suggested, except I had the service to be exposed publicly as webapp and not helm-z5ab8b04b-ab-test-webapp-svc

It was the tooltip in the screenshot below that lead me down the route of using the helm chart name webapp and not deriving it from the pod name/ namespaced service name.

I understand why what you’ve done works now though. My only question is about why you dropped everything after helm-z5ab8b04b-ab-test-webapp and added -svc?

Database Issue

I was testing using an external database with a different Airbyte service. Here’s a link to that service

I have the following setup in my values override

postgresql:
  enabled: false

global:
  database:
    # -- The database host
    host: qovery.env.DATABASE_HOST
    # -- The database port
    port: qovery.env.DATABASE_PORT
    # -- The database name
    database: qovery.env.DATABASE_NAME
    # -- The database user
    user: qovery.env.DATABASE_USER
    # -- The database password
    password: qovery.env.DATABASE_PASSWORD

  # -- Temp fix for temporal issue
  temporal:
    image:
      tag: "1.20.1"
    extraEnv:
      - 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"

  storage:
    type: "S3"
    bucket:
      log: <bucket obfuscated>
      state: <bucket obfuscated>
      workloadOutput: <bucket obfuscated>
    s3:
      region: "ap-southeast-2"
      authenticationType: instanceProfile

externalDatabase:
  jdbcURL: qovery.env.DATABASE_URL

I added the global.temporal and externalDatabase config based on some Airbyte topics and the error logs, but I think those are red herrings.

The temporal service isn’t coming up because of this error:

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 \"<IP>", user \"qoveryadmin\", database \"postgres\", no encryption pq: no pg_hba.conf entry for host \"<IP>\", user \"qoveryadmin\", database \"defaultdb\", no encryption]", "logging-call-at": "handler.go:94"}

Trying your suggested config didn’t work either and came up with the error:

helm.go:84: [debug] execution error at (airbyte/templates/secret.yaml:13:6): You must set `global.database.user` when using an external database

Feel free to jump into this service and have a look. I just deleted the ab-test service as it’s polluting the global namespace.

rophilogene · July 21, 2024, 10:46am

Hi @andrew , my pleasure

Networking Issue

That’s a good question, and to be honest, it’s not super obvious, which is why our team will work to make it simple. Today, you have to “guess” and look at the services running on your cluster, which is obviously not great at all and not the experience we want to provide.

I put in cc @a_carrano (Lead PM) @Julien_Dan (Technical PM).

Database Issue

I see that Airbyte has 2 database configuration objects:

global.database: `
- global.database.user is properly set
externalDatabase: # External PostgreSQL configuration, All of these values are only used when postgresql.enabled is set to false
- externalDatabase.jdbcURL is set (and includes the user).

Could you try to remove the global.database.* definition and only let the externalDatabase.jdbcURL one? Which seems to be the DB configuration for all Airbyte services.

I am not sure how Airbyte should be configured but I’m looking on my side in the meantime (I’m going to try to deploy it on my end).

andrew · July 21, 2024, 11:01am

@rophilogene just a quick reply to say that I’d already tried removing the global.database.* definition.

The below error occurs:

helm.go:84: [debug] execution error at (airbyte/templates/secret.yaml:13:6): You must set global.database.user when using an external database

Here’s the Airbyte guide I was using: External Database | Airbyte Documentation

rophilogene · July 21, 2024, 12:31pm

The error I see is the following:

io.grpc.netty.shaded.io.netty.channel.AbstractChannel$AnnotatedConnectException: finishConnect(..) failed: Connection refused: helm-z93111b36-airbyte-temporal/172.20.111.207:7233

from helm-z93111b36-airbyte-worker-76b765b7cd-8jstf

Troubleshooting

This is what I’ve done to troubleshoot.

1. No Custom Values File (OK)

I removed the whole values file to validate that the base configuration works. And the default conf works.

2. Override Global Database (OK)

Then, I overrode the global database configuration to use the external DB available and deployed it with Qovery. (It worked)

postgresql:
  enabled: false

global:
  database:
    type: "external"
    # -- The database host
    host: qovery.env.DATABASE_HOST
    # -- The database port
    port: qovery.env.DATABASE_PORT
    # -- The database name
    database: qovery.env.DATABASE_NAME
    # -- The database user
    user: qovery.env.DATABASE_USER
    # -- The database password
    password: qovery.env.DATABASE_PASSWORD

3. Override Global Database + S3 Bucket (OK)

This conf also worked:

postgresql:
  enabled: false

global:
  database:
    type: "external"
    # -- The database host
    host: qovery.env.DATABASE_HOST
    # -- The database port
    port: qovery.env.DATABASE_PORT
    # -- The database name
    database: qovery.env.DATABASE_NAME
    # -- The database user
    user: qovery.env.DATABASE_USER
    # -- The database password
    password: qovery.env.DATABASE_PASSWORD

  storage:
    type: "S3"
    bucket:
      log: <truncated>
      state: <truncated>
      workloadOutput: <truncated>
    s3:
      region: "ap-southeast-2"
      authenticationType: instanceProfile

But I saw a non-critical error from your S3 auth:

Cannot publish to S3: User: arn:aws:sts::[TRUNCATED]:assumed-role/qovery-eks-workers-zXXX/i-XXX is not authorized to perform: kms:GenerateDataKey on resource: arn:aws:kms:ap-southeast-2:...

4. Override Global Database + S3 Bucket + externalDatabase

This one also did work

postgresql:
  enabled: false

global:
  database:
    type: "external"
    # -- The database host
    host: qovery.env.DATABASE_HOST
    # -- The database port
    port: qovery.env.DATABASE_PORT
    # -- The database name
    database: qovery.env.DATABASE_NAME
    # -- The database user
    user: qovery.env.DATABASE_USER
    # -- The database password
    password: qovery.env.DATABASE_PASSWORD

  storage:
    type: "S3"
    bucket:
      log: <truncated>
      state: <truncated>
      workloadOutput: <truncated>
    s3:
      region: "ap-southeast-2"
      authenticationType: instanceProfile

externalDatabase:
  jdbcURL: qovery.env.DATABASE_URL

5. Temporal fix?

This configuration with the Temporal fix does not work.

postgresql:
  enabled: false

global:
  database:
    type: "external"
    # -- The database host
    host: qovery.env.DATABASE_HOST
    # -- The database port
    port: qovery.env.DATABASE_PORT
    # -- The database name
    database: qovery.env.DATABASE_NAME
    # -- The database user
    user: qovery.env.DATABASE_USER
    # -- The database password
    password: qovery.env.DATABASE_PASSWORD
  
  # -- Temp fix for temporal issue
  temporal:
    image:
      tag: "1.20.1"
    extraEnv:
      - 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"

  storage:
    type: "S3"
    bucket:
      log: <truncated>
      state: <truncated>
      workloadOutput: <truncated>
    s3:
      region: "ap-southeast-2"
      authenticationType: instanceProfile

externalDatabase:
  jdbcURL: qovery.env.DATABASE_URL

–

Why do you need to change the Temporal configuration? This seems to be the reason for the failed deployment.

andrew · July 22, 2024, 12:57am

@rophilogene I think there still might be an issue.

When I looked at that service, it still had a db pod running. This pod should not be running with an external database in use and it’s there because you started the service without a values.yaml override in an earlier deployment.

I tried shutting down the service as a way to kill the db pod, but this didn’t work (and I couldn’t find an easy way to stop pods from the UI). In the end I resulted to a bit of a dirty kill by cloning the service and deleting the old one.

The new service is here: https://console.qovery.com/organization/e2c6428a-a4c9-4737-a4e1-ecfcdcf51504/project/4f869621-42a1-454e-a716-2542c53b6f68/environment/1f7cef2d-ad5d-4be7-b5d1-f3e1d9d8c059/application/8effb4d2-d804-4511-98d6-45ed6c0c2fd8/general

I started this service with the exact same config as you had and it failed to deploy with the same temporal error as previous:

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 \"<IP>\", user \"qoveryadmin\", database \"postgres\", no encryption pq: no pg_hba.conf entry for host \"<IP>\", user \"qoveryadmin\", database \"defaultdb\", no encryption]", "logging-call-at": "handler.go:94"}

I think your setup was working because it had a db pod running that the temporal service was connecting to. I don’t think it was ever connecting to the external database.

The reason I put the Temporal configuration in place was trying to fix this issue above.

rophilogene · July 22, 2024, 7:44am

Ok @andrew, what I am going to do is cloning the current airbyte helm chart with Qovery and testing from a brand new service definition. I’ll keep you posted.

ps: I never had so many issues with one Helm chart But I am sure we will find out what’s going on.

rophilogene · July 22, 2024, 10:04am

From what I see - you’re right that there is something wrong with the temporal service. It’s the only one crashing now.

I’m still investigating to see if a conf is not exposed in the values file (Airbyte doesn’t provide a way to configure the DB of Temporal… I am not sure how it works tho)

rophilogene · July 22, 2024, 2:30pm

I got the following error from Temporal

[Fx] PROVIDE	*pprof.PProfInitializerImpl <= go.temporal.io/server/common/pprof.NewInitializer()
[Fx] PROVIDE	[]trace.SpanExporter <= go.temporal.io/server/temporal.init.func2()
[Fx] SUPPLY	[]temporal.ServerOption
[Fx] PROVIDE	fx.Lifecycle <= go.uber.org/fx.New.func1()
[Fx] PROVIDE	fx.Shutdowner <= go.uber.org/fx.(*App).shutdowner-fm()
[Fx] PROVIDE	fx.DotGraph <= go.uber.org/fx.(*App).dotGraph-fm()
[Fx] RUN	supply: stub([]temporal.ServerOption)
[Fx] RUN	provide: go.temporal.io/server/temporal.ServerOptionsProvider()
[Fx] Error returned: received non-nil error from function "go.temporal.io/server/temporal".ServerOptionsProvider
	/home/builder/temporal/temporal/fx.go:180:
sql schema version compatibility check failed: pq: no pg_hba.conf entry for host "10.0.78.221", user "qoveryadmin", database "temporal", no encryption
[Fx] ERROR		Failed to initialize custom logger: could not build arguments for function "go.uber.org/fx".(*module).constructCustomLogger.func2
	/go/pkg/mod/go.uber.org/fx@v1.20.0/module.go:251:
failed to build fxevent.Logger:
could not build arguments for function "go.temporal.io/server/temporal".init.func8
	/home/builder/temporal/temporal/fx.go:1029:
failed to build log.Logger:
received non-nil error from function "go.temporal.io/server/temporal".ServerOptionsProvider
	/home/builder/temporal/temporal/fx.go:180:
sql schema version compatibility check failed: pq: no pg_hba.conf entry for host "10.0.78.221", user "qoveryadmin", database "temporal", no encryption
Unable to create server. Error: could not build arguments for function "go.uber.org/fx".(*module).constructCustomLogger.func2 (/go/pkg/mod/go.uber.org/fx@v1.20.0/module.go:251): failed to build fxevent.Logger: could not build arguments for function "go.temporal.io/server/temporal".init.func8 (/home/builder/temporal/temporal/fx.go:1029): failed to build log.Logger: received non-nil error from function "go.temporal.io/server/temporal".ServerOptionsProvider (/home/builder/temporal/temporal/fx.go:180): sql schema version compatibility check failed: pq: no pg_hba.conf entry for host "10.0.78.221", user "qoveryadmin", database "temporal", no encryption.

So I looked around and found this thread

github.com/airbytehq/airbyte

Can't use Azure Database for PostgreSQL as Airbyte internal DB

opened 07:39PM - 17 Mar 23 UTC

closed 09:00AM - 20 Jun 24 UTC

detygon

type/bug area/platform community team/platform-enablement Stale

## Environment - **Airbyte version**: 0.42.0 - **OS Version / Instance**: Ubu…ntu 20.04 - **Deployment**: Docker - **Source Connector and version**: (if applicable example Salesforce 0.2.3) - **Destination Connector and version**: (if applicable example Postgres 0.3.3) - **Step where error happened**: Deploy ## Current Behavior I get the following error: Unable to start server. Error: sql schema version compatibility check failed: pq: no pg_hba.conf entry for host "X.X.X.X", user "example_user", database "temporal", no encryption ## Expected Behavior Airbyte deployed successfully. ## Logs Unable to start server. Error: sql schema version compatibility check failed: pq: no pg_hba.conf entry for host "X.X.X.X", user "example_user", database "temporal", no encryption ## Steps to Reproduce 1. Set Azure Database for PostgreSQL as Airbyte internal DB 2. 3. ## Are you willing to submit a PR? I can't.

And what suggested one person is to add those TLS parameters in the Values file:

temporal:
  extraEnv:
    - name: SQL_TLS
      value: "true"
    - name: SQL_TLS_ENABLED
      value: "true"

Which is partially working ( :D) because now I got the following error:

sql schema version compatibility check failed: pq: database "temporal" does not exist

Which is easily solvable with the following commands:

create database temporal;
grant all privileges on database temporal to airbyte;

create database temporal_visibility;
grant all privileges on database temporal_visibility to airbyte;

My full Values file looks like this:

postgresql:
  enabled: false

global:
  database:
    type: "external"
    # -- The database host
    host: qovery.env.DATABASE_HOST
    # -- The database port
    port: qovery.env.DATABASE_PORT
    # -- The database name
    database: qovery.env.DATABASE_NAME
    # -- The database user
    user: qovery.env.DATABASE_USER
    # -- The database password
    password: qovery.env.DATABASE_PASSWORD

  storage:
    type: "S3"
    bucket:
      log: [REDACTED]
      state: [REDACTED]
      workloadOutput: [REDACTED]
    s3:
      region: "ap-southeast-2"
      authenticationType: instanceProfile

externalDatabase:
  # -- The database host
  host: qovery.env.DATABASE_HOST
  # -- The database port
  port: qovery.env.DATABASE_PORT
  # -- The database name
  database: qovery.env.DATABASE_NAME
  # -- The database user
  user: qovery.env.DATABASE_USER
  # -- The database password
  password: qovery.env.DATABASE_PASSWORD
  # JDBC
  jdbcURL: qovery.env.JDBC_DATABASE_URL

temporal:
  extraEnv:
    - name: SQL_TLS
      value: "true"
    - name: SQL_TLS_ENABLED
      value: "true"

andrew · July 22, 2024, 11:04pm

@rophilogene thank you so much for persisting with this!

I’ll jump back in later this week and have a look properly, but it all looks to be working great.

Will you write this up as a guide somewhere? I’d also suggest reaching out to Airbyte to let them know that qovery is an option for people wanting to use Kubernetes in a more managed way. They are shutting down support for docker in August and there will be OSS customers looking to setup kubernetes clusters.

Thanks again.

rophilogene · July 23, 2024, 4:06am

My pleasure @andrew

Absolutely, I even plan to create a one command template installation of all of this with the temporal database creation included. I’ll share it here once it’s available (probably this week).

Do you have a link where they talk about this? I’ll definitely reach out the Airbyte team to see what we can do together.

andrew · July 23, 2024, 5:02am

@rophilogene

Here’s the github issue about it Deprecation of Docker Compose · airbytehq/airbyte · Discussion #40599 · GitHub

Airbyte is dropping support for docker compose from version 1.0 which releases in August. All production OSS deployments will need to use Kubernetes or abctl on a VM to get updates from then.

rophilogene · July 24, 2024, 8:51am

Hi @andrew , I am working on this repository containing the Template. I’ll explain in the README how to use it

andrew · July 25, 2024, 4:46am

@rophilogene this is great. Also good to know you can put basic auth in place easily.

rophilogene · July 25, 2024, 2:30pm

@andrew you might like this

Topic		Replies	Views
Airbyte 1.1.0 installation on Qovery managed cluster broke again Deployment	31	71	November 29, 2024
Database Connection Refused Questions and Answers qovery , aws	5	639	May 21, 2023
Installing New Relic using Qovery Helm container Documentation	2	362	March 25, 2024
Ssh'ing into kubernetes thru kubectl Questions and Answers	3	482	August 2, 2022
Backups of my environments databases Platform Engineering	2	351	March 25, 2024

Help setting up Airbyte and using Kubernetes EKS

Networking Issue

Database Issue

Networking Issue

Database Issue

Troubleshooting

1. No Custom Values File (OK)

2. Override Global Database (OK)

3. Override Global Database + S3 Bucket (OK)

4. Override Global Database + S3 Bucket + externalDatabase

5. Temporal fix?

Related topics