Can't reach postgres container from app container

Question

I have a docker-composer.yml that is setting up two services: server and db. The Node.js server, which is the server service, uses pg to connect to the PostgreSQL database; and the db service is a PostgreSQL image.

On the server startup, it tries to connect to the database but gets a timeout.

---

docker-compose.yml

version: '3.8'

services:
  server:
    image: myapi
    build: .
    container_name: server
    env_file: .env
    environment:
      - PORT=80
      - DATABASE_URL=postgres://postgres:postgres@db:15432/mydb
      - REDIS_URL=redis://redis
    ports:
      - 3000:80
    depends_on:
      - db
    command: node script.js
    restart: unless-stopped

  db:
    image: postgres
    container_name: db
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: mydb
    ports:
      - 15432:15432
    volumes:
      - db-data:/var/lib/postgresql/data
    command: -p 15432
    restart: unless-stopped

volumes:
  db-data:

Edit: code above changed to remove links and expose.

---

db service output:

db        | 
db        | PostgreSQL Database directory appears to contain a database; Skipping initialization
db        | 
db        | 2020-11-05 20:18:15.865 UTC [1] LOG:  starting PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db        | 2020-11-05 20:18:15.865 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 15432
db        | 2020-11-05 20:18:15.865 UTC [1] LOG:  listening on IPv6 address "::", port 15432
db        | 2020-11-05 20:18:15.873 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.15432"
db        | 2020-11-05 20:18:15.880 UTC [25] LOG:  database system was shut down at 2020-11-05 20:18:12 UTC
db        | 2020-11-05 20:18:15.884 UTC [1] LOG:  database system is ready to accept connections

---

script.js - used by the command from the server service.

const pg = require('pg');

console.log(process.env.DATABASE_URL);

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  connectionTimeoutMillis: 5000,
});
pool.connect((err, _, done) => {
  if (err) {
    console.error(err);
    done(err);
  }
  done();
});
pool.query('SELECT NOW()', (err, res) => {
  console.log(err, res);
  pool.end();
});

const client = new pg.Client({
  connectionString: process.env.DATABASE_URL,
  connectionTimeoutMillis: 5000,
});
client.connect(console.error);
client.query('SELECT NOW()', (err, res) => {
  console.log(err, res);
  client.end();
});

server service output:

NOTE: The first line is the output of the first console.log call from script.js.

NOTE: Since the server service is set up with restart: unless-stopped, it will repeat this output forever.

server    | postgres://postgres:postgres@db:15432/mydb
server    | Error: Connection terminated due to connection timeout
server    |     at Connection.<anonymous> (/home/node/app/node_modules/pg/lib/client.js:255:9)
server    |     at Object.onceWrapper (events.js:421:28)
server    |     at Connection.emit (events.js:315:20)
server    |     at Socket.<anonymous> (/home/node/app/node_modules/pg/lib/connection.js:78:10)
server    |     at Socket.emit (events.js:315:20)
server    |     at emitCloseNT (net.js:1659:8)
server    |     at processTicksAndRejections (internal/process/task_queues.js:79:21)
server    |     at runNextTicks (internal/process/task_queues.js:62:3)
server    |     at listOnTimeout (internal/timers.js:523:9)
server    |     at processTimers (internal/timers.js:497:7)
server    | Error: Connection terminated due to connection timeout
server    |     at Connection.<anonymous> (/home/node/app/node_modules/pg/lib/client.js:255:9)
server    |     at Object.onceWrapper (events.js:421:28)
server    |     at Connection.emit (events.js:315:20)
server    |     at Socket.<anonymous> (/home/node/app/node_modules/pg/lib/connection.js:78:10)
server    |     at Socket.emit (events.js:315:20)
server    |     at emitCloseNT (net.js:1659:8)
server    |     at processTicksAndRejections (internal/process/task_queues.js:79:21)
server    |     at runNextTicks (internal/process/task_queues.js:62:3)
server    |     at listOnTimeout (internal/timers.js:523:9)
server    |     at processTimers (internal/timers.js:497:7) undefined
server    | Error: timeout expired
server    |     at Timeout._onTimeout (/home/node/app/node_modules/pg/lib/client.js:95:26)
server    |     at listOnTimeout (internal/timers.js:554:17)
server    |     at processTimers (internal/timers.js:497:7)
server    | Error: Connection terminated unexpectedly
server    |     at Connection.<anonymous> (/home/node/app/node_modules/pg/lib/client.js:255:9)
server    |     at Object.onceWrapper (events.js:421:28)
server    |     at Connection.emit (events.js:315:20)
server    |     at Socket.<anonymous> (/home/node/app/node_modules/pg/lib/connection.js:78:10)
server    |     at Socket.emit (events.js:315:20)
server    |     at emitCloseNT (net.js:1659:8)
server    |     at processTicksAndRejections (internal/process/task_queues.js:79:21) undefined
server    | postgres://postgres:postgres@db:15432/mydb
...

