Containerized database file path and root directory unknown

Question

I recently tried copying my database contents to a csv file with the following command inside my containerized Postgres database:

\copy ${TABLE} TO ${FILE} DELIMITER ',' CSV HEADER;

I got a response indicating the file was successfully copied however I can't find where it was copied to. When I try specifying a different path to output the file, I get the response directory/file.csv: No such file or directory

Does anyone know where containerized databases output files and how I can direct them to accessible locations?

I am on a Mac OS and this is some of the relevant info from my docker-compose file with which the database was initiated.

  db:
      image: kartoza/postgis:12.0
      volumes:
        - postgis:/var/lib/postgresql

enter the postgresql container and search for the file find / -iname "file.csv" — Edwin
– Edwin, Commented Oct 13, 2020 at 20:39

Marc Sances · Accepted Answer · 2020-10-13 20:41:46Z

1

Docker containers store their information internally in what is called Docker volumes. You can read more literature on that in Use volumes.

Regarding your particular issue, you've got some options:

Copy to a temporary file and pull it from the container:

\copy ${TABLE} TO /tmp/file.csv DELIMITER ',' CSV HEADER;

Then run docker ps, find your container ID and run:

docker cp container_id:/tmp/file.csv file.csv

And you will have file.csv with the data in your current folder.

Another, simpler way is to export to stdout, if the output is gonna be short:

\copy ${TABLE} TO STDOUT DELIMITER ',' CSV HEADER;

This will dump all the data through the terminal. Only use it if there are few enough registers that it doesn't get past the scrollback.

Third option, because two are never enough... you could publish temporarily the 5432 port and connect from your local machine using psql... then running the copy command will dump to your local machine. (Or use third-party tools like pgAdmin or DataGrip to dump the information).

answered Oct 13, 2020 at 20:41

Marc Sances

2,6441 gold badge22 silver badges38 bronze badges

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

1 Comment

Display name Over a year ago

You're a god-send mate. To anyone else reading this down the line: pay attention to the warnings listed for the second option, if the data set is too large it'll crash your terminal or editor

Collectives™ on Stack Overflow

Containerized database file path and root directory unknown

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related