Initialize Postgres db in Docker Compose

Question

I have the following docker-compose.yml file:

version: '3'

services:
  postgres:
    image: postgres
    container_name: postgres
    ports:
      - "5431:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=anime
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

This configuration starts a Postgres database. In volume I defined init.sql, which should set up a table:

CREATE TABLE anime ( 
  anime_id INT PRIMARY KEY,
  title TEXT
);

Then, I would like to fill the Postgres database with data from the CSV file.

I tried to add another volume to docker-compose:

 - ./preload.sql:/preload/preload.sql

with that script:

copy anime FROM 'docker/data/AnimeList.csv' DELIMITER ',' CSV HEADER;

The CSV file is located in the data folder, on the same level as docker-compose.yml.

But it is not working. The database is created correctly, but it doesn't have the table and data. When I connect to Docker container, run 'psql command and try to get anime table, I get the following error:

Did not find any relation named "anime".

My question is: how to preload the Postgres container with the CSV data file in docker-compose?

Do you have any error? Can you share the final docker-compose.yml file you're using? — whites11
– whites11, Commented Jun 18, 2019 at 9:00

hY8vVpf3tyR57Xib · Accepted Answer · 2020-12-11 13:22:30Z

43

I managed to make it work using custom Dockerfile, here's my solution:

Project structure

data/
  datasource.csv
db/
  scripts/
    1_init.sql
    2_copy.sql
  Dockerfile
docker-compose.yml

Files

CSV file is located in data folder inside of the project.

In the project folder there is the following docker-compose.yml file:

version: '3.3'

services:
  db:
    build: ./db
    container_name: postgres
    ports:
      - "5431:6666"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=db_name
    volumes:
      - ./data:/data

Dockerfile contains:

FROM postgres:alpine
ADD scripts/1_init.sql /docker-entrypoint-initdb.d
ADD scripts/2_copy.sql /docker-entrypoint-initdb.d
RUN chmod a+r /docker-entrypoint-initdb.d/*
EXPOSE 6666

1_init.sql body:

CREATE TABLE table_name
(
   --statement body
);

And 2_copy.sql:

COPY table_name FROM '/data/datasource.csv' DELIMITER ',' CSV HEADER;

Explanation

1_init.sql creates the DB table, it has to have the same column names as in CSV file. 2_copy.sql is responsible for copying data from the CSV to postgres.

Dockerfile uses postgres image and copies all *.sql files to /docker-entrypoint-initdb.d/. Later, all files are executed in alphanumerical order, that's why *.sql files start with digits. Finally, port 6666 is exposed.

docker-compose.yml builds the Dockerfile from db folder and make it accessible through 5431 port. As environmental properties basic postgres properties are used. And at the end data folder with CSV file is copied to the container.

edited Dec 11, 2020 at 13:22

hY8vVpf3tyR57Xib

3,9659 gold badges51 silver badges98 bronze badges

answered Jun 26, 2019 at 13:41

Forin

1,6492 gold badges26 silver badges49 bronze badges

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

2 Comments

yoyoyoyo123 Over a year ago

this was an awesome answer and should have more upvotes. Solved my problem!

Markus Over a year ago

You simply could have mounted the SQL files to where you copied them in your image. Another option would be to use a psql-client-Docker-Image.

Collectives™ on Stack Overflow

Initialize Postgres db in Docker Compose

1 Answer 1

Project structure

Files

Explanation

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Project structure

Files

Explanation

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related