---

From the host computer, I can reach the PostgreSQL database at the db service, connecting successfully, using the same script from the server service.

The output of the script running from the host computer:

➜  node script.js
postgres://postgres:postgres@localhost:15432/mydb
null Client { ... }
undefined Result { ... }
null Result { ... }

This output means the connection succeeded.

---

In summary:

I can't reach the db container from the server container, getting timeouts on the connection, but I can reach the db container from the host computer, connecting successfully.

---

Considerations

First, thanks for the answer so far. Addressing some points raised:

• Missing network:

  It isn't required because docker-compose has a default network. A tested with a custom network but it didn't work either.

• Order of initialization:

  I'm using depends_on to ensure the db container is started first but I know it isn't ensuring the database is in fact initialized first then the server. It isn't the problem because the server breaks when a timeout happens and it runs again because it is set up with restart: unless-stopped. So if the database is still initializing on the first or second try to start the server, there is no problem because the server will continue to be restarted until it succeeds in the connection (which never happened.)

• UPDATE:

  From the server container, I could reach the database at the db service using psql. I still can't connect from the Node.js app there.

  The DATABASE_URL isn't the problem because the URI I used in the psql command is the same URI used by the script.js and printed by the first console.log call there.

  Command-line used:

  docker exec -it server psql postgres://postgres:postgres@db:15432/mydb
  

Edit: Improved code by removing the dependency for Sequelize. Now it uses only pg and calls the script directly.

Answer 1

Thanks for providing the source to reproduce the issue. No issues in the docker-compose file as you have already ruled out.

The problem lies between your Dockerfile and the version of node-pg that you are using.

You are using node:14-alpine and pg: 7.18.2. Turns out there is a bug on node 14 and earlier versions of node-pg.

Solution is either downgrade to node v12 or use latest version of node-pg which is currently 8.4.2 (fix went in on v8.0.3).

I have verified both these solutions on the branch you provided and they work.

Answer 2

This isn't a complete answer; I don't have your code handy so I can't actually test the compose file. However, there are a few issues there I'd like to point out:

• The links directive is deprecated.

  The links is a legacy option that was used before Docker introduced user-defined networks and automatic DNS support. You can just get rid of it. Containers in a compose file are able to refer to each other by name without.

• The expose directive does nothing. It can be informative in for example a Dockerfile as a way of saying, "this image will expose a service on this port", but it doesn't actually make anything happen. It's almost entirely useless in a compose file.

• The depends_on directive is also less useful than you would think. It will indeed cause docker-compose to bring up the database container first, but it the container is considered "up" as soon as the first process has started. It doesn't cause docker-compose to wait for the database to be ready to service requests, which means you'll still run into errors if your application tries to connect before the database is ready.

  The best solution to this is to built database re-connection logic into your application so that if the database ever goes down (e.g. you restart the postgres container to activate a new configuration or upgrade the postgres version), the app will retry connections until it is successful.

  An acceptable solution is to include code in your application startup that blocks until the database is responding to requests.

Answer 3

The problem has nothing to do with docker. To test that, perform following actions :

By using this docker-compose.yml file:

version: '3.8'

services:
  app:
    image: ubuntu
    container_name: app
    command: sleep 8h

  db:
    image: postgres
    container_name: db
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: mydb
    expose:
      - '15432'
    ports:
      - 15432:15432
    volumes:
      - db-data:/var/lib/postgresql/data
    command: -p 15432
    restart: unless-stopped

volumes:
  db-data:

Perform a docker exec -it app bash to go into container app then install postgresql-client with apt install -y postgresql-client`.

Command psql -h db -p 15432 -U postgres -W succeeded !

Check pg configuration

You say that pg use environment variable DATABASE_URL to reach postgresql. I'm not sure :

From https://node-postgres.com/features/connecting, we can found this example :

$ PGUSER=dbuser \
  PGHOST=database.server.com \
  PGPASSWORD=secretpassword \
  PGDATABASE=mydb \
  PGPORT=3211 \
  node script.js

And this sentence :

node-postgres uses the same environment variables as libpq to connect to a PostgreSQL server.

In libpq documentation, no DATABASE_URL.

To adapt example provided in pg documentation with your docker-compose.yml file, try with following file (I only changed environments variables of app service) :

version: '3.8'

services:
  server:
    image: myapi
    build: .
    container_name: server
    env_file: .env
    environment:
      - PORT=80
      - PGUSER=postgres
      - PGPASSWORD=postgres
      - PGHOST=db
      - PGDATABASE=mydb
      - PGPORT=15432
      - REDIS_URL=redis://redis
    ports:
      - 3000:80
    depends_on:
      - db
    command: node script.js
    restart: unless-stopped

  db:
    image: postgres
    container_name: db
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: mydb
    ports:
      - 15432:15432
    volumes:
      - db-data:/var/lib/postgresql/data
    command: -p 15432
    restart: unless-stopped

volumes:
  db-data: