https://linen.dev logo
Join Slack
Powered by
Hey guys, we’re using helm to deploy airbyte open ...
# replication-troubleshooting
o
Hey guys, we’re using helm to deploy airbyte open source on k8s and we’re trying to spin up and leverage external DBs for the webapp/UI. I see how to pass the SSL flag in the docs for a Docker deployment, but don’t see anything identical for kubernetes+helm. Anyone run into this before?
Copy code
Unable to start server: sql schema version compatibility check failed: pq: no pg_hba.conf entry for host "10.13.34.11", user "airbyte", database "temporal", no encryption.
j
Hey! Did you get any success with this?
n
not really - any advice here?
j
I’m trying to configure the DATABASE_URL in values.yaml, like so: (doing the same for temporal, worker, webapp..) server: secrets: DATABASE_URL: ‘${pg_conn_ssl_jdbc}’ CONFIG_DATABASE_URL: ‘${pg_conn_ssl_jdbc}’
no success yet
o
we’re currently playing around with extra env vars to see if it’ll work
Copy code
temporal:
          extraEnv:
            - name: SQL_TLS 
              value: true
j
🙏 1
o
Yeah! That’s actually where we got the idea to use those flags. We were going to try with just SQL_TLS, but I guess we’ll go ahead and leverage both
d
Hi, did you manage to solve it? I was struggling with the same issue a few days ago, and it did not work for me with ssl connection to an external postgres
o
Still waiting for our sync to complete, I’ll update here with a success or failure when that completes! We use Argo and I have limited access so forcing a sync is a bit of a convoluted process 😅
d
Ok, thank you for your response! Would be interesting to hear if it works on your side.
🫡 1
o
hey sorry for the delayed response (blame argentina v. croatia). that ended up working for us!
Copy code
temporal:
          image:
            tag: "1.9.2"
          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"
🙏 1
that’s the final config we used to modify temporal and resolve the issue
d
Perfect, anything else that you set up in addition to that?
Did you deploy it using kustomize or helm?
o
not really, everything else i would assume you already have set up (everything within the same vpc, passing in the correct secret to the global: database, etc)
our airbyte deployment is via helm on revision 0.42.4
but that piece is via kustomize
specifically a patch file
d
I think I tried to do the same yesterday, but it did not work for me. Let me try to do it quickly in the coming hours and come back to you if I have any issue
👍 1
I am really happy to hear that it worked for you! It really gives me some hope 🙂
o
yeah, hope it works for you too!
🙏 1
d
Omar, I am one more step forward! But still have some problem now with worker. Do you connect to PostgreSQL using a connection pool?
o
Yes, but we use a managed service called Aiven
What’s the error you’re seeing?
d
I also use a managed service from DigitalOcean. So if I connect without pool, the worker fails with 
Message: Failed to initialize pool: FATAL: remaining connection slots are reserved for non-replication superuser connections
. If I connect using a pool, when I install the helm chart, I get: 
Error: failed pre-install: pod airbyte-airbyte-bootloader failed
, and if I check logs, I can see this error:
Copy code
2022-12-13 22:03:14 INFO o.f.c.i.l.s.Slf4jLog(info):49 - Flyway Community Edition 7.14.0 by Redgate
Exception in thread "main" org.flywaydb.core.internal.exception.FlywaySqlException: Unable to determine the original schema for the connection
----------------------------------------------------------
SQL State  : 42P05
Error Code : 0
Message    : ERROR: prepared statement "S_2" already exists
Any idea what is going on?
o
when standing up your postgresdb do you have an option to set your 
pooling_mode:"session"
d
Yes, before I had it set to 
Transaction
as recommended by DigitalOcean. Why would it matter? I try to redeploy now
Wow it works, let's see if all pods are running now!
o
nice!
d
Hmm, now somehow it tells me that 
Unable to start server. Error: sql schema version compatibility check failed: pq: no such database: temporal
even though I have created it... Checking permissions now
Thank you a lot for your help! I highly appreciate it!
o
no problem! for us we pre-created both 
temporal
and 
temporal_visibility
and just ensured that the db user being passed had create permission in all of the dbs
also for why 
session
instead of 
transaction
i’m going based off of the aiven docs:
Copy code
Several PostgreSQL features, described in the official PgBouncer features page, are known to be broken by the default transaction-based pooling and must not be used by the application when in this mode.

