\copy table via psql from local to Docker container from within the container

Question

Disclaimer: Not talking about this.

How can I execute \copy command from psql when I'm inside the live docker container?

When I try \copy table from '/home/Snow/Documents/table.csv' with delimiter as ',' csv header; I get the error:

No such file or directory

Is there a different path I should write?

Jim Jones · Accepted Answer · 2019-12-12T13:21:47.033

2

From outside the container you can import a file using psql with the following command:

$ cat table.csv | psql -h docker_container -d db -c \
"COPY table FROM STDIN WITH DELIMITER ',' CSV"

Replace docker_container with the IP or hostname of the container
In case your postgres isn't configured to accept external connections, change it in the postgres.conf file: e.g. listen_addresses = '*' to listen to all external connections.

edited Dec 12 '19 at 13:21

answered Dec 12 '19 at 12:40

Jim Jones

the file.csv is located outside the container, and I execute the command from within the container filesystem. Not from `/home/Snow/Documents/` – Snow Dec 12 '19 at 12:43
so you don't have the option of executing this command from outside the container? – Jim Jones Dec 12 '19 at 12:44
no I can't because the postgres database is located inside the container, so I can't access it from outside – Snow Dec 12 '19 at 12:46
using psql you can. You just need to pass the hostname using the parameter `-h`, e.g. `psql -h localhost -U user -d db -c "..."`, `psql -h 192.168.1.42 -U user -d db -c "..."` – Jim Jones Dec 12 '19 at 12:49
very interesting, so that `-h localhost` makes the difference. I thought that localhost would be the default host... If i remove that part I can't login with that user anymore – Snow Dec 12 '19 at 12:58
+1, but I'm not sure whether I should accept the answer or not, since my question is about copying the table from within the container. However, it did solve my issue – Snow Dec 12 '19 at 12:58
@Snow well, I'm not veeery familiar with docker, but I guess it would suffice to go to the directory (inside the container), execute `pwd` and copy the path into your `copy` command. In case the file is outside the container, it could be that the volume wasn't specified when you started the container. Thanks for the upvote anyway :) – Jim Jones Dec 12 '19 at 13:06

score 1 · Answer 2 · answered Dec 12 '19 at 13:26

You would have to either copy the file to your docker container or mount the directory.

To copy the file, follow the procedure in the question to which you linked, something like:

docker cp /home/Snow/Documents/table.csv your_container:/

Then, from within your container, you could import access the file:

\copy table from '/table.csv' with delimiter as ',' csv header;

Alternatively, when you created the container, you could have added a volume with the -v option like:

docker run -v /home/Snow/Documents:/data postgres

and then your Documents directory would be at /data allowing you to import like this:

\copy table from '/data/table.csv' with delimiter as ',' csv header;

2 Answers2