How to export data from a postgres container inside of Docker

Cover Image for How to export data from a postgres container inside of Docker
James Phoenix
James Phoenix

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.


More Stories

Cover Image for What Are Webhooks? And How Do They Relate to Data Engineering?

What Are Webhooks? And How Do They Relate to Data Engineering?

Webhooks are a simple and powerful method for receiving real-time notifications when certain events occur. They enable a whole host of automated and interconnected applications. Broadly speaking, your apps can communicate via two main ways: polling and webhooks.  Polling is like going to a shop and asking for pizza – you have to ask whenever you want it. Webhooks are almost the…

James Phoenix
James Phoenix
Cover Image for What is an API? And How Do They Relate to Data Engineering?

What is an API? And How Do They Relate to Data Engineering?

An API, or Application Programming Interface, is a set of rules and protocols that allow different software systems to communicate with each other. Put simply, APIs define how software components should interact and allow developers to create new applications by leveraging existing functionality from other systems. Research shows that business investment in API has boomed in recent years, and significant API investment…

James Phoenix
James Phoenix