4

Suppose I run my Apache Superset on top of the Docker and I want this to connect with my local postgreSQL server. I used the following URI but I got an error:

postgresql+psycopg2://username:password@localhost:5432/mydb

The error is:

ERROR: {"error": "Connection failed!\n\nThe error message returned was:\n(psycopg2.OperationalError) could not connect to server: Connection refused\n\tIs the server running on host \"localhost\" (127.0.0.1) and accepting\n\tTCP/IP connections on port 5432?\ncould not connect to server: Cannot assign requested address\n\tIs the server running on host \"localhost\" (::1) and accepting\n\tTCP/IP connections on port 5432?\n\n(Background on this error at: http://sqlalche.me/e/e3q8)", "stacktrace": "Traceback (most recent call last):\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py\", line 2265, in _wrap_pool_connect\n    return fn()\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py\", line 303, in unique_connection\n    return _ConnectionFairy._checkout(self)\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py\", line 760, in _checkout\n    fairy = _ConnectionRecord.checkout(pool)\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py\", line 492, in checkout\n    rec = pool._do_get()\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py\", line 238, in _do_get\n    return self._create_connection()\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py\", line 308, in _create_connection\n    return _ConnectionRecord(self)\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py\", line 437, in __init__\n    self.__connect(first_connect_check=True)\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py\", line 639, in __connect\n    connection = pool._invoke_creator(self)\n  File \"/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py\", line 114, in ...

How can I solve it?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Ghazaleh Kharadpoor
  • 146
  • 3
  • 13

4 Answers4

2

Instead of using localhost or 127.0.0.1, open up your pgAdmin. The servers are on the left.

  • Click the dropdown.
  • Right click on the now opened cluster (level above "Databases") & open properties.
  • Navigate to the opened connection tab and the Hostname/Address is your replacement for "localhost"

Also make sure the final part of your connection string is pointed at your database which is one level below "Databases" in your pgAdmin.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Brendan McMahon
  • 86
  • 1
  • 9
  • Sorry, I meant to add that my use case may be different to yours as I am using Superset through a Debian VM and pulling data from my remote PostgreSQL DB – Brendan McMahon Jan 14 '20 at 16:48
  • In pgAdmin4 v4.13, in the "Properties" dialog there is no "connection tab" or hostname/address. In the browser URL address bar, I see `127.0.0.1/57441/browser/` My database owner and database name are still both "postgres". – Rich Lysakowski PhD Apr 12 '20 at 02:45
2

I encountered the same problem with connecting superset to local database (postgresql), and after consulting many sites on the internet this trick solved it.Instead of local host, try to put this in SQLalchemy URI:

postgresql+psycopg2://user:password@host.docker.internal:5432/database

Nanis Sn
  • 35
  • 3
0

I understand that It is a bad practice action to connect the Docker with a host database so I changed my opinion and use the postgres image inside the docker and push my data to that postgres server. It would be helpful if you notify me if I am wrong.

Ghazaleh Kharadpoor
  • 146
  • 3
  • 13
0

In my case, I had to replace localhost with my host IP address in SQLAlchemy URI, like so:

postgresql+psycopg2://username:password@your-host-ip-address:5432/database-name

Alternatively you can use host.docker.internal, like so:

postgresql+psycopg2://username:password@host.docker.internal:5432/database-name

Please refer this answer.

Vivek Kumar
  • 113
  • 2
  • 4