Cannot connect to postgres server in docker

Question

I started a postgresql server in docker and exposed the 5432 port by sudo docker run -it -p 5432:5432 9c421f1a239c bash and start the postgres server manually inside the docker container, but cannot connect to it with command: psql -h 172.17.0.63 -U venti. 172.17.0.63 is a right IP, and venti is my pg username. But get error:

psql: could not connect to server: Connection refused
    Is the server running on host "172.17.0.63" and accepting
    TCP/IP connections on port 5432?

My pg_hba.conf looks like this:

local   all             postgres                                peer
host all all 0.0.0.0/0 trust
local all all trust

Connecting to pg server inside container works successfully.

Dockerfile:

FROM ubuntu:12.04
RUN apt-get update

RUN apt-get install -y gcc libc-dev-bin libc6 libc6-dev libssl-dev libkrb5-dev comerr-dev

RUN apt-get install -y postgresql-common libpq-dev postgresql-9.1-postgis --fix-missing
RUN apt-get install -y postgresql postgresql-client

USER postgres

ENV PGDATA /etc/postgresql/9.1/main
ENV LOGDIR  /etc/postgresql/9.1/main/postgresql.log

WORKDIR /usr/lib/postgresql/9.1/bin
USER root
RUN apt-get install -y vim
USER postgres
RUN sed -e '90d' -i /etc/postgresql/9.1/main/pg_hba.conf
RUN sed -e '91d' -i /etc/postgresql/9.1/main/pg_hba.conf

RUN echo "host all all 0.0.0.0/0 trust" >> '/etc/postgresql/9.1/main/pg_hba.conf'
RUN echo "local all all trust" >> '/etc/postgresql/9.1/main/pg_hba.conf'

RUN ./pg_ctl start && sleep 8 && ./createdb pg && ./createdb bloodstone \
    && createuser -Upostgres -s venti \
    && createdb -Uventi -Oventi venti

# ENTRYPOINT ./pg_ctl start && bash -c "while true; do echo "" > /dev/null; sleep 1; done" 

VOLUME $PGDATA
EXPOSE 5432

you are missing the -p (port parameter before 5432:5432 ) in the command if it is not a typo — Chandan Nayak
– Chandan Nayak, Commented Jun 11, 2015 at 5:04
Is your container still running after you exit from the running container ? Becasue you are not starting it with -d parameter — Chandan Nayak
– Chandan Nayak, Commented Jun 11, 2015 at 5:16
@ChandanNayak While debugging, I did not exit the container, so it's still running at least now. — Kane Blueriver
– Kane Blueriver, Commented Jun 11, 2015 at 5:18
@ChandanNayak Seems I did not set the postgresql listen_addresses to public. So I add this line: RUN sed -e "s/[#]\?listen_addresses = .*/listen_addresses = '*'/g" -i '/etc/postgresql/9.1/main/postgresql.conf'. Seems solved the problem, still thanks! — Kane Blueriver
– Kane Blueriver, Commented Jun 11, 2015 at 6:07

Szaman · Accepted Answer · 2018-08-11 04:32:20Z

5

It's merely a misconfiguration. I should have set pg to listen on public addresses or make a port mapping. I fixed this by editing pg config file with sed:

 RUN sed -e "s/[#]\?listen_addresses = .*/listen_addresses = '*'/g" -i '/etc/postgresql/9.1/main/postgresql.conf'

Add this to a proper place in your Dockerfile, and you should be OK.

edited Aug 11, 2018 at 4:32

Szaman

2,4161 gold badge19 silver badges34 bronze badges

answered Mar 2, 2016 at 12:20

Kane Blueriver

4,2984 gold badges35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Curtis Yallop · Accepted Answer · 2017-04-21 17:22:08Z

1

To troubleshoot postgres auth problems in general, look at the postgres log/stderr to see verbose reasons why it failed. (I see your problem is solved but if anyone runs into similar problems)

To find the postgres log location: "show log_destination;" if you have a working psql (eg locally on the postgres server box)

I see yours is set to "/etc/postgresql/9.1/main/postgresql.log". You can connect to the container to see the log with "docker exec -it container_name bash".

Alternately, if a container runs postgres directly, "docker attach db_container_name" views postgres stderr messages.

Note that by default user postgres does not have a password and uses only ident auth.

answered Apr 21, 2017 at 17:22

Curtis Yallop

7,3893 gold badges53 silver badges37 bronze badges

Comments

Tom-db · Accepted Answer · 2017-11-14 07:48:26Z

-1

I also faced similar issue. Try running docker-compose up twice.

The reason is, that in my docker-compose.yml file the DB service was below the Web service in which it was trying to connect to DB which does not exists yet.

**

version: '2'  
services:
 nginx:
    image: nginx:latest
    container_name: ng01
    ports:
      - "8000:8000"
    volumes:
      - ./src:/src
      - ./config/nginx:/etc/nginx/conf.d
      - /static:/static  <--- HERE
    depends_on:
      - web  
 web:
    build: .
    container_name: dg01
    command: bash -c "python /src/IMS/manage.py makemigrations && python /src/IMS/manage.py migrate && gunicorn IMS.wsgi -b
0.0.0.0:8000"
    depends_on:
      - db
    volumes:
      - ./src:/src
      - /static:/static 
    expose:
      - "8000"  
 db:
    image: postgres:latest
    container_name: ps01**

edited Nov 14, 2017 at 7:48

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

answered Nov 14, 2017 at 7:09

prashant

3,3788 gold badges32 silver badges46 bronze badges

1 Comment

gdvalderrama Over a year ago

That shouldn't be happening if you have depends_on

Collectives™ on Stack Overflow

Cannot connect to postgres server in docker

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related