You must carefully consider the design of the client applications connecting to PgBouncer, otherwise the application may not work as expected.
i believe it’s specific to SET commands not working
d
I see, good to know! Thank you for clarifying!
o
no problem, full disclosure i’m not an expert in anything so i could also be totally wrong haha
d
Haha, if you say that you are not an expert, likely you are an expert 🙂
How do you actually initialise the user and databases? I do the following, but it seems that it does not work:
Copy code
CREATE USER airbyte_admin_user WITH PASSWORD 'PASSWORD';
ALTER ROLE airbyte_admin_user SET client_encoding TO 'utf8';
ALTER ROLE airbyte_admin_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE airbyte_admin_user SET timezone TO 'UTC';

CREATE DATABASE airbyte;
GRANT ALL PRIVILEGES ON DATABASE airbyte TO airbyte_admin_user;

CREATE DATABASE temporal;
GRANT ALL PRIVILEGES ON DATABASE temporal TO airbyte_admin_user;

CREATE DATABASE temporal_visibility;
GRANT ALL PRIVILEGES ON DATABASE temporal_visibility TO airbyte_admin_user;
o
we’re actually using terraform to stand up our databases in aiven, but i imagine it should have the same effect
when you say it does not work, do you mean that you fail to create the DB using the DDL above? or that those statements succeed but then when you try and access the DB in airbyte you get a failure?
if the DDL works and you see the database, but you’re getting the error above saying “no such database” you may want to try and test the connection manually. make sure you are passing in the correct host, port, etc
also i will probably be signing off soon, but i am happy to continue this conversation tomorrow morning if you are still running into problems!
d
The DDL works and I can see the database as root user, but if I login as 
airbyte_admin_user
, I cannot see the databases.
Yes, for me it is also quite late already, but I would like to try to finish it still today. I feel I am very close to have it done 🙂
Ok, I found this statement: 
Database: Choose the database that you want to connect to. Each pool can only connect to a single database.
But in our case, we need to make sure that we can connect through a pool to 3 databases: 
airbyte
, 
temporal
, and 
temporal_visibility
. How did you address it?
j
Hey @Omar Mayar, it’s working! Thank you so so much! (what a show messi put on yesterday huh? ⚽)
✅ 1
@Denis Samuylov you are using a custom db script? The error is on a specific pod?
o
@Denis Samuylov looking at the docs for the product you’re using i believe you should be able to spin up a single pool and then attach it to multiple dbs?
can you confirm if you are or are not able to do that?
j
@Omar Mayar you are using datadog with your deployment by any chance?
o
yup!
d
@Jhonatas Kleinkauff sorry for the delay, I was on some meetings all day. What do you mean by a custom db script? I use helm chart with custom values where I specify how to connect to MinIO and a managed instance of PostgreSQL. I think all the pods that require connection to DB are failing or not starting. I have something like this:
Copy code
airbyte-airbyte-bootloader                          0/1     Completed   0             50s
airbyte-connector-builder-server-7fcb5d9c6b-t5wpl   1/1     Running     0             29s
airbyte-cron-6884f9bb5c-fbj7t                       1/1     Running     0             29s
airbyte-pod-sweeper-pod-sweeper-77d99c9548-5zb4m    0/1     Running     0             29s
airbyte-server-679c55df86-htrdz                     0/1     Running     0             29s
airbyte-temporal-6b8cf5fc45-wtzlm                   0/1     Error       2 (18s ago)   29s
airbyte-webapp-6778dcf8bb-c6htx                     0/1     Running     0             29s
airbyte-worker-7c8ff98db-cwvng                      0/1     Running     0             29s
And in the logs of the temporal pod I can see that 
Unable to start server. Error: sql schema version compatibility check failed: pq: no such database: temporal
. Even though the 
temporal
database exists, but not available to the user if I connect through the pool.
@Omar Mayar I am checking the link that you refer, but cannot find how is it possible to do... Could you elaborate on it? The day was long and maybe I just miss something obvious.
o
I’m sorry I don’t use DigitalOcean so I’m not sure on the implementation details either. Have you tried not manually running the DDL to initialize the 
temporal
DB and seeing what would happen if you let the helm deployment build it for you?
The only reason I say that is because of the workaround described here
d
I tried it and it also does not work with 
2022/12/15 21:27:33 error creating database:unable to connect to DB, tried default DB names: postgres,defaultdb
. It seems that it expects that the databases are pre-created.
o
i’m really sorry man, at this point i can’t really come up with any workaround for the problem you’re having
d
Hi man, no worries, you already helped me a lot and I highly appreciate it! I will try to figure out it a bit later... It is pity the process is so not straightforward...
🫡 1
o
no problem at all, hope you get it sorted!
🙏 1
m
Hello @Denis Samuylov, have you come up with any solution to your problem? Unfortunately, I’m facing similar issue too
d
Hello @Mateusz Kijewski, no I did not get any reply and I am still stuck with it. Let's maybe try to debug and figure it out together?
g
Hi @Denis Samuylov and @Mateusz Kijewski, it took me some time to figure out the reasons for this error and I found that it was related to the need to use an SSL connection to PostgreSQL. In order to get around this error, you need to add a few unusual environment variables that include ssl support in a container with temporal service.
Copy code
- name: SQL_TLS
   value: 'true'
