"Unable to create SQL database." when trying to connect to an external database

Platform, Deploy & Infra Issues
,
1
  • Is this your first time deploying Airbyte?: No
  • OS Version / Instance: Debian on AWS EC2 t2.medium
  • Memory / Disk: 4Gb / 8Gb
  • Deployment: Docker
  • Airbyte Version: 0.39.21-alpha
  • Source name/version: -
  • Destination name/version: -
  • Step: During initial startup of Airbyte
  • Description: I am trying to setup a deployment of Airbyte on an AWS EC2 instance with an external RDS PostrgreSQL database following the guide on the Airbyte docs here. I have modified the .env file to point at my database but I keep getting the error Unable to create SQL database. from the airbyte-temporal step of starting up.

I’ll describe my steps below during setup and add the errors in the logs.

  1. Download Airbyte on my EC2 instance running Debian
  2. Create an RDS instance running a PostgreSQL server
  3. Within my PostgreSQL server create a database called airbyte with a user called airbyte and then grant all privileges on database airbyte to airbyte
  4. Modify my .env file to point at this new database using the airbyte user. The extract from my .env file is below with password and DATABASE_HOST removed
# Airbyte Internal Job Database, see https://docs.airbyte.io/operator-guides/configuring-airbyte-db
DATABASE_USER=airbyte
DATABASE_PASSWORD=<password>
DATABASE_HOST=<PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com
DATABASE_PORT=5432
DATABASE_DB=airbyte
# translate manually DATABASE_URL=jdbc:postgresql://${DATABASE_HOST}:${DATABASE_PORT}/${DATABASE_DB} (do not include the username or password here)
DATABASE_URL=jdbc:postgresql:/<PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com:5432/airbyte
JOBS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=0.29.15.001

# Airbyte Internal Config Database, defaults to Job Database if empty. Explicitly left empty to mute docker compose warnings.
CONFIG_DATABASE_USER=
CONFIG_DATABASE_PASSWORD=
CONFIG_DATABASE_URL=
CONFIGS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=0.35.15.001
  1. Start airbyte by running docker-compose up

This is where I get the error, as you can see in the following extract from the logs:

note I have partially obscured the database URL with <PART_OF_DATABASE_URL>


