Connect to postgresql container from another container (Docker)

Question

I have am trying to follow this tutorial and set up a postgresql container.

I have the following script:

#!/bin/bash
# wait-for-postgres.sh

set -e

host="$1"
shift
cmd="$@"

until psql -h "$host" -U "postgres" -c '\l'; do
  >&2 echo "Postgres is unavailable - sleeping"
  sleep 1
done

>&2 echo "Postgres is up - executing command"
exec $cmd

And the following docker-compose.yml:

version: '2'
services:
  server:
    build: .
    ports:
      - 3030:3030
    depends_on:
      - database
    command: ["./setup/wait-for-postgres.sh", "localhost:5432", "--", "node", "src"]
  database:
    image: postgres
    environment:
      - "POSTGRES_USER=postgres"
      - "POSTGRES_PASSWORD=postgres"
      - "POSTGRES_DB=tide_server"
    ports:
      - 5432:5432

The problem is that when I run docker-compose up I get the following error:

server_1    | Postgres is unavailable - sleeping
server_1    | psql: could not translate host name "192.168.64.2:5432" to address: Name or servi
ce not known
server_1    | Postgres is unavailable - sleeping
server_1    | psql: could not translate host name "192.168.64.2:5432" to address: Name or servi
ce not known
server_1    | Postgres is unavailable - sleeping
server_1    | psql: could not translate host name "192.168.64.2:5432" to address: Name or servi
ce not known

Now I have tried setting the host as database, localhost, 0.0.0.0, and even the containers IP but nothing works, I have no idea what it should be or how to debug it, I am not 100% sure how docker-compose links the containers.

Solution 1

do not use depends_on. try it with "links"

    version: '2'
    services:
      server:
        build: .
        ports:
          - 3030:3030
        links:
          - database
        #environment could be usefull too
        environment:
            DATABASE_HOST: database
        command: ["./setup/wait-for-postgres.sh", "localhost:5432", "--", "node", "src"]
      database:
        image: postgres
        environment:
          - "POSTGRES_USER=postgres"
          - "POSTGRES_PASSWORD=postgres"
          - "POSTGRES_DB=tide_server"
        ports:
          - 5432:5432

for more informations https://docs.docker.com/compose/compose-file/#links

Solution 2

The tutorial skips over a few things, and is confusing in that it mentions the wait-for-it.sh script, but then shows a much simplified version that doesn't work if you pass hostname:port as one argument to it.

I had a crack at getting this to work and both for future me and others I will add the steps below. I did this on MacOS, and have both docker and docker-compose installed as well as nodejs.

I don't have your node app handy so I used the one as described here https://nodejs.org/de/docs/guides/nodejs-docker-webapp/

I have the following directory structure:

/src/package.json
/src/server.js
/.pgpass
/docker-compose.yml
/Dockerfile
/wait-for-postgres.sh

The contents of these files is listed below.

Steps

1. From the ./src directory run $ npm install (creates package-lock.json)
2. Fix pgpass permissions with $ chmod 600 .pgpass
3. Make the script executable $ chmod +x wait-for-postgres.sh
4. From the root directory $ docker-compose up

It will pull the postgres image and build the node app container. When that's done it will wait for postgres and when postgres is up you'll see it ready.

Files

The src files are exactly as per the node js dockerize link above

/src/package.json

{
    "name": "docker_web_app",
    "version": "1.0.0",
    "description": "Node.js on Docker",
    "author": "First Last <[email protected]>",
    "main": "server.js",
    "scripts": {
      "start": "node server.js"
    },
    "dependencies": {
      "express": "^4.16.1"
    }
  }

/src/server.js

'use strict';

const express = require('express');

// Constants
const PORT = 8080;
const HOST = '0.0.0.0';

// App
const app = express();
app.get('/', (req, res) => {
  res.send('Hello world\n');
});

app.listen(PORT, HOST);
console.log(`Running on http://${HOST}:${PORT}`);

.pgpass

This uses the username:password postgres:postgres and is purely for development demo purposes. In the wild you will use some other method of secrets management and never ever commit a pgpass file to version control

#host:port:db:user:pass
db:5432:*:postgres:postgres

docker-compose.yml

• I have added the wait-for-postgres.sh script as a managed volume, in the original question it was bundling it in with the app src which was weird.

