https://linen.dev logo
Join Slack
Powered by
Hi, I am trying to setup external database for air...
# replication-ask-ai
m
Hi, I am trying to setup external database for airbyte - managed RDS Aurora. However I am facing to several issues in bootloader service that is trying to prepare the database. Right now bootloader is failing on
Copy code
CREATE                                                                                                                                                                                                                                                                                                                 
│     UNIQUE INDEX IF NOT EXISTS job_attempt_idx ON                                                                                                                                                                                                                                                                      
│     ATTEMPTS(                                                                                                                                                                                                                                                                                                         
│         job_id,                                                                                                                                                                                                                                                                                                        
│         attempt_number                                                                                                                                                                                                                                                                                                 
│     );                                                                                                                                                                                                                                                                                                                 
│ ]; ERROR: type "job_status" already exists                                                                                                                                                                                                                                                                             
│     at org.jooq_3.17.8.POSTGRES.debug(Unknown Source) ~[?:?]                                                                                                                                                                                                                                                           
│     at org.jooq.impl.Tools.translate(Tools.java:3313) ~[jooq-3.17.8.jar:?]                                                                                                                                                                                                                                             
│     at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:678) ~[jooq-3.17.8.jar:?]                                                                                                                                                                                                           
│     at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:355) ~[jooq-3.17.8.jar:?]                                                                                                                                                                                                                                
│     at org.jooq.impl.DefaultDSLContext.execute(DefaultDSLContext.java:1132) ~[jooq-3.17.8.jar:?]                                                                                                                                                                                                                       
│     at io.airbyte.db.init.DatabaseInitializer.initializeSchema(DatabaseInitializer.java:94) ~[io.airbyte.airbyte-db-db-lib-0.50.25.jar:?]                                                                                                                                                                              
│     at io.airbyte.db.Database.lambda$transaction$0(Database.java:27) ~[io.airbyte.airbyte-db-db-lib-0.50.25.jar:?]                                                                                                                                                                                                     
│     at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$3(DefaultDSLContext.java:552) ~[jooq-3.17.8.jar:?]                                                                                                                                                                                                    
│     at org.jooq.impl.Tools$3$1.block(Tools.java:5854) ~[jooq-3.17.8.jar:?]                                                                                                                                                                                                                                             
│     at java.util.concurrent.ForkJoinPool.unmanagedBlock(ForkJoinPool.java:3745) ~[?:?]                                                                                                                                                                                                                                 
│     at java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3690) ~[?:?]
k
@Matej Líner That's a great question, but unfortunately it is too long. Can you ask me again but make it shorter? I'm here to help!
m
@kapa.ai when using external database I am facing to issue with bootloader failing on ERROR: type "job_status" already exists
Is there any postgresql version constraint? @kapa.ai
k
A message from kapa.ai
Yes, there are certain version requirements for using Postgres with Airbyte: - For non-CDC workflows, you should use Postgres v9.3.x or above. - For CDC workflows, you should use Postgres v10 or above. - If you're using the Postgres destination, you'll need a Postgres server version 9.5 or above. Also, if you're an Airbyte Open Source user, you should upgrade your Airbyte platform to version 
v0.40.0-alpha
or newer. For more details, you can refer to the [Postgres source troubleshooting guide](https://docs.airbyte.com/integrations/sources/postgres/postgres-troubleshooting) and the [Postgres destination guide](https://docs.airbyte.com/integrations/destinations/postgres).
m
Are there any preparations required when using external database @kapa.ai
k
A message from kapa.ai
Yes, there are several preparations required when using an external database with Airbyte: 1. Database Setup: You need to ensure that the database exists on the server and the user has both read and write permissions to the database. The database should be empty. If the database is not empty and has a table that shares the same name as one of the Airbyte tables, the server will assume that the database has been initialized, and will not copy the data over, resulting in server failure. If you run into this issue, just wipe out the database, and launch the server again ([source](https://docs.airbyte.com/operator-guides/configuring-airbyte-db)). 2. Environment Variables: You need to set up the following environment variables in the 
.env
file:
Copy code
bash DATABASE_USER=postgres   DATABASE_PASSWORD=password   DATABASE_HOST=host.docker.internal # refers to localhost of host   DATABASE_PORT=3000   DATABASE_DB=postgres
If you need to separate the Config Database from the Job Database, you can specify separate parameters:
Copy code
bash CONFIG_DATABASE_USER=airbyte_config_db_user   CONFIG_DATABASE_PASSWORD=password
You also need to redefine the JDBC URL in the 
DATABASE_URL
environment variable to include the correct host, port, and database. If you need to provide extra arguments to the JDBC driver (for example, to handle SSL) you should add it here as well:
Copy code
bash DATABASE_URL=jdbc:<postgresql://host.docker.internal:3000/postgres?ssl=true&sslmode=require>
If the config database is separate from the job database, you need to set up 
CONFIG_DATABASE_URL
as well:
Copy code
bash CONFIG_DATABASE_URL=jdbc:postgresql://<host>:<port>/<database>?<extra-parameters>
([source](https://docs.airbyte.com/operator-guides/configuring-airbyte-db)) 3. Data Migration: If you're switching from the internal database to an external one, you need to manually backup the database. Currently, Airbyte doesn’t have a migration feature between the internal database and an external one ([source](https://discuss.airbyte.io/t/2903)). 4. Database Version: Ensure that you're using a compatible version of Postgres. For non-CDC workflows, you should use Postgres v9.3.x or above. For CDC workflows, you should use Postgres v10 or above. If you're using the Postgres destination, you'll need a Postgres server version 9.5 or above. Please note that currently, Airbyte does not support MySQL as an external database ([source](https://discuss.airbyte.io/t/3069)).
m
When using external databaze for the first time, bootloader fails on ERROR: type "job_status" already exists can you help me out please @kapa.ai
k
A message from kapa.ai
Based on the information provided in the extracts, it seems like there might be a conflict with an existing "job_status" type in your external database. You might want to check if this type already exists in your database and if it's being used somewhere else. If it's not being used, you could try deleting it and then re-running the Airbyte setup. Also, ensure that the user has the necessary permissions to create new databases or types in your external database. For more detailed instructions on how to connect to an external Postgres database, you can refer to the [Airbyte documentation](https://docs.airbyte.com/operator-guides/configuring-airbyte-db/#connecting-to-an-external-postgres-database). If the issue persists, it would be helpful to have more information about the error message you're seeing. You can also consider reporting this issue on the [Airbyte Github](https://github.com/airbytehq/airbyte/issues/new?assignees=&amp;labels=type%2Fbug%2C+needs-triage&amp;template=bug-report.md&amp;title=) for further assistance.
m
Is there any hard requirement to use public schema for airbyte database? @kapa.ai
k
A message from kapa.ai
No, there is no hard requirement to use the 
public
schema for the Airbyte database. The 
public
schema is set by default, but you can specify other schemas if you prefer. For example, when setting up a Postgres source, you have the option to list each of the schemas you want to sync. These are case-sensitive, and multiple schemas may be entered ([source](https://docs.airbyte.com/integrations/sources/postgres#step-1-create-a-dedicated-read-only-postgres-user)). Similarly, when setting up a Vertica destination, you can list the default schemas you want to use. Multiple schemas may be used at one time. If no schemas are set explicitly, all existing schemas will be synced ([source](https://docs.airbyte.com/integrations/destinations/vertica#step-1-optional-create-a-dedicated-read-only-user)). Remember to ensure that the user has the necessary permissions to access and modify the schemas you specify.
m
How can I specify schema name for external database @kapa.ai
k
A message from kapa.ai
@Matej Líner That's a great question, but unfortunately I am only capable of responding to a maximum of five questions per thread. Don't hesitate to create a new thread for any further questions you have though. I'm here to help!
Open in Slack
PreviousNext
Powered by