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

Answer 1

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.

Answer 2

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.