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

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.

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 :)

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/

If you don’t specify the PGUSER environment variable, then psql will assume you want to use the current OS user as your database user name. In this case, you are using root as your OS user, and you will attempt to log in as root, but that user doesn’t exist in the database.

You’ll need to either call psql -U postgres, or su - Postgres first

See also the postgresql documentation

UPDATE: Someone suggested changing PGUSER to POSTGRES_USER -- this is actually incorrect. Postgres looks for PGUSER in the environment, but if you're using Docker, you'll tell Docker the correct user by using POSTGRES_USER, which gets assigned to PGUSER -- see the entrypoint source code