Hi team we are facing issue with connecting postgres running on a vm to airbyte, for running airbyte with custom postgres db. screenshots attached. please help
Hey can you share the complete server logs ?
[quote="harshith, post:2, topic:1264"]
omplete server lo
[/quote]
2022-06-01 05:12:49 INFO i.a.d.Database(createWithRetryTimeout):71 - Database is not ready yet. Please wait a moment, it might still be initializing...
2022-06-01 05:12:54 WARN i.a.d.Database(createWithRetryTimeout):63 - Waiting for database to become available...
2022-06-01 05:12:54 INFO i.a.d.i.BaseDatabaseInstance(lambda$isDatabaseConnected$5):110 - Testing airbyte configs database connection...
2022-06-01 05:13:24 ERROR i.a.d.i.BaseDatabaseInstance(lambda$isDatabaseConnected$5):113 - Failed to verify database connection.
org.jooq.exception.DataAccessException: Error getting connection from data source HikariDataSource (HikariPool-1)
at org.jooq_3.13.4.POSTGRES.debug(Unknown Source) ~[?:?]
at org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:86) ~[jooq-3.13.4.jar:?]
at org.jooq.impl.DefaultExecuteContext.connection(DefaultExecuteContext.java:647) ~[jooq-3.13.4.jar:?]
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:334) ~[jooq-3.13.4.jar:?]
at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:502) ~[jooq-3.13.4.jar:?]
at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:471) ~[jooq-3.13.4.jar:?]
at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:485) ~[jooq-3.13.4.jar:?]
at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:654) ~[jooq-3.13.4.jar:?]
at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2884) ~[jooq-3.13.4.jar:?]
at org.jooq.impl.DefaultDSLContext.fetchExists(DefaultDSLContext.java:4372) ~[jooq-3.13.4.jar:?]
at io.airbyte.db.instance.BaseDatabaseInstance.lambda$isDatabaseConnected$4(BaseDatabaseInstance.java:111) ~[io.airbyte.airbyte-db-lib-0.39.1-alpha.jar:?]
at io.airbyte.db.Database.query(Database.java:33) ~[io.airbyte.airbyte-db-lib-0.39.1-alpha.jar:?]
at io.airbyte.db.instance.BaseDatabaseInstance.lambda$isDatabaseConnected$5(BaseDatabaseInstance.java:111) ~[io.airbyte.airbyte-db-lib-0.39.1-alpha.jar:?]
at io.airbyte.db.Database.createWithRetryTimeout(Database.java:70) [io.airbyte.airbyte-db-lib-0.39.1-alpha.jar:?]
at io.airbyte.db.Database.createWithRetry(Database.java:46) [io.airbyte.airbyte-db-lib-0.39.1-alpha.jar:?]
at io.airbyte.db.instance.configs.ConfigsDatabaseInstance.isInitialized(ConfigsDatabaseInstance.java:49) [io.airbyte.airbyte-db-lib-0.39.1-alpha.jar:?]
at io.airbyte.db.instance.MinimumFlywayMigrationVersionCheck.assertDatabase(MinimumFlywayMigrationVersionCheck.java:51) [io.airbyte.airbyte-db-lib-0.39.1-alpha.jar:?]
at io.airbyte.server.ServerApp.assertDatabasesReady(ServerApp.java:146) [io.airbyte-airbyte-server-0.39.1-alpha.jar:?]
at io.airbyte.server.ServerApp.getServer(ServerApp.java:183) [io.airbyte-airbyte-server-0.39.1-alpha.jar:?]
at io.airbyte.server.ServerApp.main(ServerApp.java:310) [io.airbyte-airbyte-server-0.39.1-alpha.jar:?]
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - 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:83) ~[jooq-3.13.4.jar:?]
... 18 more
Caused by: org.postgresql.util.PSQLException: Connection to pet--qa--airbyte--vm-01:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:319) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.Driver.makeConnection(Driver.java:402) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.Driver.connect(Driver.java:261) ~[postgresql-42.3.4.jar:42.3.4]
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:264) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
at java.lang.Thread.run(Thread.java:833) ~[?:?]
Caused by: java.net.ConnectException: Connection refused
at sun.nio.ch.Net.pollConnect(Native Method) ~[?:?]
at sun.nio.ch.Net.pollConnectNow(Net.java:672) ~[?:?]
at sun.nio.ch.NioSocketImpl.timedFinishConnect(NioSocketImpl.java:542) ~[?:?]
at sun.nio.ch.NioSocketImpl.connect(NioSocketImpl.java:597) ~[?:?]
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:327) ~[?:?]
at java.net.Socket.connect(Socket.java:633) ~[?:?]
at org.postgresql.core.PGStream.createSocket(PGStream.java:241) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.core.PGStream.<init>(PGStream.java:98) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:109) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:235) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.Driver.makeConnection(Driver.java:402) ~[postgresql-42.3.4.jar:42.3.4]
at org.postgresql.Driver.connect(Driver.java:261) ~[postgresql-42.3.4.jar:42.3.4]
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:264) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
at java.lang.Thread.run(Thread.java:833) ~[?:?]
Hey can you check if both the database and airbyte are on same VPC ?
we have both in same instance/vm
Do you mind temporarily installing psql in 1 of the container and try to connect to that database?
i tried running psql in 1 container and changed config in .env file, but still same error.
can you please suggest what needs to be changed in configuration of airbyte for external psql connectivity. i changed the below parameters in .env file. still issue persists.
DATABASE_USER=postgres
DATABASE_PASSWORD=password
DATABASE_HOST=host.docker.internal # refers to localhost of host
DATABASE_PORT=3000
DATABASE_DB=postgres
Were you able to connect with psql ?
yes in terminal i was able to connect but in airbyte-server logs there was a error, “failed to verify database connection” also same airbyte UI error cannot reach server…
Hey also to understand the connectivity more better could you use airbyte-db (default airbyte database) and check if that is working as expected?
yes default db is working fine, there was no error in airbyte UI for default airbyte-db.
You are saying even with default-db there is error in UI? If so can you share the screenshot?
there was no error with default-db
Hey with the above changed parameters
DATABASE_USER=postgres
DATABASE_PASSWORD=password
DATABASE_HOST=host.docker.internal # refers to localhost of host
DATABASE_PORT=3000
DATABASE_DB=postgres
did you also change the DATABASE_URL?
yes i had changed DATABASE_URL based on the parameters which i had mentioned.
Hey we have a doc on connecting the external database https://docs.airbyte.com/operator-guides/configuring-airbyte-db/#connecting-to-an-external-postgres-database was this helpful?
i have installed using this link reference only, but we are getting this error. please suggest i need urgent help on this.
Hi there from the Community Assistance team.
We’re letting you know about an issue we discovered with the back-end process we use to handle topics and responses on the forum. If you experienced a situation where you posted the last message in a topic that did not receive any further replies, please open a new topic to continue the discussion. In addition, if you’re having a problem and find a closed topic on the subject, go ahead and open a new topic on it and we’ll follow up with you. We apologize for the inconvenience, and appreciate your willingness to work with us to provide a supportive community.