CODEWITHSUNDEEP
sqlpostgresqldockercsvpsql
nhtnhan
nhtnhan

Reputation: 115

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

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

Upvotes: 5

Views: 7100

Answers (2)

Álvaro González
Álvaro González

Reputation: 146578

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.

Upvotes: 0

Jim Jones
Jim Jones

Reputation: 19653

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

Upvotes: 9

Related Questions