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

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

CodePudding user response：

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

CodePudding user response：

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.