You most probably started the container without the env variables set. The db was then initialized without user or DB info.

You need to delete the volume and try again.

Credit: https://github.com/docker-library/postgres/issues/453#issuecomment-393939412

The default data directory is /var/lib/postgresql/data. You need to adjust your volume:

volumes:
  - ./log-directory:/var/lib/postgresql/data

You can read more about it here

Fixed it. Solution: changed the -p argument in the docker command line to -p 5433:5432. Also changed the last 2 execution lines:

psql -U postgres -d dev -h localhost -p 5433 -f schema.sql
psql -U postgres -d dev -h localhost -p 5433  -f data.sql

Why was port 5432 breaking everything? I have no clue either. It may have to do with the fact that I have a local instance of Postgres installed and running psql connected to the local instance instead of the Docker container.

If my explanation seems wrong, please correct me in the comments as I am still learning :)

I've solved this problem setting this line in my docker-compose file:

healthcheck:
  test: [ "CMD-SHELL", "pg_isready -d $${POSTGRES_DB} -U $${POSTGRES_USER}"]

Have you tried setting POSTGRES_DB variable in your docker-compose.yml environment ?

 image: postgres
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
      POSTGRES_DB: premiership
      PGDATA: /data/postgres
    volumes:

see https://hub.docker.com/_/postgres :

POSTGRES_DB

This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.

Found the issue:

I had PSQL installed locally (via brew) which was showing the error message.

Uninstalled it. Then used PgAdmin to connect to the container. And it's working fine now.

The problem was simple enough that my computer was already running an instance of Postgres that I was not aware was still running (not inside Docker) on :5432, checked with:

$ lsof -n -i:5432 | grep LISTEN

So I remembered I installed it via https://gist.github.com/sgnl/609557ebacd3378f3b72, I ran

$ pg-stop

And then I had no problem connecting to the Docker instance.

Edit (2019/07/02)

This question recently passed 10,000 views, so I thought I should elaborate more on why this happened.

Usually running through docker, using python and connecting to a postgres database requires you to install psycopg2, via pip3 install psycopg2, but if you run this command you will get:

Error: pg_config executable not found.

This is because psycopg2 requires an operating system install of the postgres libraries:

yum install postgresql-devel
apt-get install postgresql-client

Now, on a Mac, you will need to do the same with brew:

brew install postgresql

One thing that I didn't realise, is that on Mac, doing the above will not only install required libraries, but also start a database on :5432. Because this was all done in the background, it didn't occur to me that this was the problem as none of the usual errors popped up to inform that the port was being used, etc...

Are you sure your database init script is getting called? The postgresql docker uses files in /docker-entrypoint-initdb.d/* as you can see in the Dockerfile here: https://github.com/docker-library/postgres/blob/443c7947d548b1c607e06f7a75ca475de7ff3284/9.4/docker-entrypoint.sh#L77

You can test this by bringing up ONLY the database, by running something like

docker-compose up db

which will ONLY bring up the db container, and then you can docker exec -it in and psql into the postgres processes, or use your exposed port on 5432

Also, you may want to pass in the initialization of the database, username, and password the way the Dockerfile expects them, as env variables as you can see in the same file

Environment Variables The PostgreSQL image uses several environment variables which are easy to miss. While none of the variables are required, they may significantly aid you in using the image.

POSTGRES_PASSWORD This environment variable is recommended for you to use the PostgreSQL image. This environment variable sets the superuser password for PostgreSQL. The default superuser is defined by the POSTGRES_USER environment variable. In the above example, it is being set to "mysecretpassword".

POSTGRES_USER This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used.

POSTGRES_DB This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.

https://hub.docker.com/_/postgres/