airbyte-temporal    | + echo 'PostgreSQL started.'
airbyte-temporal    | + update_postgres_schema
airbyte-temporal    | + CONTAINER_ALREADY_STARTED=CONTAINER_ALREADY_STARTED_PLACEHOLDER
airbyte-temporal    | + '[' '!' -e CONTAINER_ALREADY_STARTED_PLACEHOLDER ']'
airbyte-temporal    | + touch CONTAINER_ALREADY_STARTED_PLACEHOLDER
airbyte-temporal    | PostgreSQL started.
airbyte-temporal    | touch: CONTAINER_ALREADY_STARTED_PLACEHOLDER: Permission denied
airbyte-temporal    | + temporal-sql-tool --plugin postgres --ep <PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com -u airbyte -p 5432 create --db temporal
airbyte-temporal    | 2022-06-22T15:51:10.937Z	ERROR	Unable to create SQL database.	{"error": "pq: permission denied to create database", "logging-call-at": "handler.go:97"}
airbyte-temporal    | + temporal-sql-tool --plugin postgres --ep <PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com -u airbyte -p 5432 --db temporal setup-schema -v 0.0
airbyte-temporal    | 2022-06-22T15:51:11.000Z	ERROR	Unable to connect to SQL database.	{"error": "pq: database \"temporal\" does not exist", "logging-call-at": "handler.go:52"}
airbyte-temporal    | + temporal-sql-tool --plugin postgres --ep <PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com -u airbyte -p 5432 create --db temporal_visibility
airbyte-webapp      | /docker-entrypoint.sh: /docker-entrypoint.d/ is not empty, will attempt to perform configuration
airbyte-webapp      | /docker-entrypoint.sh: Looking for shell scripts in /docker-entrypoint.d/
airbyte-webapp      | /docker-entrypoint.sh: Launching /docker-entrypoint.d/10-listen-on-ipv6-by-default.sh
airbyte-webapp      | 10-listen-on-ipv6-by-default.sh: info: IPv6 listen already enabled
airbyte-webapp      | /docker-entrypoint.sh: Launching /docker-entrypoint.d/20-envsubst-on-templates.sh
airbyte-webapp      | 20-envsubst-on-templates.sh: Running envsubst on /etc/nginx/templates/default.conf.template to /etc/nginx/conf.d/default.conf
airbyte-webapp      | /docker-entrypoint.sh: Launching /docker-entrypoint.d/30-tune-worker-processes.sh
airbyte-webapp      | /docker-entrypoint.sh: Configuration complete; ready for start up
init                | MOUNT: /local_parent
init                | ROOT_PARENT: /tmp
init                | ROOT: /tmp/airbyte_local
init                | MOUNT_ROOT: /local_parent//airbyte_local
init exited with code 0
airbyte-temporal    | 2022-06-22T15:51:11.080Z	ERROR	Unable to create SQL database.	{"error": "pq: permission denied to create database", "logging-call-at": "handler.go:97"}
airbyte-temporal    | + temporal-sql-tool --plugin postgres --ep <PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com -u airbyte -p 5432 --db temporal_visibility setup-schema -v 0.0
airbyte-temporal    | 2022-06-22T15:51:11.121Z	ERROR	Unable to connect to SQL database.	{"error": "pq: database \"temporal_visibility\" does not exist", "logging-call-at": "handler.go:52"}
airbyte-temporal    | + echo 'Starting to update the temporal DB'
airbyte-temporal    | + temporal-sql-tool --plugin postgres --ep <PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com -u airbyte -p 5432 --db temporal update-schema -d /etc/temporal/schema/postgresql/v96/temporal/versioned
airbyte-temporal    | Starting to update the temporal DB
airbyte-temporal    | 2022-06-22T15:51:11.168Z	ERROR	Unable to connect to SQL database.	{"error": "pq: database \"temporal\" does not exist", "logging-call-at": "handler.go:73"}
airbyte-temporal    | 2022-06-22T15:51:11.168Z	INFO	UpdateSchemeTask started{"config": {"DBName":"","TargetVersion":"","SchemaDir":"/etc/temporal/schema/postgresql/v96/temporal/versioned","IsDryRun":false}, "logging-call-at": "updatetask.go:98"}
airbyte-temporal    | panic: runtime error: invalid memory address or nil pointer dereference
airbyte-temporal    | 	panic: runtime error: invalid memory address or nil pointer dereference
airbyte-temporal    | [signal SIGSEGV: segmentation violation code=0x1 addr=0x10 pc=0xebe954]
airbyte-temporal    |
airbyte-temporal    | goroutine 1 [running]:
airbyte-temporal    | go.temporal.io/server/tools/sql.(*Connection).Close(0x0)
airbyte-temporal    | 	/temporal/tools/sql/conn.go:120 +0x14
airbyte-temporal    | panic({0xf78e80, 0x1a77a30})
airbyte-temporal    | 	/usr/local/go/src/runtime/panic.go:1038 +0x215
airbyte-temporal    | go.temporal.io/server/tools/sql.(*Connection).ReadSchemaVersion(0xc00049d7c0)
airbyte-temporal    | 	/temporal/tools/sql/conn.go:65 +0x14
airbyte-temporal    | go.temporal.io/server/tools/common/schema.(*UpdateTask).Run(0xc00041f608)
airbyte-temporal    | 	/temporal/tools/common/schema/updatetask.go:106 +0x223
airbyte-temporal    | go.temporal.io/server/tools/common/schema.Update(0xc00049d7c0, {0x12ed200, 0x0}, {0x12e8ed8, 0xc00049d7c0})
airbyte-temporal    | 	/temporal/tools/common/schema/handler.go:48 +0x98
airbyte-temporal    | go.temporal.io/server/tools/sql.updateSchema(0x7fb39a634478, {0x12e8ed8, 0xc00049d7c0})
airbyte-temporal    | 	/temporal/tools/sql/handler.go:76 +0x51a
airbyte-temporal    | go.temporal.io/server/tools/sql.cliHandler(0x2, 0x1126110, {0x12e8ed8, 0xc00049d7c0})
airbyte-temporal    | 	/temporal/tools/sql/main.go:48 +0x56
airbyte-temporal    | go.temporal.io/server/tools/sql.BuildCLIOptions.func2(0xc00035d1e0)
airbyte-temporal    | 	/temporal/tools/sql/main.go:185 +0x2b
airbyte-temporal    | github.com/urfave/cli.HandleAction({0xf317a0, 0xc00049d7e0}, 0xd)
airbyte-temporal    | 	/go/pkg/mod/github.com/urfave/cli@v1.22.5/app.go:526 +0x50
airbyte-temporal    | github.com/urfave/cli.Command.Run({{0x10af830, 0xd}, {0x0, 0x0}, {0xc00049d820, 0x1, 0x1}, {0x10dcff0, 0x27}, {0x0, ...}, ...}, ...)
airbyte-temporal    | 	/go/pkg/mod/github.com/urfave/cli@v1.22.5/command.go:173 +0x652
airbyte-temporal    | github.com/urfave/cli.(*App).Run(0xc000338a80, {0xc0000300e0, 0xe, 0xe})
airbyte-temporal    | 	/go/pkg/mod/github.com/urfave/cli@v1.22.5/app.go:277 +0x705
airbyte-temporal    | go.temporal.io/server/tools/sql.RunTool({0xc0000300e0, 0xe, 0xe})
airbyte-temporal    | 	/temporal/tools/sql/main.go:42 +0x3c
airbyte-temporal    | main.main()
airbyte-temporal    | 	/temporal/cmd/tools/sql/main.go:36 +0x2e
airbyte-temporal    | + echo 'Update the temporal DB is done'
airbyte-temporal    | + echo 'Starting to update the temporal visibility DB'
airbyte-temporal    | + temporal-sql-tool --plugin postgres --ep <PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com -u airbyte -p 5432 --db temporal_visibility update-schema -d /etc/temporal/schema/postgresql/v96/visibility/versioned
airbyte-temporal    | Update the temporal DB is done
airbyte-temporal    | Starting to update the temporal visibility DB
airbyte-temporal    | 2022-06-22T15:51:11.213Z	ERROR	Unable to connect to SQL database.	{"error": "pq: database \"temporal_visibility\" does not exist", "logging-call-at": "handler.go:73"}
airbyte-temporal    | 2022-06-22T15:51:11.213Z	INFO	UpdateSchemeTask started{"config": {"DBName":"","TargetVersion":"","SchemaDir":"/etc/temporal/schema/postgresql/v96/visibility/versioned","IsDryRun":false}, "logging-call-at": "updatetask.go:98"}
airbyte-temporal    | panic: runtime error: invalid memory address or nil pointer dereference
airbyte-temporal    | 	panic: runtime error: invalid memory address or nil pointer dereference
airbyte-temporal    | [signal SIGSEGV: segmentation violation code=0x1 addr=0x10 pc=0xebe954]
airbyte-temporal    |
airbyte-temporal    | goroutine 1 [running]:
airbyte-temporal    | go.temporal.io/server/tools/sql.(*Connection).Close(0x0)
airbyte-temporal    | 	/temporal/tools/sql/conn.go:120 +0x14
airbyte-temporal    | panic({0xf78e80, 0x1a77a30})
airbyte-temporal    | 	/usr/local/go/src/runtime/panic.go:1038 +0x215
airbyte-temporal    | go.temporal.io/server/tools/sql.(*Connection).ReadSchemaVersion(0xc0000862e0)
airbyte-temporal    | 	/temporal/tools/sql/conn.go:65 +0x14
airbyte-temporal    | go.temporal.io/server/tools/common/schema.(*UpdateTask).Run(0xc00041f608)
airbyte-temporal    | 	/temporal/tools/common/schema/updatetask.go:106 +0x223
airbyte-temporal    | go.temporal.io/server/tools/common/schema.Update(0xc0000862e0, {0x12ed200, 0x0}, {0x12e8ed8, 0xc0000862e0})
airbyte-temporal    | 	/temporal/tools/common/schema/handler.go:48 +0x98
airbyte-temporal    | go.temporal.io/server/tools/sql.updateSchema(0x7f15f2481cb8, {0x12e8ed8, 0xc0000862e0})
airbyte-temporal    | 	/temporal/tools/sql/handler.go:76 +0x51a
airbyte-temporal    | go.temporal.io/server/tools/sql.cliHandler(0x2, 0x1126110, {0x12e8ed8, 0xc0000862e0})
airbyte-temporal    | 	/temporal/tools/sql/main.go:48 +0x56
airbyte-temporal    | go.temporal.io/server/tools/sql.BuildCLIOptions.func2(0xc00035cc60)
airbyte-temporal    | 	/temporal/tools/sql/main.go:185 +0x2b
airbyte-temporal    | github.com/urfave/cli.HandleAction({0xf317a0, 0xc000086300}, 0xd)
airbyte-temporal    | 	/go/pkg/mod/github.com/urfave/cli@v1.22.5/app.go:526 +0x50
airbyte-temporal    | github.com/urfave/cli.Command.Run({{0x10af830, 0xd}, {0x0, 0x0}, {0xc000086340, 0x1, 0x1}, {0x10dcff0, 0x27}, {0x0, ...}, ...}, ...)
airbyte-temporal    | 	/go/pkg/mod/github.com/urfave/cli@v1.22.5/command.go:173 +0x652
airbyte-temporal    | github.com/urfave/cli.(*App).Run(0xc000338a80, {0xc0000300e0, 0xe, 0xe})
airbyte-temporal    | 	/go/pkg/mod/github.com/urfave/cli@v1.22.5/app.go:277 +0x705
airbyte-temporal    | go.temporal.io/server/tools/sql.RunTool({0xc0000300e0, 0xe, 0xe})
airbyte-temporal    | 	/temporal/tools/sql/main.go:42 +0x3c
airbyte-temporal    | main.main()
airbyte-temporal    | 	/temporal/cmd/tools/sql/main.go:36 +0x2e
airbyte-temporal    | + echo 'Update the temporal visibility DB is done'
airbyte-temporal    | Update the temporal visibility DB is done
airbyte-temporal    | + echo 'starting temporal server'
airbyte-temporal    | starting temporal server
airbyte-temporal    | + ./start-temporal.sh
airbyte-temporal    | + setup_server
airbyte-temporal    | + echo 'Temporal CLI address: 172.21.0.7:7233.'
airbyte-temporal    | Temporal CLI address: 172.21.0.7:7233.
airbyte-temporal    | + grep SERVING
airbyte-temporal    | + tctl cluster health
airbyte-temporal    | 2022/06/22 15:51:11 Loading config; env=docker,zone=,configDir=config
airbyte-temporal    | 2022/06/22 15:51:11 Loading config files=[config/docker.yaml]
airbyte-temporal    | + echo 'Waiting for Temporal server to start...'
airbyte-temporal    | Waiting for Temporal server to start...
airbyte-temporal    | + sleep 1
airbyte-temporal    | {"level":"error","ts":"2022-06-22T15:51:11.392Z","msg":"Unable to read dynamic config file. Continue with default settings but the ERROR MUST BE FIXED before the next upgrade","error":"unable to validate dynamic config: dynamic config: config/dynamicconfig/development.yaml: stat config/dynamicconfig/development.yaml: no such file or directory","logging-call-at":"main.go:140","stacktrace":"go.temporal.io/server/common/log.(*zapLogger).Error\n\t/temporal/common/log/zap_logger.go:142\nmain.buildCLI.func2\n\t/temporal/cmd/server/main.go:140\ngithub.com/urfave/cli/v2.(*Command).Run\n\t/go/pkg/mod/github.com/urfave/cli/v2@v2.3.0/command.go:163\ngithub.com/urfave/cli/v2.(*App).RunContext\n\t/go/pkg/mod/github.com/urfave/cli/v2@v2.3.0/app.go:313\ngithub.com/urfave/cli/v2.(*App).Run\n\t/go/pkg/mod/github.com/urfave/cli/v2@v2.3.0/app.go:224\nmain.main\n\t/temporal/cmd/server/main.go:50\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:255"}
airbyte-temporal    | {"level":"info","ts":"2022-06-22T15:51:11.392Z","msg":"Starting server for services","value":["history","matching","frontend","worker"],"logging-call-at":"server.go:123"}
airbyte-temporal    | Unable to start server. Error: sql schema version compatibility check failed: pq: database "temporal" does not exist
airbyte-temporal    | + echo 'Done init'
airbyte-temporal    | Done init
airbyte-temporal    | + wait_for_postgres
airbyte-temporal    | + nc -z <PART_OF_DATABASE_URL>.eu-west-2.rds.amazonaws.com 5432
airbyte-temporal    | + echo 'PostgreSQL started.'
airbyte-temporal    | PostgreSQL started.
airbyte-temporal    | + update_postgres_schema

