Re: bash or sql script to copy foreign table to locale table

khoff <koff10@xxxxxxxxxxx> · Fri, 20 May 2022 11:40:03 +0200



    Hi  Ron,
    It works  

    
    Thanks for your  contribution 

    
    khoff.

    
    Le 20/05/2022 à 05:17, Ron a écrit :

    
      Please do not hijack existing threads.

      
      On 5/19/22 18:55, koff wrote:

      
      Hi
        all, 

        
        I need little help  please, 

        
        I'v  already   foreign tables (from oracle_fdw) in my 
        postgresql environment. 

        
        I'm Just  looking for  script shell  or sql  ( easy way)  to
        migrate  this  foreign tables   to locale  table . 

        
        My script below  has some issue 

        
        thanks lot 

        
        ------script migrate--- 

        
        #!/bin/bash 

        export PGDATA=/pg01/pgdata/9.6/data 

        export PGUSER="postgres" 

        export PGPORT="5433" 

        export PGPASSWORD="ttooe2" 

        
        for tbl in `psql -qAt   data_2020 -c
          "select  foreign_table_name  from
          information_schema.foreign_tables;"`; 

          do

      
                        ## note the added
        semicolon

          psql data_2020  -c "create table
          public.\"$tbl\" as select * from fdw_link.\"$tbl\";"
      Here, either psql COPY the fdw_link.\"$tbl\"
      data OUT to a local file, and then COPY IN to public.\"$tbl\" OR INSERT INTO public.\"$tbl\" SELECT * FROM fdw_link.\"$tbl\";

      
      done 

        
        ----end  of script--- 

        
        the database is :  data_2020 

        
         foreign_table schema   is :  fdw_link 

        
        and want to copy  to public  schema 

        
      -- 

        Angular momentum makes the world go 'round.