How can I populate a postgres table using docker-compose?

Question

I am trying to populate a postgres table with data from a csv file when running docker-compose up -d, however, all the methods I have tried end up saying the file could not be found.

One of the ways I was trying was using golang-migrate and the migrations to create the table work, but when attempting to run COPY customers FROM 'customers.csv' CSV HEADER; it gives the following error:

error: migration failed: could not open file "customers.csv" for reading: No such file or directory

My migrations step looks like this:

  migrations:
    image: migrate/migrate
    command: -database postgres://postgres:password@database:5432/database?sslmode=disable -path /migrations up
    volumes:
      - ./migrations:/migrations

The customers.csv file is located in my migrations directory along with my migration sql files to create and drop the table (both of which work fine) along with a third migration sql file with the COPY query. I was under the impression that by setting the volume to ./migrations:/migrations it would map all files from my ./migrations directory in my project to /migrations in the container, so I really don't understand how it can't find the file.

Is there something else I need to do to get my csv file to my docker container or is there a better way to do this?

try passing the entire path in the COPY command like `/migrations/customer.csv` or `./customer.csv` maybe it will work — Neenad, Jul 03 '22 at 09:41
Other way I prefer is to do migration as a part of your application startup and avoid this extra step through docker-compose setup — Neenad, Jul 03 '22 at 09:41

score 0 · Answer 1 · answered Aug 04 '22 at 15:26

It seems the migration files themselves are found correctly, via the -path /migrations flag, but this doesn't change the working directory. The COPY customers FROM 'customers.csv' CSV HEADER; command will try to read customers.csv from the working directory, since it is not an absolute path.

You could either change the command to COPY customers FROM '/migrations/customers.csv' CSV HEADER; or change the working directory of the container via the working_dir docker-compose option.

  migrations:
    image: migrate/migrate
    command: -database postgres://postgres:password@database:5432/database?sslmode=disable -path /migrations up
    volumes:
      - ./migrations:/migrations
    working_dir: /migrations

The second option may be slightly nicer, as it doesn't require a SQL code change.

How can I populate a postgres table using docker-compose?

1 Answers1