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

Is that your full compose file? No volumes defined? If you did define a volume, did it already exist when you run your most recent test? — BMitch
– BMitch, Commented Dec 23, 2020 at 21:08
@BMitch it initially had volumes defined but i've since pruned and removed the images and containers and rebuilt entirely. — orpheus
– orpheus, Commented Dec 23, 2020 at 21:36
Have you checked the container logs to see if there are any messages related to that script? — larsks
– larsks, Commented Dec 23, 2020 at 22:50
I wasn't running it in the background but I'll update the post with logs — orpheus
– orpheus, Commented Dec 23, 2020 at 22:56

larsks · Accepted Answer · 2020-12-23 22:58:55Z

3

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.

answered Dec 23, 2020 at 22:58

larsks

318k49 gold badges473 silver badges482 bronze badges

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

1 Comment

orpheus Over a year ago

Thanks! This solved my problem, but in case anyone stumbles across this, I pruned the containers and it still wasn't running the script. I had to rebuild without cache.

Collectives™ on Stack Overflow

Postgres Docker container does not run create SQL script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related