Question

0

634

Views

Initialize Postgres db in Docker Compose

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?

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-04-21T20:35:19.158Z

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.

0

634