I have a database with a lot of schemas in it and I want to dump the each of the table contents to CSV. I'm aware of the COPY command but I'm not sure how to script something that will read all of the tables in a schema and execute the COPY against them.
Here's a shell script that can do what you want:
SCHEMA="myschema" DB="mydb" USER="myuser" psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\ while read TBL; do psql -U $USER -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv done
Make sure you set the DB and SCHEMA variables to your particular database and schema.
The wrapping psql command uses the A and t flags to make a list of tables from the string passed to the c command.
If you want to include table column names add "HEADER" at the end of the query:
psql -U $USER -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV HEADER" $DB > $TBL.csv
If you need to export all schemas, here's the script
PGDATABASE="db" PGUSER="user" psql -Atc "select schema_name from information_schema.schemata" |\ while read SCHEMA; do if [[ "$SCHEMA" != "pg_catalog" && "$SCHEMA" != "information_schema" ]]; then psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" |\ while read TBL; do psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV DELIMITER ';' HEADER ENCODING 'UTF-8'" > $SCHEMA.$TBL.csv done fi done