Search code examples
postgresqldockerdocker-composepostgis

Containerized database file path and root directory unknown

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
Solution

  • 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).