Export Query Result as CSV file from Docker PostgreSQL container to local machine

Question

I'm not sure if this is possible of if I'm doing something wrong since I'm still pretty new to Docker. Basically, I want to export a query result inside PostgreSQL docker container as a csv file to my local machine.

This is where I got so far. Firstly, I run my PostgreSQL docker container with this command:

sudo docker run --rm --name pg-docker -e POSTGRES_PASSWORD=something -d -p 5432:5432 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data postgres

Then I access the docker container with docker exec to run PostgreSQL command that would copy the query result to a csv file with specified location like this:

\copy (select id,value from test) to 'test_1.csv' with csv;

I thought that should export the query result as a csv file named test_1.csv in the local machine, but I couldn't find the file anywhere in my local machine, also checked both of these directories: $HOME/docker/volumes/postgres; /var/lib/postgresql/data postgres

The file will be inside the container, perhaps in the root directory. — Álvaro González
– Álvaro González, Commented Oct 25, 2021 at 8:37
That's what I thought too, but it's nowhere to be found. Also, I'm only running a PostgreSQL docker container. — nhtnhan
– nhtnhan, Commented Oct 25, 2021 at 21:01

Jim Jones · Accepted Answer · 2021-10-26 07:20:23Z

10

You can export the data to the STDOUT and pipe the result to a file in the client machine:

docker exec -it -u database_user_name container_name \
psql -d database_name -c "COPY (SELECT * FROM table) TO STDOUT CSV" > output.csv

-c tells psql you to execute a given SQL statement when the connection is established.

So your command should look like this:

docker exec -it -u postgres pgdocker \
psql -d yourdb -c "COPY (SELECT * FROM test) TO STDOUT CSV" > test_1.csv

edited Oct 26, 2021 at 7:20

answered Oct 25, 2021 at 13:34

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nhtnhan Over a year ago

Thanks Jim for your answer! When I run this command, I got this error and not sure what it means or how to fix it: psql: error: missing "=" after "COPY" in connection info string

Jim Jones Over a year ago

@nhtnhan can you try this? docker exec -it -u your_user postgresql14 psql -d your_db -c "COPY (SELECT * FROM your_table) TO STDOUT CSV"

Alex Over a year ago

if you want to export with headers, change this: ` "COPY (SELECT * FROM table) TO STDOUT WITH CSV HEADER" `

Álvaro González · Accepted Answer · 2021-10-26 16:37:34Z

The /var/lib/postgresql/data directory is where the database server stores its data files. It isn't a directory that users need to manipulate directly or where nothing interesting can be found.

Paths like test_1.csv are relative to working directory. The default directory when you enter the postgres container with docker exec is / so that's where your file should be. You can also switch to another directory with cd before running psql:

root@b9e5a0572207:/some/other/path# cd /some/other/path/
root@b9e5a0572207:/some/other/path# psql -U postgres

... or you can provide an absolute path:

\copy (select id,value from test) to '/some/other/path/test_1.csv' with csv;

You can use docker cp to transfer a file from the container to the host:

docker cp pg-docker:/some/other/path/test_1.csv /tmp

... or you can create a volume if this is something you do often.

Collectives™ on Stack Overflow

Export Query Result as CSV file from Docker PostgreSQL container to local machine

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related