Hi dbt community!
I’ve connected to private databases using dbt-core before by whitelisting a static IP that the app uses in AWS.
I’m trying this on a Heroku app with the Proximo addon that forwards all the apps traffic through a static IP that I’m whitelisting on the database server’s firewall.
When I run ‘dbt debug’ in this environment, I get “All checks passed”. But when I try to run one of my models, I get a Database Error: “timeout expired”. I’ve never seen a situation where debug succeeds and run fails to connect! I’ve also successfully connected to the database in this environment in a python app that uses psycopg2. Please share any insight that you may have. Thank you! Detailed logs below:
dbt run logs:
19:17:22.536295 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'load_project', 'label': 'fd8ce4c8-6736-4d26-a10f-d2820dffd438', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc29836d0>]}
19:17:22.544922 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'resource_counts', 'label': 'fd8ce4c8-6736-4d26-a10f-d2820dffd438', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc2982fb0>]}
19:17:22.546241 [info ] [MainThread]: Found 2 models, 4 tests, 0 snapshots, 0 analyses, 193 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
19:17:22.546740 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': 'fd8ce4c8-6736-4d26-a10f-d2820dffd438', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc314e470>]}
19:17:22.549925 [info ] [MainThread]:
19:17:22.550592 [debug] [MainThread]: Acquiring new redshift connection "master"
19:17:22.555779 [debug] [ThreadPool]: Acquiring new redshift connection "list_dev"
19:17:22.581653 [debug] [ThreadPool]: Using redshift connection "list_dev"
19:17:22.583627 [debug] [ThreadPool]: On list_dev: /* {"app": "dbt", "dbt_version": "1.1.0", "profile_name": "ip_test", "target_name": "prod", "connection_name": "list_dev"} */
select distinct nspname from pg_namespace
19:17:22.584550 [debug] [ThreadPool]: Opening a new connection, currently in state init
19:17:22.585092 [debug] [ThreadPool]: Redshift adapter: Connecting to Redshift using 'database' credentials
19:17:32.606154 [debug] [ThreadPool]: Postgres adapter: Got an error when attempting to open a postgres connection: 'connection to server at "[removed for privacy]" ([removed for privacy]), port 5439 failed: timeout expired
'
19:17:32.606839 [debug] [ThreadPool]: Postgres adapter: Error running SQL: /* {"app": "dbt", "dbt_version": "1.1.0", "profile_name": "ip_test", "target_name": "prod", "connection_name": "list_dev"} */
select distinct nspname from pg_namespace
19:17:32.607185 [debug] [ThreadPool]: Postgres adapter: Rolling back transaction.
19:17:32.608147 [debug] [ThreadPool]: Postgres adapter: Error running SQL: macro list_schemas
19:17:32.608396 [debug] [ThreadPool]: Postgres adapter: Rolling back transaction.
19:17:32.609018 [debug] [ThreadPool]: On list_dev: No close available on handle
19:17:32.611206 [debug] [MainThread]: Connection 'master' was properly closed.
19:17:32.611439 [debug] [MainThread]: Connection 'list_dev' was properly closed.
19:17:32.611669 [error] [MainThread]: Encountered an error:
Database Error
connection to server at "[removed for privacy]" ([removed for privacy]), port 5439 failed: timeout expired
19:17:32.612484 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc2983a00>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc4b2e710>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc41c5390>]}
19:17:32.612953 [debug] [MainThread]: Flushing usage events
dbt debug logs:
Configuration:
profiles.yml file [OK found and valid]
dbt_project.yml file [OK found and valid]
Required dependencies:
19:26:32.050026 [debug] [MainThread]: Executing "git --help"
19:26:32.060361 [debug] [MainThread]: STDOUT: "b"usage: git [--version] [--help] [-C <path>] [-c <name>=<value>]\n [--exec-path[=<path>]] [--html-path] [--man-path] [--info-path]\n [-p | --paginate | -P | --no-pager] [--no-replace-objects] [--bare]\n [--git-dir=<path>] [--work-tree=<path>] [--namespace=<name>]\n [--super-prefix=<path>] [--config-env=<name>=<envvar>]\n <command> [<args>]\n\nThese are common Git commands used in various situations:\n\nstart a working area (see also: git help tutorial)\n clone Clone a repository into a new directory\n init Create an empty Git repository or reinitialize an existing one\n\nwork on the current change (see also: git help everyday)\n add Add file contents to the index\n mv Move or rename a file, a directory, or a symlink\n restore Restore working tree files\n rm Remove files from the working tree and from the index\n\nexamine the history and state (see also: git help revisions)\n bisect Use binary search to find the commit that introduced a bug\n diff Show changes between commits, commit and working tree, etc\n grep Print lines matching a pattern\n log Show commit logs\n show Show various types of objects\n status Show the working tree status\n\ngrow, mark and tweak your common history\n branch List, create, or delete branches\n commit Record changes to the repository\n merge Join two or more development histories together\n rebase Reapply commits on top of another base tip\n reset Reset current HEAD to the specified state\n switch Switch branches\n tag Create, list, delete or verify a tag object signed with GPG\n\ncollaborate (see also: git help workflows)\n fetch Download objects and refs from another repository\n pull Fetch from and integrate with another repository or a local branch\n push Update remote refs along with associated objects\n\n'git help -a' and 'git help -g' list available subcommands and some\nconcept guides. See 'git help <command>' or 'git help <concept>'\nto read about a specific subcommand or concept.\nSee 'git help git' for an overview of the system.\n""
19:26:32.061313 [debug] [MainThread]: STDERR: "b"ERROR: ld.so: object 'libdl.so' from LD_PRELOAD cannot be preloaded (cannot open shared object file): ignored.\n""
- git [OK found]
Connection:
host: [removed for privacy]
port: 5439
user: awsuser
database: dev
schema: public
search_path: None
keepalives_idle: 240
sslmode: None
method: database
cluster_id: None
iam_profile: None
iam_duration_seconds: 900
19:26:32.066420 [debug] [MainThread]: Acquiring new redshift connection "debug"
19:26:32.067448 [debug] [MainThread]: Using redshift connection "debug"
19:26:32.067980 [debug] [MainThread]: On debug: select 1 as id
19:26:32.073439 [debug] [MainThread]: Opening a new connection, currently in state init
19:26:32.074219 [debug] [MainThread]: Redshift adapter: Connecting to Redshift using 'database' credentials
19:26:32.359175 [debug] [MainThread]: SQL status: SELECT in 0.28 seconds
19:26:32.362744 [debug] [MainThread]: On debug: Close
Connection test: [OK connection ok]
All checks passed!
19:26:32.377432 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fae8e270fa0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fae8e271330>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fae8e2738e0>]}
19:26:32.378168 [debug] [MainThread]: Flushing usage events
19:26:32.676501 [debug] [MainThread]: Connection 'debug' was properly closed.