Do you have any advice on what I am doing incorrectly? Any help would be much appreciated!!

I think it may be to do with how I’ve created an account an airbyte database in my Postgres instance and then created a user for Airbyte to use but I really don’t know where I’ve gone wrong or what the correct procedure here should be!

Thanks in advance for your help!!

2

Hello @will_m! I’ve found a bug that may be related to what you’re running into, here’s a PR for it.

Looks like the problem is the Kernel version, the default for AWS is 4.14.x, but upgrading it to 5.4.x might help. Another solution that might work would be to use legacy IP tables, here’s a guide on how to do that.

Let me know if that helps!

3

Hi @natalyjazzviolin,
Thanks for the suggestion but our error was slightly different to that GitHub PR. I managed to fix it so I just thought I’d add my solution below in case anyone else has the same issue in the past.

The issue was that I created a new user in our Postgres sever and configured Airbyte to use this. This user however does not have permission to create new databases. This causes an issue when starting Airbyte for the first time as Airbyte expects to find databases called temporal and temporal_visibility. If these do not exist then Airbyte tries to create them.

The solution was to manually create the databases called temporal and temporal_visibility in our Postgres server and assign permissions to the Airbyte user.

Just to be clear this is the code required to be run if connecting to the Postgres server using psql

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

create database temporal_visibility;
grant all privileges on database temporal_visibility to airbyte;
1 Like
4

Thank you for such a great follow up, and glad you were able to solve it!

5

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.