How to export data from a postgres container inside of Docker

If you are running a postgres database inside of Docker, it can be a powerful tool for quickly and easily transferring data. Exporting data from a postgres container inside of Docker is a relatively straightforward process and can be done with a few simple command line steps. We’ll walk through the steps for how to properly export data from a postgres container inside of Docker.

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

docker exec -it -u postgres postgres \
psql -d TEST_DB -c "COPY (SELECT * FROM tenants) TO STDOUT WITH CSV HEADER" > output.csv

Now let’s assume you’ve got multiple tables that you want to output:

#!/bin/bash 

# declare an array of table names 
declare -a tables=("table1" "table2" "table3")

# loop over the array of table names
for table in "${tables[@]}"
do
  # execute the psql command for each table
  docker exec -it -u database_user_name container_name \
  psql -d database_name -c "COPY (SELECT * FROM $table) TO STDOUT CSV" > "$table"_output.csv

done

Exporting data from a postgres container inside of Docker can be a complex process, but by following the steps outlined in this article, it can be done with relative ease. It is important to remember to use the right credentials and access rights to ensure the data is properly exported and secured.

Additionally, taking the time to understand the principles of docker containers and postgres databases can help make the process easier to understand. With the right knowledge and guidance, exporting data from a postgres container inside of Docker can be a successful and straightforward task.