How can I set path to load data from CSV file into

问题:

I would like to load data from CSV file into PostgreSQL database in Docker. I run:

docker exec -ti my project_db_1 psql -U postgres

Then I select my database:

\c myDatabase

Now I try to load data from myfile.csv which is in the main directory of the Django project into backend_data table:

\copy backend_data (t, sth1, sth2) FROM 'myfile.csv' CSV HEADER;

However I get error:

myfile.csv: No such file or directory

It seems to me that I tried every possible path and nothing works. Any ideas how can I solve it? This is my docker-compose.yml:

version: '3'

services:
  db:
    image: postgres
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
  django:
    build: .
    command: python3 manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    depends_on:
      - db

回答1:

The easiest way is to mount a directory into the postgres container, place the file into the mounted directory, and reference it there.

We are actually mounting the pgdata directory, to be sure that the postgres data lives even if we recreate the postgres docker container. So, my example will also use pgdata:

services:
  db:
    image: postgres
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
    volumes:
    volumes:
      - "<path_to_local_pgdata>:/var/lib/postgresql/data/pgdata"

Place myfile.csv into <path_to_local_pgdata> (relative to directory containing the config or absolute path). The copy command then looks like this:

\copy backend_data (t, sth1, sth2) FROM '/var/lib/postgresql/data/pgdata/myfile.csv' CSV HEADER;

回答2:

you need to mount the path of the myfile.csv in the db container if you are running the command in that container.

you might have mounted the file only in django service.

possible docker-compose.yml

version: '3'

services:
  db:
    image: postgres
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
    volumes:
      - <path_to_csv_in_local>:<path_of_csv_in_db_container>
  django:
    build: .
    command: python3 manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    depends_on:
      - db

you haven't created a mount of your db. this will act fatal once you close your database container (you will lose all your data). postgresql container stores data in /var/lib/postgresql/data. you need to mount this this path to your local system to maintain the data even if the container closes.

volumes:
  - <path_of_db_in_local_system>:/var/lib/postgresql/data