How to properly copy data into postgres database through docker via COPY?

Question

I am looking to run a postgresql docker container to hold some data. I've used docker before for clickhouse but not for postgresql and I'm having a bit of a basic issue here which is loading data with COPY. Here are the details:

os: UBUNTU 16.04 running on a NUC

using docker postgres server container from here:

https://docs.docker.com/engine/examples/postgresql_service/

docker ps shows the server running no problem:

29fb9b39e293        eg_postgresql                     "/usr/lib/postgresql…"   43 hours ago        Up 3 hours              0.0.0.0:5432->5432/tcp   pg_test

I'd like to copy a file that is currently located in the same NUC in the following folder:

Desktop/ems/ems_raw.csv

I've given user rights to postgres user just in case:

-rw-rw-r-- 1 postgres me 4049497429 Mar 22 12:17 Desktop/ems/ems_raw.csv
me@docker1:~$

I've tried running the following in psql. METHOD 1:

me@docker1:~$ docker exec -ti pg_test psql -U postgres
psql (9.3.17)
Type "help" for help.

postgres=# COPY ems_stage FROM "Desktop/ems/ems_raw.csv" WITH (format csv,header);
ERROR:  syntax error at or near ""Desktop/ems/ems_raw.csv""
LINE 1: COPY ems_stage FROM "Desktop/ems/ems_raw.csv" WITH (format c...
                            ^
postgres=#

I've also tried running this via terminal direct just in case, METHOD 2:

me@docker1:~$ docker exec -ti pg_test psql -U postgres -c "COPY ems_stage FROM "Desktop/ems/ems_raw.csv" WITH (format csv,header);"
ERROR:  syntax error at or near "Desktop"
LINE 1: COPY ems_stage FROM Desktop/ems/ems_raw.csv WITH (format csv...
                            ^
me@docker1:~$

I know there is something basic here I may not be wrapping my head around. How would I go about running this properly? I'm assuming i am making a mistake with the path? Appreciate the help guys.

You can mount this directory as a volume when running container or copy it into container as described here: https://stackoverflow.com/questions/22907231/copying-files-from-host-to-docker-container — Chris, Mar 22 '20 at 17:44

score 4 · Answer 1 · answered Mar 23 '20 at 15:52

So after a combination of suggestions this is what worked for me:

psql -h localhost -p 5432 -d docker -U docker --password --c "\COPY ems_stage FROM 'Desktop/ems/ems_raw.csv' WITH (format csv,header);"

Referencing the docker database and docker username from the dockerfile in docker's documentation.

score 1 · Answer 2 · answered Mar 22 '20 at 19:00

You should install the postgresql-client package on your host, if you haven't already, and use psql from there to connect to the database

host$ sql -h localhost -U postgres

Once you've connected, run the COPY FROM ... command as above.

In this scenario it helps to think of the Docker container like a remote system, and the docker exec command as equivalent to ssh root@.... The container has an isolated filesystem and can't see files on the host; since in this case you're launching psql from inside the container, the COPY can't see the file it's trying to copy.

In principle you can use the docker run -v option to mount a directory from the host into the container. Since you're on native Linux, it might work to start the database with the external data file mounted, run the COPY FROM ... as you've shown, and then restart the database without it. Restarting the database for this doesn't seem like a desirable path, though. (On other host configurations bind mounts can be pretty slow, and this could be a substantial problem for a 4 GB data file.)

score 0 · Answer 3 · answered Mar 22 '20 at 19:10

I know there is something basic here I may not be wrapping my head around.

Indeed. The name of the file needs to be in single quotes (or dollar quotes), not in double quotes. There may be later errors as well, but you have to fix this in order to get to those.

How to properly copy data into postgres database through docker via COPY?

3 Answers3