https://linen.dev logo
Join Slack
Powered by
Hi, I’m trying to connect my local Airbyte to a cu...
# contributing-to-airbyte
c
Hi, I’m trying to connect my local Airbyte to a custom job / config database that is running on my localhost, but when I try to start airbyte with docker-compose up, it keepks showing the message : “waiting for postgresql to start up”. Basically what I did was to change the database variables in the .env file, as follows, thanks!
Copy code
DATABASE_USER=postgres
DATABASE_PASSWORD=my_local_pwd
DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_DB=airbyte
DATABASE_URL=jdbc:<postgresql://localhost:5432/airbyte>
u
I think your database host is wrong. Using 
localhost
will make the Airbyte container look for something running on port 5432 inside the container itself, not the host/machine. Try using 
172.17.0.1
(Docker localhost) instead.
u
thanks ! @João Tavares
u
it working by setting the host to “host.docker.internal”, although now when I run the docker-compose up again, this error happens
Copy code
org.jooq.exception.DataAccessException: SQL [select * from airbyte_metadata where key = ?]; ERROR: relation "airbyte_metadata" does not exist
c
Do you have a compose file for your database?
u
no, but I found this doc: https://docs.airbyte.io/operator-guides/configuring-airbyte-db, here it mentions that I need to run a SQL script when using an external DB so I’m going to do that and see if it works
u
Okay! Good luck! 🙏
u
thanks!
u
it worked, thanks!
u
In the future we can automate the table creation for the jobs database. I created an issue here: https://github.com/airbytehq/airbyte/issues/4872
u
a thing I noticed is that there are only these databases I show in the screenshot, it is not supposed to have a configuration database also?
u
Previously the configs are stored on the docker volume, /data/config. We only recently migrated it to a database. But the changes have just been merged this week, and not included in any release yet. The next release will have that, and auto migrate all configs to the airbyte_configs table.
u
nice, thanks!
u
@Cristiano Sarmento can you point me to the SQL script that you mentioned? we are trying to move to an external DB also but the config tables do not seem to be initialized automatically
u
@Rish FYI
u
@Hai To, let me check
l
u
nice, @Hai To, sorry for the late reply, it is that (previously that link was mentioned here (https://docs.airbyte.io/operator-guides/configuring-airbyte-db) but now it is not there anymore)
u
np, yup that was our problem 😅 but thx to your thread we got the right direction again highly appreciated 🙏
u
thanks 🙂
u
@Cristiano Sarmento, @Hai To, the latest Airbyte version can create the database tables automatically now. The only prerequisite is that the database exists in the server you specified, and the user has the permission to read / write / create tables in that database. That’s why the script was no longer mentioned in the documentation: https://docs.airbyte.io/operator-guides/configuring-airbyte-db#initializing-the-database
u
@Liren Tu, thanks!
u
@Liren Tu we still have problems to upgrade from 
0.27.0-alpha
to latest 
0.28.0-alpha
or later the database migration does not seem to work and throws this error
Copy code
airbyte-server      | 2021-08-06 12:33:31 ERROR i.a.s.ServerApp(setCustomerIdIfNotSet):197 - {workspace_app_root=/tmp/workspace/server/logs} - Could not find workspace with id: 5ae6b09b-fdec
-41af-aaf7-7d94cfc33ef6
airbyte-server      | io.airbyte.config.persistence.ConfigNotFoundException: config type: STANDARD_WORKSPACE id: 5ae6b09b-fdec-41af-aaf7-7d94cfc33ef6
airbyte-server      |   at io.airbyte.config.persistence.DatabaseConfigPersistence.getConfig(DatabaseConfigPersistence.java:133) ~[io.airbyte.airbyte-config-persistence-0.28.0-alpha.jar:?]
airbyte-server      |   at io.airbyte.config.persistence.ValidatingConfigPersistence.getConfig(ValidatingConfigPersistence.java:55) ~[io.airbyte.airbyte-config-persistence-0.28.0-alpha.jar:?
]
airbyte-server      |   at io.airbyte.config.persistence.ConfigRepository.getStandardWorkspace(ConfigRepository.java:55) ~[io.airbyte.airbyte-config-persistence-0.28.0-alpha.jar:?]
airbyte-server      |   at io.airbyte.server.ServerApp.setCustomerIdIfNotSet(ServerApp.java:185) [io.airbyte-airbyte-server-0.28.0-alpha.jar:?]
airbyte-server      |   at io.airbyte.server.ServerApp.runServer(ServerApp.java:218) [io.airbyte-airbyte-server-0.28.0-alpha.jar:?]
airbyte-server      |   at io.airbyte.server.ServerApp.main(ServerApp.java:265) [io.airbyte-airbyte-server-0.28.0-alpha.jar:?]
any idea what we could do here?
u
btw. even when I try to start 
0.29.1-alpha
from scratch with a new clean DB it does not seem to create the config DB automatically
u
Could not find workspace with id: 5ae6b09b-fdec-41af-aaf7-7d94cfc33ef6
@charles, is that there was a bug in 
0.28.0-alpha
related to the default workspace ID? In which version was it fixed?
u
even when I try to start 
0.29.1-alpha
 from scratch with a new clean DB it does not seem to create the config DB automatically
This is unexpected. The config DB creation is regularly tested by the integration test in CI. @Hai To, could you provide the logs?
u
It was fixed in 0.29.1-alpha. there was a different bug that we encountered in 0.29.1 (that causes an NPE--doesn't sound like the problem here). That being said, if you can go for at least 0.29.2-alpha that's the best place to be.
u
Looks like 
0.29.2-alpha
was not tagged or released.
a
Let me do that now.
u
really?
u
ah. i see. the tag wasn't in github. 😞
u
thanks.
u
Copy code
airbyte-server      | 2021-08-09 07:38:26 INFO i.a.d.i.BaseDatabaseInstance(lambda$getAndInitialize$1):98 - {workspace_app_root=/tmp/workspace/server/logs} - The airbyte configs database has
 been initialized
airbyte-server      | Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select 1 as "one" where exists (select * from airbyte_configs)]; ERROR: relation "airbyte_config
s" does not exist
airbyte-server      |   Position: 47
airbyte-server      |   at org.jooq_3.13.4.POSTGRES.debug(Unknown Source)
airbyte-server      |   at org.jooq.impl.Tools.translate(Tools.java:2753)
airbyte-server      |   at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
airbyte-server      |   at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:385)
airbyte-server      |   at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:502)
airbyte-server      |   at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:471)
airbyte-server      |   at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:485)
airbyte-server      |   at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:654)
airbyte-server      |   at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2884)
airbyte-server      |   at org.jooq.impl.DefaultDSLContext.fetchExists(DefaultDSLContext.java:4372)
airbyte-server      |   at io.airbyte.config.persistence.DatabaseConfigPersistence.lambda$loadData$2(DatabaseConfigPersistence.java:76)
airbyte-server      |   at io.airbyte.db.Database.lambda$transaction$0(Database.java:51)
airbyte-server      |   at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:564)
airbyte-server      |   at org.jooq.impl.Tools$12$1.block(Tools.java:4926)
airbyte-server      |   at java.base/java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3137)
airbyte-server      |   at org.jooq.impl.Tools$12.get(Tools.java:4923)
airbyte-server      |   at org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:616)
airbyte-server      |   at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:533)
airbyte-server      |   at io.airbyte.db.Database.transaction(Database.java:51)
airbyte-server      |   at io.airbyte.db.ExceptionWrappingDatabase.transaction(ExceptionWrappingDatabase.java:51)
airbyte-server      |   at io.airbyte.config.persistence.DatabaseConfigPersistence.loadData(DatabaseConfigPersistence.java:75)
airbyte-server      |   at io.airbyte.config.persistence.ConfigPersistenceBuilder.getDbPersistence(ConfigPersistenceBuilder.java:113)
airbyte-server      |   at io.airbyte.config.persistence.ConfigPersistenceBuilder.getDbPersistenceWithFileSeed(ConfigPersistenceBuilder.java:95)
airbyte-server      |   at io.airbyte.config.persistence.ConfigPersistenceBuilder.create(ConfigPersistenceBuilder.java:75)
airbyte-server      |   at io.airbyte.config.persistence.ConfigPersistenceBuilder.getAndInitializeDbPersistence(ConfigPersistenceBuilder.java:55)
airbyte-server      |   at io.airbyte.server.ServerApp.getServer(ServerApp.java:172)
airbyte-server      |   at io.airbyte.server.ServerApp.main(ServerApp.java:241)
airbyte-server      | Caused by: org.postgresql.util.PSQLException: ERROR: relation "airbyte_configs" does not exist
airbyte-server      |   Position: 47
airbyte-server      |   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
airbyte-server      |   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
airbyte-server      |   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
airbyte-server      |   at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
airbyte-server      |   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
airbyte-server      |   at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
airbyte-server      |   at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
airbyte-server      |   at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
airbyte-server      |   at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
airbyte-server      |   at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209)
airbyte-server      |   at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:3992)
airbyte-server      |   at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:295)
airbyte-server      |   at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:371)
airbyte-server      |   ... 23 more
u
thank you for the quick response guys, unfortunately even when we try to start Airbyte from scratch with 
0.29.2-alpha
we still get following error with an empty external PostgreSQL DB
u
when we manually init the DB with the SQL scripts in https://github.com/airbytehq/airbyte/tree/master/airbyte-db/src/main/resources it seems to work though
u
We use this query to check if a table has already been created:
select 1 as “one” where exists (select * from information_schema.tables where table_name = ‘airbyte_configs’)
Based on the log line below:
The airbyte configs database has been initialized
one possibility is that your database already has an 
airbyte_configs
table under a different schema. By default, Airbyte uses the 
public
schema. @Hai To, can you confirm if this is the case? Thank you.
u
@Liren Tu ah yes that could be the problem I've kept and backup of all the tables in another schema
6 Views
Open in Slack
PreviousNext
Powered by