How to download all tables from a postgres database as CSV format

Question

I want to download all tables from a Postgres database as CSV format. Below is a sample shell script I'm using to download all tables from a particular database.

But the script is not running after echo "No User/Role name provided set default User?Role postgres." User=postgres fi command.

Or anyone please suggest me any script or SQL queries to download all tables as CSV format. Thanks in advance.

#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=postgres
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

Answer 1

Seems like you do redirection which make the script to stop. Change the line:

echo " Schema Name is-->$Schema   Database Name--> $Db   User Name-->$User "

to be:

echo " Schema Name is--"'>'"$Schema   Database Name--"'>'" $Db   User Name-->$User "

The symbol > have special meaning in shells and must be escaped