I want to export the entire database to aws S3 to later import into BI tools. I need to export each database table into its own csv file. But I dont wanna do it manually or do some script-fu. MySQL have something for this. Is there an easy way to achieve this with Postgresql?
Asked
Active
Viewed 4,048 times
2 Answers
4
With this query you can list all tables in the schema public
:
select table_schema, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema = 'public';
You can use the query in a function, which will execute appropriate copy
command for each table_name:
create or replace function copy_my_tables ()
returns void language plpgsql as $$
declare
r record;
begin
for r in
select table_schema, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema = 'public'
loop
execute format ('copy %s.%s to ''c:\data\%s_%s.csv'' (format csv)',
r.table_schema, r.table_name, r.table_schema, r.table_name);
end loop;
end $$;
select copy_my_tables();

klin
- 112,967
- 15
- 204
- 232
-
Plus 1. Can I also insert column name into csv? – MAK Feb 03 '16 at 06:42
-
2@MAK - Yes, add `header` as an option in `copy` command, i.e. `copy ... (format csv, header)`. – klin Feb 03 '16 at 07:24
-
Hey yup! I got it. Thank you so much. – MAK Feb 03 '16 at 07:58
1
we can export All table in separate csv with Header with in schema ,from bash scripts
#set -x
read -p "Please provide Schema name " Schema
if [ -z $Schema ]
then
echo "No Schema name provided set default schema public."
Schema=public
fi
read -p "Please provide Database name " Db
if [ -z $Db ]
then
echo "No Database name provided set default database postgres."
Db=postges
fi
read -p "Please provide Postgres Role/User name " User
if [ -z $User ]
then
echo "No User/Role name provided set default User?Role postgres."
User=postgres
fi
echo " Schema Name is-->$Schema Database Name--> $Db User Name-->$User "
psql -U $User -h localhost -Atc "select tablename from pg_tables where schemaname='$Schema'" $Db |\
while read TABLENAME; do
echo "$TABLENAME"
psql -U $User -h localhost -c "COPY $Schema.$TABLENAME TO STDOUT WITH CSV HEADER" $Db > $TABLENAME.csv
done
In above mention script you have to provide Schema database and User/Role if there is required to provide port and host name then you can use -p and -h Option

adg
- 99
- 6