Docker Community Forums

Share and learn in the Docker community.

Error with docker-entrypoint-initdb.d/init.sql: ERROR: relation "mytable" does not exist

Hi,

I have develop a Django app and try to “dockerize” it but I am newbie in Docker and docker-compose

I want to initialize a postgres database based on init.sql file, placed in docker-entrypoint-initdb.d folder in my container
when running docker-compose up -d -build, my 2 containers are built and running but database is empty

docker logs <my_db_container> show me an error because the first table I try to INSERT doe not exist.

what I do wrong?
thanks for help

Dockerfile

FROM python:3.8.3-alpine

WORKDIR /usr/src/app

ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

RUN apk update && apk add postgresql-dev gcc python3-dev musl-dev
RUN apk --update add libxml2-dev libxslt-dev libffi-dev gcc musl-dev libgcc openssl-dev curl
RUN apk add jpeg-dev zlib-dev freetype-dev lcms2-dev openjpeg-dev tiff-dev tk-dev tcl-dev
RUN pip3 install psycopg2 psycopg2-binary

COPY requirements/ requirements/

RUN pip install --upgrade pip && pip install -r requirements/dev.txt

COPY ./entrypoint.sh .
COPY . .

ENTRYPOINT [ “/usr/src/app/entrypoint.sh” ]

docker-compose.yml

version: ‘3.7’

services:
web:
build: ./app
restart: always
command: python manage.py runserver 0.0.0.0:8000
volumes:
- ./app/:/usr/src/app
ports:
- 8000:8000
env_file:
- ./.env.dev
depends_on:
- db

db:
    image: postgres:12.0-alpine
    restart: always
    volumes:
        - postgres_data:/var/lib/postgres/data/
        - ./imports/init.sql:/docker-entrypoint-initdb.d/init.sql
        - ./imports/adm_ran.csv:/imports/adm_ran.csv
        - ./imports/adm_med.csv:/imports/adm_med.csv

    environment:
        - POSTGRES_USER=user
        - POSTGRES_PASSWORD=user
        - POSTGRES_DB=db_dev

volumes:
postgres_data:

My current script looks something like this:
!/bin/sh
sudo -u postgres createdb mydb
sudo -u postgres psql mydb
CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));
CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));
exit 0;
As of right now, when I execute the script it creates the database and logs me into it. However, after it logs me into mydb the script does not continue to do the CREATE TABLE commands.
I’m new when it comes to this stuff so I have no idea if I’m going about this the right way. Basically I just want a script that creates a database and then a few tables inside it (using PostgreSQL).
#!/bin/bash
psql -d mydb -U postgres -c “CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));”
psql -d mydb -U postgres -c “CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));”
You can run all queries with -c option.
One method is to use a here-doc (this is a shell feature and depends on what shell you’re running):
psql mydb <<EOF
CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));
CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));
EOF
Another would be to write the commands to a sufficiently protected (with permissions) temporary file and then pass that as a script to postgres with the -f FILENAME option.
After editing the hba_conf file to “local all postgres trust” (I used trust so I wouldn’t have to supply a password for my scripts), I used TraviJuu’s suggestion and it now my script works flawlessly. Here is what the end result looks like.
!/bin/sh
sudo -u postgres createdb mydb
psql -w -d mydb -U postgres -c “CREATE TABLE person (first VARCHAR(32), last VARCHAR(32));”
psql -w -d mydb -U postgres -c “CREATE TABLE thing (size VARCHAR(32), price VARCHAR(32));”
exit 0;