• I have also mounted the .pgpass file in the root user's home directory, which psql will look in for auto-password completion. If you don't have some method of supplying this then you'll get an error:

  psql: fe_sendauth: no password supplied

• Notice the command for the server container is referring to database which is a valid docker-compose internal dns name for the postgres container.

version: '2'
services:

  server:
    build: .
    ports:
      - 3030:3030
    depends_on:
      - database
    volumes:
      - ./wait-for-postgres.sh:/usr/app/setup/wait-for-postgres.sh
      - ./.pgpass:/Users/root/.pgpass
    command: ["/usr/app/setup/wait-for-postgres.sh", "database", "--", "node", "src"]

  database:
    image: postgres
    environment:
      - "POSTGRES_USER=postgres"
      - "POSTGRES_PASSWORD=postgres"
      - "POSTGRES_DB=tide_server"
    ports:
      - 5432:5432

Dockerfile

• I have modified this from the node js tutorial, pinning it to the Debian "buster" version and also installing psql which it needs for that script.

FROM node:10-buster

RUN apt-key adv --keyserver hkp://p80.pool.sks-keyservers.net:80 --recv-keys B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8

RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main" > /etc/apt/sources.list.d/pgdg.list && \
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -


RUN apt-get -y update - && \
    apt-get -y install libpq-dev && \
    apt-get -y install postgresql-client-11

# Create app directory
WORKDIR /usr/src/app

# Install app dependencies
# A wildcard is used to ensure both package.json AND package-lock.json are copied
# where available (npm@5+)
COPY package*.json ./

RUN npm install
# If you are building your code for production
# RUN npm ci --only=production

# Bundle app source
COPY . .

EXPOSE 8080
CMD [ "node", "server.js" ]

wait-for-postgres.sh

• I have modified the script very slightly because I ran the "shellcheck" linter and it complained about a few things. I realise this script is from the docker tutorial page.

#!/bin/bash
# wait-for-postgres.sh

set -e

host="$1"
shift
cmd="$*"

export PGPASSFILE=./pgpass

until psql -h "$host" -U "postgres" -c '\l'; do
  >&2 echo "Postgres is unavailable - sleeping"
  sleep 1
done

>&2 echo "Postgres is up - executing command"
exec "$cmd"

Solution 3

May be an old thread to answer but I have been using depends_on with the following docker-compose file

version: '3.4'

volumes:
  postgres_data:
      driver: local

services:
  postgres:
      image: postgres
      volumes:
        - ./postgres_data:/var/lib/postgresql:rw
        - ./deployments:/opt/jboss/wildfly/standalone/deployments:rw
      environment:
        POSTGRES_DB: keycloak
        POSTGRES_USER: keycloak
        POSTGRES_PASSWORD: password
      ports:
        - 5432:5432
   keycloak:
      image: jboss/keycloak
      environment:
        POSTGRES_ADDR: postgres
        POSTGRES_DATABASE: keycloak
        POSTGRES_USER: keycloak
        POSTGRES_PASSWORD: password
        KEYCLOAK_USER: admin
        KEYCLOAK_PASSWORD: Pa55w0rd
      ports:
        - 8080:8080
        - 9990:9990
      depends_on:
        - postgres

Solution 4

The problem here is the host itself.

psql -h "$host" -U "" -c '\l'

You are passing a wrong HOSTNAME "localhost:5432" / "192.168.64.2:5432"

What I did is setup a ~/.pgpass that has localhost:5432:DB:USER:PASSWORD

and instead of passing "localhost:5432", omit the port. Just use "localhost"

This works for me ...

View more solutions

Comments

[Otis Wright]

Thank you for that, I have tried links and depends_on and both and still had no luck, cheers.

[Rodel]

It will help to specify the database using -d option

[Davos]

Not sure why this got downvoted, it's pretty much correct, if a little brief.

[Davos]

links is deprecated docs.docker.com/compose/compose-file/#links , the advice is to use user defined networks instead. depends_on is fine, but it doesn't wait for the db to be ready, only for the container to have started docs.docker.com/compose/compose-file/#depends_on. It is intentional as per the docs "tutorial" link provided by the OP in this question, which is why they suggest wait-for-it and other ways to explicitly handle robust connection and reconnection from your application, not the container infrastructure.

[Davos]

This appears to be a reply/comment on someone else's answer, not an answer to the OP's question in itself. Probably worth combining this (useful) point with your other answer.