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

Question

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.

Answer 1

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

Answer 2

If you need to export all schemas, here's the script

export PGDATABASE="db"
export 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