CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
MStikh
MStikh

Reputation: 404

Cannot connect to postgresql through sqlalchemy from different computer

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.

Upvotes: 2

Views: 5099

Answers (2)

MStikh
MStikh

Reputation: 404

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.

Upvotes: 2

mad_
mad_

Reputation: 8273

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

Upvotes: 1

Related Questions