docker-compose with multiple postgres databases from sql dumps

Question

I know this may seem like a duplicate of docker-compose with multiple databases, but I still can't manage to get it working after going through the answers.

Here is my docker-compose.yml:

version: '3'
services:
  backend:
    image: backend:1.0
    build: ./backend
    ports:
      - "9090:9090"
    depends_on:
      - db
      - ppt
    environment:
      - DATABASE_HOST=db
  db:
    image: main_db:26.03.18
    restart: always
    build: ./db
    ports:
      - "5432:5432"
  ppt:
    image: ppt_generator:1.0
    build: ./ppt
    ports:
      - "6060:6060"
  login:
    image: login:1.0
    build: ./login
    ports:
      - "7070:7070"
    depends_on:
      - login_db
  login_db:
    image: login_db:27.04.2018
    restart: always
    build: ./login_db
    ports:
      - "5433:5433"

Notice that I have one db on port 5433 and the other on 5432. However, when I run docker ps after starting the containers I get the following. I don't fully understand what is going on with the ports.

CONTAINER ID        IMAGE                 COMMAND                  CREATED              STATUS              PORTS                              NAMES
997f816ddff3        backend:1.0           "/bin/sh -c 'pytho..."   About a minute ago   Up About a minute   0.0.0.0:9090->9090/tcp             backendservices_backend_1
759546109a66        login:1.0             "/bin/sh -c 'pytho..."   About a minute ago   Up About a minute   0.0.0.0:7070->7070/tcp, 9090/tcp   backendservices_login_1
a2a26b72dd0c        login_db:27.04.2018   "docker-entrypoint..."   About a minute ago   Up About a minute   5432/tcp, 0.0.0.0:5433->5433/tcp   backendservices_login_db_1
3f97de7fc41e        main_db:26.03.18   "docker-entrypoint..."   About a minute ago   Up About a minute   0.0.0.0:5432->5432/tcp             backendservices_db_1
1a61e741ccba        ppt_generator:1.0     "/bin/sh -c 'pytho..."   About a minute ago   Up About a minute   0.0.0.0:6060->6060/tcp             backendservices_ppt_1

Both my db dockerfiles are essentially identical except for the port number I expose:

FROM postgres:9.6.3
ENV POSTGRES_USER ludo
ENV POSTGRES_PASSWORD password
ENV POSTGRES_DB login
EXPOSE 5433

ADD db_dump.sql /docker-entrypoint-initdb.d

This is the error I get:

backend_1   | Traceback (most recent call last):
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect
backend_1   |     return fn()
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 387, in connect
backend_1   |     return _ConnectionFairy._checkout(self)
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 766, in _checkout
backend_1   |     fairy = _ConnectionRecord.checkout(pool)
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 516, in checkout
backend_1   |     rec = pool._do_get()
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1138, in _do_get
backend_1   |     self._dec_overflow()
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
backend_1   |     compat.reraise(exc_type, exc_value, exc_tb)
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
backend_1   |     raise value
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1135, in _do_get
backend_1   |     return self._create_connection()
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 333, in _create_connection
backend_1   |     return _ConnectionRecord(self)
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 461, in __init__
backend_1   |     self.__connect(first_connect_check=True)
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 651, in __connect
backend_1   |     connection = pool._invoke_creator(self)
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect
backend_1   |     return dialect.connect(*cargs, **cparams)
backend_1   |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 393, in connect
backend_1   |     return self.dbapi.connect(*cargs, **cparams)
backend_1   |   File "/usr/local/lib/python3.6/site-packages/psycopg2/__init__.py", line 130, in connect
backend_1   |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
backend_1   | psycopg2.OperationalError: could not connect to server: Connection refused
backend_1   |   Is the server running on host "localhost" (127.0.0.1) and accepting
backend_1   |   TCP/IP connections on port 5432?
backend_1   | could not connect to server: Cannot assign requested address
backend_1   |   Is the server running on host "localhost" (::1) and accepting
backend_1   |   TCP/IP connections on port 5432?
backend_1   | 

Why is the db not running on port 5432? It used to work when I only had one database and now with two it seems to be confused...?

UPDATE I can access the databases respectively on ports 5432 and 5433 locally. However, from my backend container I can't. My backend container seems to not be receiving anything running on port 5432. How do I make db container port 5432 visible to the backend container?

Answer 1

UPDATE

After a lot of fiddling around I got it to work. As @Iarwa1n suggested, you map one db as such "5432:5432" and the other as such "5433:5432". The error I encountered was due to how I was calling postgres from the application itself. It is important to realize the postgres host is not localhost anymore, but whatever name you gave your database service in docker-compose.yaml. In my case; db for backend and login_db for the login service. Additionally, I had to change my driver from postgresql to postgres – not sure why this is...

As such, my db_url ended up looking like this from within my python backend app:

postgres://ludo:password@db:5432/main_db

And defined in this way:

DATABASE_CONFIG = {
    'driver': 'postgres',
    'host': 'db',
    'user': 'ludo',
    'password': 'password',
    'port': 5432,
    'dbname': main_db
}
db_url = '{driver}://{user}:{password}@{host}:{port}/{dbname}'.format(database_config)

Two things to note:

1) Regardless of how you mapped your ports, you always have to connect to postgres default port 5432 from within your app

2) If you're using the requests python library (as was I) then make sure to change the url appropriately as well. For example I had a ppt service I was calling via the requests library and I had to change the url to: 'http://ppt:6060/api/getPpt' instead of 'http://localhost:6060/api/getPpt'

Answer 2

The postgres process on both your machines listens to 5432, as this is the default behavior. Just by EXPOSE you do not change that, this just means, that the container exposes port 5433 instead of 5432. But on this port no process is listening. Try to change the following:

FROM postgres:9.6.3
ENV POSTGRES_USER ludo
ENV POSTGRES_PASSWORD password
ENV POSTGRES_DB login
EXPOSE 5432
ADD db_dump.sql /docker-entrypoint-initdb.d

and then change the docker-compose like this:

login_db:
    image: login_db:27.04.2018
    restart: always
    build: ./login_db
    ports:
      - "5433:5432"

Now you can access the "db" at 5432 (from the host) and "login_db" at 5433 from the host. Note, that you still need to use 5432 to access one of the dbs from another container.