How can I dump all tables to CSV for a PostgreSQL schema?

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 -U $USER -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

StackExchange Database Administrators Q#137140, answer score: 33