Postgres Docker container does not run create SQL script

Question

docker-compose.yml

version: '3'
services:
  db:
    image: "postgres"
    build: .
    ports:
      - "5432:5432"
    env_file:
      - database.env

Dockerfile

FROM postgres:9.3
ADD CreateDB.sql /docker-entrypoint-initdb.d/

CreateDB.sql

CREATE TABLE data_actions (
    username VARCHAR NOT NULL,
    json_payload VARCHAR NOT NULL,
    cache_id VARCHAR,
    action_timestamp TIMESTAMP
)

database.env

DOCKER_USER=docker
DOCKER_DB=docker
DOCKER_PASSWORD=docker

These are the files I'm using to create this container. I've verified that the CreateDB.sql script is copied into the container and is in the correct location. When I build and run the container and check pgAdmin, there are no tables in the docker database. I feel like I'm missing something obvious here?

UPDATE Here are the logs from startup ->

LOG:  database system was interrupted; last known up at 2020-12-23 18:21:18 UTC
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/17A7030
LOG:  redo is not required
LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
LOG:  received smart shutdown request
LOG:  autovacuum launcher shutting down

user_147356 · Answer

The problem is that you have misnamed your environment variables. The postgres image expects the environment names to begin with POSTGRES_ (so, POSTGRES_DB, POSTGRES_USER, etc).

After renaming things in database.env so that I have:

POSTGRES_USER=docker
POSTGRES_DB=docker
POSTGRES_PASSWORD=docker

I can start your compose stack and then:

$ docker-compose exec db psql -U docker
psql (9.3.25)
Type "help" for help.

docker=# \c docker
You are now connected to database "docker" as user "docker".
docker=# \dt
           List of relations
 Schema |     Name     | Type  | Owner
--------+--------------+-------+--------
 public | data_actions | table | docker
(1 row)

docker=#

With your original database.env, Postgres would have created your data_actions table in the default postgres database.

Postgres Docker container does not run create SQL script

1 Answers