How to connect to postgres on another server where postgres is on docker?

Question

I can connect to postgres in that way. On my local machine i run:

1. ssh name@ip -p 22

2. input --> password

and then

3. sudo docker-compose exec postgres bash

after that i have full access to my postgres db. how can i connect to that DB with python?

I know library like psycopg2, but i didn't found any example how to connect to db which is on another server and with docker ot run.

Answer 1

There are three layers here.

1. Your local machine.
2. The server.
3. The container running the database.

Between each layer, there is a gap. Between (1) and (2) you have the Internet. Between (2) and (3) you have docker networking.

Now, what you described in the question is this.

You first cross the (1)-(2) gap with SSH then you cross the (2)-(3) with the command sudo docker-compose exec postgres bash.

Now for your question in the comment, according to docker documentation, docker-compose exec <container-name or id> <command> will run a command in a container, and sudo elevate your privilege to root account. Since the command is bash, you essentially open an interactive shell of the container.

Now this method of crossing the two gaps will work, and you observed, but for psycopg2 library, it will not.

Again with docker documentation, you can tell docker to eliminate the (2)-(3) gap for you, this is mainly known as publishing a port. You can tell docker to map a port on the server to a port on the container, so the (2)-(3) gap can be eliminated. At this point, the connection to a port on the server will be passed to the container at the defined port.

Now the only gap you need to cross is just (1)-(2) which can now be done by psycopg2 easily (given that the firewall is allowing inbound connection on that said port).

Now, the detail on how to tell docker to eliminate the (2)-(3) gap is in the answer to Connecting to Postgresql in a docker container from outside. It also show you how you can connect to the database with psql directly from your local machine.