Cannot connect to postgresql through sqlalchemy from different computer

Question

I think do not understand properly how does sqlalchemy works, I have tried to connect to postgresql running on some cloud server from my local computer:

db = create_engine('postgresql://[email protected]:5432/dbname')

but that causes the error:

Is the server running on host "172.23.160.212" and accepting
    TCP/IP connections on port 5432?

I have checked the port and host also exists.

I thought I should connect to the host using ssh first:

with SSHTunnelForwarder((172.23.160.212, 22), ssh_username='ubuntu', remote_bind_address=(127.0.0.1, 3306)) as server:
    db = create_engine('postgresql://[email protected]:5432/dbname')

But that did not help.

Can you open a telnet connection from your local pc to the cloud server on 5432? — Mike Scotty
– Mike Scotty, Commented Aug 30, 2018 at 13:52
I do not know what is telnet, but I usually connect to that server using ssh, it works fine. I can also accses database there — MStikh
– MStikh, Commented Aug 30, 2018 at 13:53
Is TCP connection enabled?. Have had trouble with similar issue in mssql — mad_
– mad_, Commented Aug 30, 2018 at 13:55
chances are, your pg_hba.conf is not configured for external connections or the firewall is blocking 5432. telnet is a tool that can connect to any port, allowing you to to check if the port is available, w/o worrying about the actual protocol. — Mike Scotty
– Mike Scotty, Commented Aug 30, 2018 at 13:57

MStikh · Accepted Answer · 2018-08-31 10:48:37Z

2

I have solved the problem partially,

If one opens ssh connection in bash (ssh [email protected] -L 5432:localhost:5432 -N -n -f), then one can open db through python:

db = create_engine('postgresql://tissuemaps@localhost:5432/dbname')

If I understand correctly, the connection to postgres directly should also have worked, and why it does not, I do not know.

answered Aug 31, 2018 at 10:48

MStikh

4047 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mad_ · Accepted Answer · 2018-08-30 14:16:01Z

1

I think the problem is TCP connection is not enabled and have to modify your pg_hba.conf file to allow the connection. Add lines in the config file to allow connection

host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5

Apart from that you can also check postgresql.conf (/etc/postgresql/9.3/main/postgresql.conf) to check other postgres configs are what you expect like port number etc. Also add below line in config file to accept all the connections

listen_addresses = '*'

You need to restart the postgres service for the changes to be picked up

sudo service postgresql restart

https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html https://www.postgresql.org/docs/9.1/static/runtime-config-connection.html

edited Aug 30, 2018 at 14:16

answered Aug 30, 2018 at 13:58

mad_

8,2832 gold badges32 silver badges46 bronze badges

5 Comments

MStikh Over a year ago

It had already the first line, I have added the second, but that id not help.

mad_ Over a year ago

What errors you are getting? Are you able to connect using psql command line interface?

MStikh Over a year ago

TO mad_ That is my question, I can reach out to psql when I am on the server, but if I do it from my computer, it even does not know what is psql, because I do not have it locally installed

mad_ Over a year ago

You need a client tool to be installed locally to check the connection like pgAdmin or similar. Again are you getting the same error? It is definitely related to connectivity related so check firewalls, antivirus any tool that can block. Make changes as liberal as possible (for checking purpose).

Sumit Yadav Over a year ago

in my case, I am able to connect remote PostgreSQL by dbeaver but not by my python script.

Collectives™ on Stack Overflow

Cannot connect to postgresql through sqlalchemy from different computer

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related