- name: SQL_TLS_ENABLE
   value: 'true'
- name: SQL_TLS_DISABLE_HOST_VERIFICATION
   value: 'true'
- name: SQL_TLS_ENABLE
   value: 'true'
- name: SSL
   value: 'true'
After add that env’s connection was successful. Reference: https://github.com/temporalio/temporal/issues/2293# Also, I think it would be a good idea to add these variables to helm chart. What do you think, @Jhonatas Kleinkauff ?
j
If these variables were in the helm chart it would have done our setup a little faster. @Marcos Marx (Airbyte) Could We add those vars to the helm chart? - or maybe mention it somewhere.
m
@Jhonatas Kleinkauff would be nice to transfer this slack thread to a Github issue and probably add information to Helm Chart!
a
I add variables and receive error from airbyte-bootloader container
Copy code
2023-03-13 17:39:35 INFO i.a.d.c.DatabaseAvailabilityCheck(lambda$isDatabaseConnected$1):75 - Testing airbyte configs database connection...
2023-03-13 17:40:05 ERROR i.a.d.c.DatabaseAvailabilityCheck(lambda$isDatabaseConnected$1):78 - Failed to verify database connection.
org.jooq.exception.DataAccessException: Error getting connection from data source HikariDataSource (HikariPool-2)
        at org.jooq_3.17.8.POSTGRES.debug(Unknown Source) ~[?:?]
        at org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:90) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.DefaultExecuteContext.connection(DefaultExecuteContext.java:560) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:296) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:301) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:322) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:2861) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.ResultQueryTrait.fetchOne(ResultQueryTrait.java:509) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.Tools.attach(Tools.java:1526) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.DefaultDSLContext.fetchOne(DefaultDSLContext.java:4662) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.DefaultDSLContext.lambda$fetchValue$49(DefaultDSLContext.java:4682) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.Tools.attach(Tools.java:1526) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.DefaultDSLContext.fetchValue(DefaultDSLContext.java:4682) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.DefaultDSLContext.fetchValue(DefaultDSLContext.java:4696) ~[jooq-3.17.8.jar:?]
        at org.jooq.impl.DefaultDSLContext.fetchExists(DefaultDSLContext.java:4778) ~[jooq-3.17.8.jar:?]
        at io.airbyte.db.check.DatabaseAvailabilityCheck.lambda$isDatabaseConnected$0(DatabaseAvailabilityCheck.java:76) ~[io.airbyte.airbyte-db-db-lib-0.42.0.jar:?]
        at io.airbyte.db.Database.query(Database.java:23) ~[io.airbyte.airbyte-db-db-lib-0.42.0.jar:?]
        at io.airbyte.db.check.DatabaseAvailabilityCheck.lambda$isDatabaseConnected$1(DatabaseAvailabilityCheck.java:76) ~[io.airbyte.airbyte-db-db-lib-0.42.0.jar:?]
        at io.airbyte.db.check.DatabaseAvailabilityCheck.check(DatabaseAvailabilityCheck.java:47) ~[io.airbyte.airbyte-db-db-lib-0.42.0.jar:?]
        at io.airbyte.db.init.DatabaseInitializer.initialize(DatabaseInitializer.java:45) ~[io.airbyte.airbyte-db-db-lib-0.42.0.jar:?]
        at io.airbyte.bootloader.Bootloader.initializeDatabases(Bootloader.java:200) ~[io.airbyte-airbyte-bootloader-0.42.0.jar:?]
        at io.airbyte.bootloader.Bootloader.load(Bootloader.java:101) ~[io.airbyte-airbyte-bootloader-0.42.0.jar:?]
        at io.airbyte.bootloader.Application.main(Application.java:22) ~[io.airbyte-airbyte-bootloader-0.42.0.jar:?]
Caused by: java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 30000ms.
        at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100) ~[HikariCP-5.0.1.jar:?]
        at org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:87) ~[jooq-3.17.8.jar:?]
        ... 21 more
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.0.jar:42.5.0]
        at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2788) ~[postgresql-42.5.0.jar:42.5.0]
        at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:174) ~[postgresql-42.5.0.jar:42.5.0]
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:290) ~[postgresql-42.5.0.jar:42.5.0]
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.5.0.jar:42.5.0]
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:247) ~[postgresql-42.5.0.jar:42.5.0]
        at org.postgresql.Driver.makeConnection(Driver.java:434) ~[postgresql-42.5.0.jar:42.5.0]
        at org.postgresql.Driver.connect(Driver.java:291) ~[postgresql-42.5.0.jar:42.5.0]
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:733) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:712) ~[HikariCP-5.0.1.jar:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
        at java.lang.Thread.run(Thread.java:1589) ~[?:?]
2023-03-13 17:40:05 INFO i.a.d.c.DatabaseAvailabilityCheck(check):49 - Database is not ready yet. Please wait a moment, it might still be initializing...
2023-03-13 17:40:11 INFO i.m.r.Micronaut(lambda$start$0):115 - Embedded Application shutting down
Do you have any idea why this might happen?
o
most likely an issue with your max connections and/or max pool size
basically the number of available connections are so low that you’re trying to use one of the default reserved superuser connection slots in the above scenario
a
Thank you, I recreate new DigitalOcean Database Cluster with more connection and it worked!
✅ 1
p
Hi All, maybe you guys can help me. I trying to deploy Airbyte on Kubernetes using official helm chart. also I have used RDS as external database and stuck with the Temporal with following error messages.
Copy code
[Fx] ERROR              Failed to initialize custom logger: could not build arguments for function "<http://go.uber.org/fx|go.uber.org/fx>".(*App).constructCustomLogger.func2
        /go/pkg/mod/go.uber.org/fx@v1.18.2/app.go:414:
failed to build fxevent.Logger:
could not build arguments for function "<http://go.temporal.io/server/temporal|go.temporal.io/server/temporal>".glob..func8
        /home/builder/temporal/temporal/fx.go:1025:
failed to build log.Logger:
received non-nil error from function "<http://go.temporal.io/server/temporal|go.temporal.io/server/temporal>".ServerOptionsProvider
        /home/builder/temporal/temporal/fx.go:159:
sql schema version compatibility check failed: unable to read DB schema version keyspace/database: spw_airbyte_temporal error: pq: relation "schema_version" does not exist
Unable to create server. Error: could not build arguments for function "<http://go.uber.org/fx|go.uber.org/fx>".(*App).constructCustomLogger.func2 (/go/pkg/mod/go.uber.org/fx@v1.18.2/app.go:414): failed to build fxevent.Logger: could not build arguments for function "<http://go.temporal.io/server/temporal|go.temporal.io/server/temporal>".glob..func8 (/home/builder/temporal/temporal/fx.go:1025): failed to build log.Logger: received non-nil error from function "<http://go.temporal.io/server/temporal|go.temporal.io/server/temporal>".ServerOptionsProvider (/home/builder/temporal/temporal/fx.go:159): sql schema version compatibility check failed: unable to read DB schema version keyspace/database: spw_airbyte_temporal error: pq: relation "schema_version" does not exist.
I have set these values in 
values.yaml
Copy code
temporal:
  enabled: true
  image:
    repository: temporalio/auto-setup
    pullPolicy: IfNotPresent
    tag: "1.20.1"

  extraEnv:
    - name: DBNAME
      value: "spw_airbyte_temporal"
    - name: VISIBILITY_DBNAME
      value: spw_airbyte_temporal_visibility
    - name: SQL_TLS
      value: "true"
    - name: SQL_TLS_ENABLE
      value: "true"
    - name: SQL_TLS_DISABLE_HOST_VERIFICATION
      value: "true"
    - name: SQL_TLS_ENABLE
      value: "true"
    - name: SSL
      value: "true"
this 1
a
I am running into ☝️ as well with the same configuration. Anyone know how we can resolve it?
p
@Alex Drawbond I can solve this problem. here is the configuration that I used. I have checked the source code from temporal’s GitHub and tried with the 
SKIP_DB_CREAT
variable then it works.
Copy code
extraEnv:
    - name: DBNAME # TEMPORAL_DB
      value: "spw_airbyte_temporal"
    - name: VISIBILITY_DBNAME # VISIBILITY_DB
      value: spw_airbyte_temporal_visibility
    - name: SKIP_DB_CREATE
      value: "true"
148 Views
Open in Slack
PreviousNext
Powered by